1. Create the primary key constraint (disabled)

alter table DEPT add (
constraint PK_DEPT
primary key (DEPTNO))
disable primary key;

create unique index PK_DEPT on DEPT (DEPTNO)
tablespace DATA_1
storage (initial 10M next 1M pctincrease 0)
parallel (degree 5)
unrecoverable;

alter table DEPT enable primary key;

Export / Import using named pipe with compress

To export to a compressed file:

/etc/mknod pipename p
compress < pipename > newfilename.Z &
exp <username>/<password> file=pipename
rm -f pipename

/etc/mknod pipename p
uncompress < newfilename.Z > pipename &
imp <username>/<password> file=pipename
rm -f pipename

Building a bogus table

create table a (a1 number, a2 varchar2(50));
insert into a values (555,'I am a unique and extraordinary individual');
insert into a select * from a;  -- insert 1 row
insert into a select * from a;  -- insert 2 rows
insert into a select * from a;  -- insert 4 rows
insert into a select * from a;  -- insert 8 rows
insert into a select * from a;  -- insert 16 rows
insert into a select * from a;  -- insert 32 rows
insert into a select * from a;  -- insert 64 rows
insert into a select * from a;  -- insert 128 rows
insert into a select * from a;  -- insert 256 rows
insert into a select * from a;  -- insert 512 rows
insert into a select * from a;  -- insert 1024 rows
insert into a select * from a;  -- insert 2048 rows
update a set a1 = mod(rownum*1551,4096);
commit;
alter table a add constraint pk_a primary key (a1);
select count(*) from a;         -- 4096 rows

Decreasing size of column (which contains data)

Let's say you want to decrease ENAME from VARCHAR2(10) to VARCHAR2(7)
Oracle allows you to increase the size of a column but does not allow you to decrease the size of a column.
Here's a way to decrease the size of a column which contains data.

SQL> desc emp
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SAL                                      NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)


drop table FIX_IT;

create table FIX_IT as select rowid rowid_xx, ENAME from EMP;
create index FIX_IT on FIX_IT (rowid_xx);

alter table EMP modify (ENAME NULL);
update      EMP    set  ENAME = NULL;
alter table EMP modify (ENAME VARCHAR2(7));
update      EMP    set  ENAME = (
     select ENAME from FIX_IT where rowid_xx = EMP.rowid);

drop table FIX_IT;

Long Deletes (committing every x number of records)

set time on
set echo on
declare
        cnt     number(7)       := 0;
        tot     number(7)       := 0;

        cursor C1 is select rowid from <OWNER>.<TABLE_NAME>
                where <YOUR CRITERIA>;

begin
        for REC in C1 loop
                delete from <OWNER>.<TABLE_NAME>
                        where rowid = REC.rowid;

                tot := tot + 1;
                cnt := cnt + 1;

                if (cnt >= 1000) then
                        commit;
                        cnt := 0;
                end if;

        end loop;
        commit;
        dbms_output.put_line('<YOUR TABLE> records deleted: '||tot);
end;
/

Identify and remove duplicate records

Identify duplicate records:

select   COL1, COL2, COL#, COUNT(*)
from     <OWNER>.<TABLE_NAME>
group by COL1, COL2, COL#
having   count(*) > 1;

delete   from <OWNER>.<TABLE_NAME> a
where    rowid < (
         select  max(rowid) 
         from    <OWNER>.<TABLE_NAME> b
         where   b.COL1 = a.COL1
         and     b.COL2 = a.COL2
         and     b.COL# = a.COL#); 

delete  from <OWNER>.<TABLE_NAME>
where   rowid in (
        select rowid 
        from   <OWNER>.<TABLE_NAME>
        minus
        select max(rowid) 
        from   <OWNER>.<TABLE_NAME> b group by b.COL1, b.COL2,b.COL#);

Display Installed Products & Version

This will show you everything installed and its version for your defined $ORACLE_HOME.
Make sure your $ORACLE_HOME variable is set correctly.

cd $ORACLE_HOME/orainst
./inspdver