Some Datapump Scripts.

Thesee are some scripts I posted in OTN various times. I am trying to collate them together in this thread.

Script I: Exporting particular schema(s).

DECLARE
  l_dp_handle      NUMBER;
  l_last_job_state VARCHAR2(30) := 'UNDEFINED';
  l_job_state      VARCHAR2(30) := 'UNDEFINED';
  l_logfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.log';
  l_expfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.dmp';
BEGIN
  l_dp_handle := DBMS_DATAPUMP.OPEN(operation   => 'EXPORT',
                                    job_mode    => 'SCHEMA',
                                    remote_link => NULL,
                                    job_name    => 'SAUBHIK_EXPORT',
                                    version     => 'COMPATIBLE');

  DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
                         filename  => l_expfilename,
                         directory => 'TEST_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
  DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
                         filename  => l_logfilename,
                         directory => 'TEST_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  dbms_datapump.metadata_filter(handle => l_dp_handle,
                                name   => 'SCHEMA_EXPR',
                                value  => 'IN (''HR'',''SAUBHIK'')');

  dbms_datapump.start_job(l_dp_handle);

  dbms_datapump.detach(l_dp_handle);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error!');
    dbms_datapump.stop_job(l_dp_handle);
END;---------------------------------------------------------------------
Script II: Exporting particular tables with LIKE clause.

DECLARE
  l_dp_handle      NUMBER;
  l_last_job_state VARCHAR2(30) := 'UNDEFINED';
  l_job_state      VARCHAR2(30) := 'UNDEFINED';
  l_logfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.log';
  l_expfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.dmp';
BEGIN
  l_dp_handle := DBMS_DATAPUMP.OPEN(operation   => 'EXPORT',
                                    job_mode    => 'TABLE',
                                    remote_link => NULL,
                                    job_name    => 'SAUBHIK_EXPORT',
                                    version     => 'COMPATIBLE');

  DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
                         filename  => l_expfilename,
                         directory => 'TEST_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
  DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
                         filename  => l_logfilename,
                         directory => 'TEST_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  dbms_datapump.metadata_filter(handle => l_dp_handle,
                                name   => 'NAME_EXPR',
                                value  => 'LIKE ''EMP%''');

  dbms_datapump.start_job(l_dp_handle);

  dbms_datapump.detach(l_dp_handle);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error!');
    dbms_datapump.stop_job(l_dp_handle);
END;-------------------------------------------------------------------
SELECT * FROM user_datapump_jobs;

Script III: Exporting particular table.

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    l_dp_handle    NUMBER;
  3    l_last_job_state VARCHAR2(30) := 'UNDEFINED';
  4    l_job_state    VARCHAR2(30) := 'UNDEFINED';
  5    l_logfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.log';
  6    l_expfilename    VARCHAR2(20) := to_char(sysdate, 'DDMMRRRR') || '.dmp';
  7    l_tbl_name    VARCHAR2(30) :='EMP';
  8  BEGIN
  9    l_dp_handle := DBMS_DATAPUMP.OPEN(operation   => 'EXPORT',
 10                     job_mode    => 'TABLE',
 11                     remote_link => NULL,
 12                     job_name    => 'SAUBHIK_EXPORT',
 13                     version     => 'COMPATIBLE');
 14    DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
 15                  filename    => l_expfilename,
 16                  directory => 'SAUBHIK',
 17                  filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
 18    DBMS_DATAPUMP.ADD_FILE(handle    => l_dp_handle,
 19                  filename    => l_logfilename,
 20                  directory => 'SAUBHIK',
 21                  filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
 22    dbms_datapump.metadata_filter(handle => l_dp_handle,
 23                     name   => 'NAME_EXPR',
 24                     value  =>'= '||''''||l_tbl_name||'''');
 25    dbms_datapump.start_job(l_dp_handle);
 26    dbms_datapump.detach(l_dp_handle);
 27  EXCEPTION
 28    WHEN OTHERS THEN
 29     dbms_datapump.stop_job(l_dp_handle);
 30     RAISE;
 31* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM user_datapump_jobs;

JOB_NAME               OPERATION              JOB_MODE                 STATE                DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------------- -----------------
SAUBHIK_EXPORT               EXPORT                  TABLE                 EXECUTING                     1       0         2

SQL> /

JOB_NAME               OPERATION              JOB_MODE                 STATE                DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------------- -----------------
SAUBHIK_EXPORT               EXPORT                  TABLE                 NOT RUNNING                 0       0         0

SQL> /

no rows selected

SQL>

Partitioning an existing non partitioned table with data, index and constraints - PART I

Method I: Using dbms_redefinition.

Step 1: Create my table:

SQL>
SQL> drop table not_paritioned_tbl purge;

Table dropped.

SQL> CREATE TABLE not_partitioned_tbl AS
  2  SELECT object_id,object_name,created,owner FROM all_objects;

Table created.

SQL> ALTER TABLE not_partitioned_tbl ADD CONSTRAINT not_partitioned_tbl_pk PRIMARY KEY (object_id);

Table altered.

SQL> CREATE INDEX npt_created ON not_partitioned_tbl(created);

Index created.

SQL> EXECUTE DBMS_STATS.gather_table_stats('SCOTT','NOT_PARTITIONED_TBL',cascade=>true);

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM not_partitioned_tbl;

  COUNT(*)
----------
     42540

SQL>

Step 2: Create a partitioned table.
SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE partitioned_tbl (object_id NUMBER, object_name VARCHAR2(30), created DATE, owner VARCHAR2(30))
  2  PARTITION BY RANGE (created)
  3  (PARTITION pt_2005 VALUES LESS THAN (to_date('01/01/2006','dd/mm/rrrr')),
  4   PARTITION pt_2006 VALUES LESS THAN (to_date('01/01/2007','dd/mm/rrrr')),
  5   PARTITION pt_2007 VALUES LESS THAN (to_date('01/01/2008','dd/mm/rrrr')),
  6*  PARTITION pt_rest VALUES LESS THAN (MAXVALUE))
SQL> /

Table created.

Step 3: Verify whether we can redefine the existing table.

SQL> EXEC DBMS_REDEFINITION.can_redef_table('SCOTT','NOT_PARTITIONED_TBL');

PL/SQL procedure successfully completed.

SQL>

Step 4: Status of original and interim table

SQL> SELECT table_name,partitioned FROM all_tables
  2  WHERE table_name IN ('NOT_PARTITIONED_TBL','PARTITIONED_TBL');

TABLE_NAME               PAR
------------------------------ ---
PARTITIONED_TBL            YES
NOT_PARTITIONED_TBL           NO

SQL>

Step 4: Connect as sys (I don't want to give grants to scott) and start redefinition.

oracle@ubuntu-desktop:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 2 15:37:17 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> BEGIN
  2   DBMS_REDEFINITION.start_redef_table('SCOTT','NOT_PARTITIONED_TBL','PARTITIONED_TBL');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','NOT_PARTITIONED_TBL','PARTITIONED_TBL');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>

Step 5: Create index, constraints.

SQL> ALTER TABLE partitioned_tbl ADD CONSTRAINT partitioned_tbl_pk PRIMARY KEY (object_id);

Table altered.

SQL> CREATE INDEX pt_created ON partitioned_tbl(created);

Index created.

SQL> EXECUTE DBMS_STATS.gather_table_stats('SCOTT','PARTITIONED_TBL',cascade=>true);

PL/SQL procedure successfully completed.

SQL>

Step 6: Finish the redef (as sys)

SQL> EXECUTE dbms_redefinition.finish_redef_table('SCOTT','NOT_PARTITIONED_TBL','PARTITIONED_TBL');

PL/SQL procedure successfully completed.

SQL>

Step 7: Now non_partitioned_tbl should become partitioned.

SQL> SELECT table_name,partitioned FROM all_tables
  2  WHERE table_name IN ('NOT_PARTITIONED_TBL','PARTITIONED_TBL');

TABLE_NAME               PAR
------------------------------ ---
NOT_PARTITIONED_TBL           YES
PARTITIONED_TBL            NO

SQL>

SQL> DROP TABLE partitioned_tbl purge;

Table dropped.

SQL>
Step 8: You can rename the index and constraints to match the table name.

SQL> SELECT index_name,table_name FROM user_indexes WHERE table_name='NOT_PARTITIONED_TBL';

INDEX_NAME               TABLE_NAME
------------------------------ ------------------------------
PARTITIONED_TBL_PK           NOT_PARTITIONED_TBL
PT_CREATED               NOT_PARTITIONED_TBL

SQL> SELECT constraint_name,table_name FROM user_constraints WHERE table_name='NOT_PARTITIONED_TBL';

CONSTRAINT_NAME            TABLE_NAME
------------------------------ ------------------------------
PARTITIONED_TBL_PK           NOT_PARTITIONED_TBL

SQL>

Further Reading:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:869096700346654484
http://www.oracle-base.com/articles/misc/PartitioningAnExistingTable.php
http://www.oracle-base.com/articles/misc/PartitioningAnExistingTableUsingExchangePartition.php