Oracle DB tips

1. If you are always prompted the DB is still in use ...
   alter system enable restricted session;
   select sid,serial# from v$session where username = 'XXX';
   alter system kill session '24,419' immediate;
   alter system disable restricted session;
2. How to change the current value of a sequence?
   Q: if the curr_val=1, how could I set it to 10000?
   A: set "mysequence.INCREMENT_BY" to 9999, then try "select mysequence.nextval from dual", and then set "mysequence.INCREMENT_BY" back to 1 again.
3. Export Schema (backup)

          o First, create directory expdir (If you don't specify the parameter DIRECTORY=xxx then the dump file will dumped to /home/oracle/app/oracle/admin/$ORACLE_SID/dpdump by default)

            SQL> CONNECT system/welcome1

            SQL> CREATE OR REPLACE DIRECTORY expdir AS '/home/exp_data';

            SQL> GRANT READ,WRITE ON DIRECTORY expdir TO PUBLIC;

          o Then use expdp

            $ORACLE_HOME/bin/expdp system/welcome1@orcl DUMPFILE=data_bak.dmp DIRECTORY=expdir VERSION=10.2 SCHEMAS=SCOTT

          o Then you can copy the /home/exp_data/data_bak.dmp to some other directory or some other machine to backup it.

<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->

1. Import Schema (restore)

          o First, create directory expdir (if you already created it, plz ignore this step)

          o Then delete the SCOTT schema (beware of this step !!!)

            SQL> CONNECT system/welcome1

            SQL> DROP USER SCOTT CASCADE;

          o Then use impdp

            $ORACLE_HOME/bin/impdp system/welcome1 DIRECTORY=expdir DUMPFILE=data_bak.dmp PARTITION_OPTIONS=MERGE remap_schema=SCOTT:NEWSCHEMA