How to convert non-partitioned table to partition table using re-definition

We will do partition of table OUT_CDR which reside on CR_2 schema. We will do partition on column CDATE using RANGE partitioning technique. We will not change any table structure other than partition. All indexes, constraints, triggers, privileges defined on the table will be remain same.

Step 01: Let's have a look at the table on which we will do partitioning.

SQL> set pagesize 200
SQL> set long 999999
SQL> set linesize 150
SQL> select dbms_metadata.get_ddl('TABLE','OUT_CDR','CR_2') from dual;

DBMS_METADATA.GET_DDL('TABLE','OUT_CDR','CR_2')
--------------------------------------------------------------------------------

CREATE TABLE "CR_2"."OUT_CDR"
(    "ID" NUMBER(32,0) NOT NULL ENABLE,
     "CDATE" DATE NOT NULL ENABLE,
     "DDATE" DATE NOT NULL ENABLE,
     "ACCTSESSIONID" VARCHAR2(100),
     "CALLINGNO" VARCHAR2(100),
     "CALLEDNO" VARCHAR2(100) NOT NULL ENABLE,
     "AREACODE" VARCHAR2(100),
     "PREFIX" VARCHAR2(100),
     "SESSIONTIME" NUMBER(32,0),
     "BILLABLETIME" NUMBER(32,0),
     "RATE" NUMBER(32,4),
     "CALL_COST" NUMBER(32,4),
     "CURRENTBILL" NUMBER(32,4),
     "DISCONNECTCAUSE" VARCHAR2(50),
     "SOURCEIP" VARCHAR2(100),
     "DESTIP" VARCHAR2(100),
     "BILLABLE" NUMBER(32,0) NOT NULL ENABLE,
     "LESS" NUMBER(32,0) NOT NULL ENABLE,
     "ACCID" NUMBER(32,0),
     "IN_DDATE" DATE,
     "IN_PREFIX" VARCHAR2(100),
     "IN_SESSIONTIME" NUMBER(32,0),
     "IN_BILLABLETIME" NUMBER(32,0),
     "IN_RATE" NUMBER(32,4),
     "IN_CALL_COST" NUMBER(32,4),
     "IN_MONEYLEFT" NUMBER(32,4),
     "IN_DISCONNECTCAUSE" VARCHAR2(50),
     "IN_BILLABLE" NUMBER(32,0),
     "IN_LESS" NUMBER(32,0),
     "SWITCH_ID" NUMBER(32,0) NOT NULL ENABLE,
     "USER_ID" NUMBER(32,0) NOT NULL ENABLE,
     "IN_USER_ID" NUMBER(32,0),
     "PROCESSED" NUMBER(1,0),
      CONSTRAINT "OUT_CDR_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 168820736 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC"  ENABLE,
      CONSTRAINT "OUT_CDR_UQ" UNIQUE ("CDATE", "CALLEDNO", "USER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 522190848 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC"  ENABLE,
      CONSTRAINT "OUT_CDR_UQ_2" UNIQUE ("DDATE", "CALLEDNO", "USER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 521142272 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC"  ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 2013265920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "OUT_CDR_NEW_SPC"

Step 02: Let's determine if the table OUT_CDR can be redefined online.
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
PL/SQL procedure successfully completed.

Step 03: Create a interim table which holds the same structure as the original table except constraints, indexes, triggers but add the partitioning attribute.
I named the interim table as OUT_CDR_. Later we may drop it.

SQL> CREATE TABLE "CR_2"."OUT_CDR_"
2    (    "ID" NUMBER(32,0),
3         "CDATE" DATE ,
4         "DDATE" DATE ,
5         "ACCTSESSIONID" VARCHAR2(100),
6         "CALLINGNO" VARCHAR2(100),
7         "CALLEDNO" VARCHAR2(100) ,
8         "AREACODE" VARCHAR2(100),
9         "PREFIX" VARCHAR2(100),
10         "SESSIONTIME" NUMBER(32,0),
11         "BILLABLETIME" NUMBER(32,0),
12         "RATE" NUMBER(32,4),
13         "CALL_COST" NUMBER(32,4),
14         "CURRENTBILL" NUMBER(32,4),
15         "DISCONNECTCAUSE" VARCHAR2(50),
16         "SOURCEIP" VARCHAR2(100),
17         "DESTIP" VARCHAR2(100),
18         "BILLABLE" NUMBER(32,0) ,
19         "LESS" NUMBER(32,0) ,
20         "ACCID" NUMBER(32,0),
21         "IN_DDATE" DATE,
22         "IN_PREFIX" VARCHAR2(100),
23         "IN_SESSIONTIME" NUMBER(32,0),
24         "IN_BILLABLETIME" NUMBER(32,0),
25         "IN_RATE" NUMBER(32,4),
26         "IN_CALL_COST" NUMBER(32,4),
27         "IN_MONEYLEFT" NUMBER(32,4),
28         "IN_DISCONNECTCAUSE" VARCHAR2(50),
29         "IN_BILLABLE" NUMBER(32,0),
30         "IN_LESS" NUMBER(32,0),
31         "SWITCH_ID" NUMBER(32,0) ,
32         "USER_ID" NUMBER(32,0) ,
33         "IN_USER_ID" NUMBER(32,0),
34         "PROCESSED" NUMBER(1,0)
35          ) TABLESPACE "OUT_CDR_NEW_SPC"
36  Partition by range(cdate)
37  (
38  partition P08152008 values less than (to_date('15-AUG-2008','DD-MON-YYYY')),
39  partition P09012008 values less than (to_date('01-SEP-2008','DD-MON-YYYY')),
40  partition P09152008 values less than (to_date('15-SEP-2008','DD-MON-YYYY')),
41  partition P10012008 values less than (to_date('01-OCT-2008','DD-MON-YYYY')),
42  partition P10152008 values less than (to_date('15-OCT-2008','DD-MON-YYYY')),
43  partition P11012008 values less than (to_date('01-NOV-2008','DD-MON-YYYY')),
44  partition P11152008 values less than (to_date('15-NOV-2008','DD-MON-YYYY')),
45  partition P12012008 values less than (to_date('01-DEC-2008','DD-MON-YYYY')),
46  partition P12152008 values less than (to_date('15-DEC-2008','DD-MON-YYYY')),
47  partition P01012009 values less than (to_date('01-JAN-2009','DD-MON-YYYY')),
48  partition P01152009 values less than (to_date('15-JAN-2009','DD-MON-YYYY')),
49  partition P02012009 values less than (to_date('01-FEB-2009','DD-MON-YYYY')),
50  partition PMAX values less than (maxvalue));

Table created.

Step 04: Initiates the redefinition process by calling dbms_redefinition.start_redef_table procedure.

SQL> exec dbms_redefinition.start_redef_table('CR_2', 'OUT_CDR', 'OUT_CDR_');

PL/SQL procedure successfully completed.

Step 05: Copies the dependent objects of the original table onto the interim table. The COPY_TABLE_DEPENDENTS Procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects. But this procedure does not clone the already registered dependent objects.

In fact COPY_TABLE_DEPENDENTS Procedure is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table which in facr represents the post-redefinition table.

SQL> declare
2  error_count pls_integer := 0;
3  BEGIN
4  dbms_redefinition.copy_table_dependents('CR_2', 'OUT_CDR', 'OUT_CDR_',1, true, true, true, false,error_count);
5  dbms_output.put_line('errors := ' || to_char(error_count));
6  END;
7  /

PL/SQL procedure successfully completed.

Step 06: Completes the redefinition process by calling FINISH_REDEF_TABLE Procedure.
SQL> exec dbms_redefinition.finish_redef_table('CR_2', 'OUT_CDR', 'OUT_CDR_');

PL/SQL procedure successfully completed.

Step 07: Check the partitioning validation by,

SQL> Select partition_name, high_value from user_tab_partitions where table_name='OUT_CDR';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ---------------------------------------------------------------------------------------------------
P01012009                      TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P01152009                      TO_DATE(' 2009-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P02012009                      TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P08152008                      TO_DATE(' 2008-08-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P09012008                      TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P09152008                      TO_DATE(' 2008-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P10012008                      TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P10152008                      TO_DATE(' 2008-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P11012008                      TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P11152008                      TO_DATE(' 2008-11-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P12012008                      TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P12152008                      TO_DATE(' 2008-12-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PMAX                           MAXVALUE

13 rows selected.

Check index status by,

SQL> select index_name , status from user_indexes where table_name='OUT_CDR';

INDEX_NAME                     STATUS
------------------------------ --------
OUT_CDR_PK                     VALID
OUT_CDR_UQ                     VALID
OUT_CDR_UQ_2                   VALID

Step 08: Drop the interim table OUT_CDR_.
SQL> DROP TABLE OUT_CDR_;
Table dropped.

How to convert non-partitioned table to partition table using re-definition

We will do partition of table OUT_CDR which reside on CR_2 schema. We will do partition on column CDATE using RANGE partitioning technique. We will not change any table structure other than partition. All indexes, constraints, triggers, privileges defined on the table will be remain same.

Step 01: Let's have a look at the table on which we will do partitioning.

SQL> set pagesize 200
SQL> set long 999999
SQL> set linesize 150
SQL> select dbms_metadata.get_ddl('TABLE','OUT_CDR','CR_2') from dual;

DBMS_METADATA.GET_DDL('TABLE','OUT_CDR','CR_2')
--------------------------------------------------------------------------------

CREATE TABLE "CR_2"."OUT_CDR"
(    "ID" NUMBER(32,0) NOT NULL ENABLE,
     "CDATE" DATE NOT NULL ENABLE,
     "DDATE" DATE NOT NULL ENABLE,
     "ACCTSESSIONID" VARCHAR2(100),
     "CALLINGNO" VARCHAR2(100),
     "CALLEDNO" VARCHAR2(100) NOT NULL ENABLE,
     "AREACODE" VARCHAR2(100),
     "PREFIX" VARCHAR2(100),
     "SESSIONTIME" NUMBER(32,0),
     "BILLABLETIME" NUMBER(32,0),
     "RATE" NUMBER(32,4),
     "CALL_COST" NUMBER(32,4),
     "CURRENTBILL" NUMBER(32,4),
     "DISCONNECTCAUSE" VARCHAR2(50),
     "SOURCEIP" VARCHAR2(100),
     "DESTIP" VARCHAR2(100),
     "BILLABLE" NUMBER(32,0) NOT NULL ENABLE,
     "LESS" NUMBER(32,0) NOT NULL ENABLE,
     "ACCID" NUMBER(32,0),
     "IN_DDATE" DATE,
     "IN_PREFIX" VARCHAR2(100),
     "IN_SESSIONTIME" NUMBER(32,0),
     "IN_BILLABLETIME" NUMBER(32,0),
     "IN_RATE" NUMBER(32,4),
     "IN_CALL_COST" NUMBER(32,4),
     "IN_MONEYLEFT" NUMBER(32,4),
     "IN_DISCONNECTCAUSE" VARCHAR2(50),
     "IN_BILLABLE" NUMBER(32,0),
     "IN_LESS" NUMBER(32,0),
     "SWITCH_ID" NUMBER(32,0) NOT NULL ENABLE,
     "USER_ID" NUMBER(32,0) NOT NULL ENABLE,
     "IN_USER_ID" NUMBER(32,0),
     "PROCESSED" NUMBER(1,0),
      CONSTRAINT "OUT_CDR_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 168820736 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC"  ENABLE,
      CONSTRAINT "OUT_CDR_UQ" UNIQUE ("CDATE", "CALLEDNO", "USER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 522190848 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC"  ENABLE,
      CONSTRAINT "OUT_CDR_UQ_2" UNIQUE ("DDATE", "CALLEDNO", "USER_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 521142272 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CDR_INDX_SPC"  ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 2013265920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "OUT_CDR_NEW_SPC"

Step 02: Let's determine if the table OUT_CDR can be redefined online.
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
PL/SQL procedure successfully completed.

Step 03: Create a interim table which holds the same structure as the original table except constraints, indexes, triggers but add the partitioning attribute.
I named the interim table as OUT_CDR_. Later we may drop it.

SQL> CREATE TABLE "CR_2"."OUT_CDR_"
2    (    "ID" NUMBER(32,0),
3         "CDATE" DATE ,
4         "DDATE" DATE ,
5         "ACCTSESSIONID" VARCHAR2(100),
6         "CALLINGNO" VARCHAR2(100),
7         "CALLEDNO" VARCHAR2(100) ,
8         "AREACODE" VARCHAR2(100),
9         "PREFIX" VARCHAR2(100),
10         "SESSIONTIME" NUMBER(32,0),
11         "BILLABLETIME" NUMBER(32,0),
12         "RATE" NUMBER(32,4),
13         "CALL_COST" NUMBER(32,4),
14         "CURRENTBILL" NUMBER(32,4),
15         "DISCONNECTCAUSE" VARCHAR2(50),
16         "SOURCEIP" VARCHAR2(100),
17         "DESTIP" VARCHAR2(100),
18         "BILLABLE" NUMBER(32,0) ,
19         "LESS" NUMBER(32,0) ,
20         "ACCID" NUMBER(32,0),
21         "IN_DDATE" DATE,
22         "IN_PREFIX" VARCHAR2(100),
23         "IN_SESSIONTIME" NUMBER(32,0),
24         "IN_BILLABLETIME" NUMBER(32,0),
25         "IN_RATE" NUMBER(32,4),
26         "IN_CALL_COST" NUMBER(32,4),
27         "IN_MONEYLEFT" NUMBER(32,4),
28         "IN_DISCONNECTCAUSE" VARCHAR2(50),
29         "IN_BILLABLE" NUMBER(32,0),
30         "IN_LESS" NUMBER(32,0),
31         "SWITCH_ID" NUMBER(32,0) ,
32         "USER_ID" NUMBER(32,0) ,
33         "IN_USER_ID" NUMBER(32,0),
34         "PROCESSED" NUMBER(1,0)
35          ) TABLESPACE "OUT_CDR_NEW_SPC"
36  Partition by range(cdate)
37  (
38  partition P08152008 values less than (to_date('15-AUG-2008','DD-MON-YYYY')),
39  partition P09012008 values less than (to_date('01-SEP-2008','DD-MON-YYYY')),
40  partition P09152008 values less than (to_date('15-SEP-2008','DD-MON-YYYY')),
41  partition P10012008 values less than (to_date('01-OCT-2008','DD-MON-YYYY')),
42  partition P10152008 values less than (to_date('15-OCT-2008','DD-MON-YYYY')),
43  partition P11012008 values less than (to_date('01-NOV-2008','DD-MON-YYYY')),
44  partition P11152008 values less than (to_date('15-NOV-2008','DD-MON-YYYY')),
45  partition P12012008 values less than (to_date('01-DEC-2008','DD-MON-YYYY')),
46  partition P12152008 values less than (to_date('15-DEC-2008','DD-MON-YYYY')),
47  partition P01012009 values less than (to_date('01-JAN-2009','DD-MON-YYYY')),
48  partition P01152009 values less than (to_date('15-JAN-2009','DD-MON-YYYY')),
49  partition P02012009 values less than (to_date('01-FEB-2009','DD-MON-YYYY')),
50  partition PMAX values less than (maxvalue));

Table created.

Step 04: Initiates the redefinition process by calling dbms_redefinition.start_redef_table procedure.

SQL> exec dbms_redefinition.start_redef_table('CR_2', 'OUT_CDR', 'OUT_CDR_');

PL/SQL procedure successfully completed.

Step 05: Copies the dependent objects of the original table onto the interim table. The COPY_TABLE_DEPENDENTS Procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects. But this procedure does not clone the already registered dependent objects.

In fact COPY_TABLE_DEPENDENTS Procedure is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table which in facr represents the post-redefinition table.

SQL> declare
2  error_count pls_integer := 0;
3  BEGIN
4  dbms_redefinition.copy_table_dependents('CR_2', 'OUT_CDR', 'OUT_CDR_',1, true, true, true, false,error_count);
5  dbms_output.put_line('errors := ' || to_char(error_count));
6  END;
7  /

PL/SQL procedure successfully completed.

Step 06: Completes the redefinition process by calling FINISH_REDEF_TABLE Procedure.
SQL> exec dbms_redefinition.finish_redef_table('CR_2', 'OUT_CDR', 'OUT_CDR_');

PL/SQL procedure successfully completed.

Step 07: Check the partitioning validation by,

SQL> Select partition_name, high_value from user_tab_partitions where table_name='OUT_CDR';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ---------------------------------------------------------------------------------------------------
P01012009                      TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P01152009                      TO_DATE(' 2009-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P02012009                      TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P08152008                      TO_DATE(' 2008-08-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P09012008                      TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P09152008                      TO_DATE(' 2008-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P10012008                      TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P10152008                      TO_DATE(' 2008-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P11012008                      TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P11152008                      TO_DATE(' 2008-11-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P12012008                      TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P12152008                      TO_DATE(' 2008-12-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PMAX                           MAXVALUE

13 rows selected.

Check index status by,

SQL> select index_name , status from user_indexes where table_name='OUT_CDR';

INDEX_NAME                     STATUS
------------------------------ --------
OUT_CDR_PK                     VALID
OUT_CDR_UQ                     VALID
OUT_CDR_UQ_2                   VALID

Step 08: Drop the interim table OUT_CDR_.
SQL> DROP TABLE OUT_CDR_;
Table dropped.

Query to check whether user has datapump privilege.

Check for whether user has full database export/import privilege
You can check whether user has privilege to export or import the full database using data pump. To export full database using data pump user must have EXP_FULL_DATABASE role or dba role and to import full database using data pump user must have IMP_FULL_DATABASE or dba role.
In order to see whether user has these privilege or not you can query,

SQL>SET lines 100
COL privilege FOR a50
SELECT grantee, granted_role, default_role
FROM dba_role_privs
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY 1,2; 
GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
SHAIK                          DBA                            YES
DBA                            EXP_FULL_DATABASE              YES
DBA                            IMP_FULL_DATABASE              YES
FARUK                          DBA                            YES
MOMIN                          DBA                            YES
SYS                            DBA                            YES
SYS                            EXP_FULL_DATABASE              YES
SYS                            IMP_FULL_DATABASE              YES
SYSMAN                         DBA                            YES
SYSTEM                         DBA                            YES
TEST                           EXP_FULL_DATABASE              YES

which indicates user SHAIK,FARUK,MOMIN,SYS,SYSTEM can do both full data pump export and full datapump import operation. And user Test only has datapump export operation. Note that DBA role includes both EXP_FULL_DATABASE and IMP_FULL_DATABASE roles.

Check for user has create table or create session privilege
In order to see whether a user has CREATE TABLE or CREATE SESSION privilege, query from dba_sys_privs table. Following is the check whether user TEST has these privilege or not.

SQL>SET lines 100 pages 100
COL privilege FOR a40
SELECT grantee, privilege
 FROM dba_sys_privs
WHERE (grantee IN ('TEST', 'PUBLIC')
       OR grantee IN (SELECT granted_role FROM dba_role_privs
                       WHERE grantee IN ('TEST', 'PUBLIC')))
  AND privilege IN ('CREATE SESSION', 'CREATE TABLE')
ORDER BY 1,2;   
If user test have it then output will be like below.
GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
TEST                           CREATE SESSION
TEST                           CREATE TABLE

Check for granted privileges on directory objects
Suppose we want to check whether user TEST has privilege on directory objects query,

SET lines 110
COL privilege FOR a12
COL grantee FOR a25
COL owner FOR a25
SELECT p.grantee, p.privilege, p.owner, d.directory_name
 FROM dba_tab_privs p, dba_directories d
WHERE p.table_name=d.directory_name
  AND (grantee IN ('TEST', 'PUBLIC')
       OR grantee IN (SELECT granted_role FROM dba_role_privs
                       WHERE grantee IN ('TEST', 'PUBLIC')))
ORDER BY 4,3,2;
A typical output is
GRANTEE              PRIVILEGE  OWNER                DIRECTORY_NAME
-------------------- ---------- -------------------- ------------------------------
TEST                 READ       SYS                  D
TEST                 WRITE      SYS                  D
EXP_FULL_DATABASE    READ       SYS                  DATA_PUMP_DIR
EXP_FULL_DATABASE    WRITE      SYS                  DATA_PUMP_DIR

Check for default tablespace on a user
In order to check what is the default tablespace of user TEST query,

SQL>select default_tablespace from dba_users where username='TEST';

Check for tablespace quota
To check whether user TEST has quota on which tablespaces query,
SET lines 100 numwidth 12
SELECT q.username, q.tablespace_name, q.bytes, q.max_bytes
 FROM dba_ts_quotas q, dba_users u
WHERE q.username=u.username AND q.username in ('TEST'); 
USERNAME                       TABLESPACE_NAME                       BYTES    MAX_BYTES
------------------------------ ------------------------------ ------------ ------------
TEST                           SYSTEM                                65536     10485760

From output we can see user TEST has quota on system tablespace 10M and he already used 65K.

Query to check whether user has datapump privilege.

Check for whether user has full database export/import privilege
You can check whether user has privilege to export or import the full database using data pump. To export full database using data pump user must have EXP_FULL_DATABASE role or dba role and to import full database using data pump user must have IMP_FULL_DATABASE or dba role.
In order to see whether user has these privilege or not you can query,

SQL>SET lines 100
COL privilege FOR a50
SELECT grantee, granted_role, default_role
FROM dba_role_privs
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY 1,2; 
GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
SHAIK                          DBA                            YES
DBA                            EXP_FULL_DATABASE              YES
DBA                            IMP_FULL_DATABASE              YES
FARUK                          DBA                            YES
MOMIN                          DBA                            YES
SYS                            DBA                            YES
SYS                            EXP_FULL_DATABASE              YES
SYS                            IMP_FULL_DATABASE              YES
SYSMAN                         DBA                            YES
SYSTEM                         DBA                            YES
TEST                           EXP_FULL_DATABASE              YES

which indicates user SHAIK,FARUK,MOMIN,SYS,SYSTEM can do both full data pump export and full datapump import operation. And user Test only has datapump export operation. Note that DBA role includes both EXP_FULL_DATABASE and IMP_FULL_DATABASE roles.

Check for user has create table or create session privilege
In order to see whether a user has CREATE TABLE or CREATE SESSION privilege, query from dba_sys_privs table. Following is the check whether user TEST has these privilege or not.

SQL>SET lines 100 pages 100
COL privilege FOR a40
SELECT grantee, privilege
 FROM dba_sys_privs
WHERE (grantee IN ('TEST', 'PUBLIC')
       OR grantee IN (SELECT granted_role FROM dba_role_privs
                       WHERE grantee IN ('TEST', 'PUBLIC')))
  AND privilege IN ('CREATE SESSION', 'CREATE TABLE')
ORDER BY 1,2;   
If user test have it then output will be like below.
GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
TEST                           CREATE SESSION
TEST                           CREATE TABLE

Check for granted privileges on directory objects
Suppose we want to check whether user TEST has privilege on directory objects query,

SET lines 110
COL privilege FOR a12
COL grantee FOR a25
COL owner FOR a25
SELECT p.grantee, p.privilege, p.owner, d.directory_name
 FROM dba_tab_privs p, dba_directories d
WHERE p.table_name=d.directory_name
  AND (grantee IN ('TEST', 'PUBLIC')
       OR grantee IN (SELECT granted_role FROM dba_role_privs
                       WHERE grantee IN ('TEST', 'PUBLIC')))
ORDER BY 4,3,2;
A typical output is
GRANTEE              PRIVILEGE  OWNER                DIRECTORY_NAME
-------------------- ---------- -------------------- ------------------------------
TEST                 READ       SYS                  D
TEST                 WRITE      SYS                  D
EXP_FULL_DATABASE    READ       SYS                  DATA_PUMP_DIR
EXP_FULL_DATABASE    WRITE      SYS                  DATA_PUMP_DIR

Check for default tablespace on a user
In order to check what is the default tablespace of user TEST query,

SQL>select default_tablespace from dba_users where username='TEST';

Check for tablespace quota
To check whether user TEST has quota on which tablespaces query,
SET lines 100 numwidth 12
SELECT q.username, q.tablespace_name, q.bytes, q.max_bytes
 FROM dba_ts_quotas q, dba_users u
WHERE q.username=u.username AND q.username in ('TEST'); 
USERNAME                       TABLESPACE_NAME                       BYTES    MAX_BYTES
------------------------------ ------------------------------ ------------ ------------
TEST                           SYSTEM                                65536     10485760

From output we can see user TEST has quota on system tablespace 10M and he already used 65K.

Script to list backups registered in RMAN catalog database

Script to list backups registered in RMAN catalog database

select d.name, p.tag, p.media,
s.incremental_level "LEVEL",
to_char(s.start_time, 'DD-MON-YY HH24:MI') start_time,
s.elapsed_seconds/60 "MINUTES"
from RC_DATABASE d, RC_BACKUP_PIECE p, RC_BACKUP_SET s
where d.name = 'WH'
and s.start_time > '04-MAY-02'
and s.completion_time < '06-MAY-02'
and p.backup_type = 'D' -- D=Database, L=Log
and d.db_key = p.db_key
and s.db_key = p.db_key
and p.bs_key = s.bs_key
/

Script to list backups registered in RMAN catalog database

Script to list backups registered in RMAN catalog database

select d.name, p.tag, p.media,
s.incremental_level "LEVEL",
to_char(s.start_time, 'DD-MON-YY HH24:MI') start_time,
s.elapsed_seconds/60 "MINUTES"
from RC_DATABASE d, RC_BACKUP_PIECE p, RC_BACKUP_SET s
where d.name = 'WH'
and s.start_time > '04-MAY-02'
and s.completion_time < '06-MAY-02'
and p.backup_type = 'D' -- D=Database, L=Log
and d.db_key = p.db_key
and s.db_key = p.db_key
and p.bs_key = s.bs_key
/

Script to monitor RMAN status, while a backup is running with details

Script to monitor RMAN status, while a backup is running with देतैल्स

SELECT dbfiles||' from '||numfiles "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from sys.v_$datafile),
(select count(*) dbfiles
from sys.v_$backup_datafile a, sys.v_$datafile b
where a.file# = b.file#
and a.completion_time > sysdate - 1),
(select count(*) cfiles from sys.v_$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from sys.v_$backup_spfile
where completion_time > sysdate - 1)
/

-- Archlog Files Backed up during past 24 Hours

SELECT backedup||' from '||archived "Archlog files backed up",
ondisk "Archlog files still on disk"
FROM (select count(*) archived
from sys.v_$archived_log where completion_time > sysdate - 1),
(select count(*) backedup from sys.v_$archived_log
where backup_count > 0
and completion_time > sysdate - 1),
(select count(*) ondisk from sys.v_$archived_log
where archived = 'YES' and deleted = 'NO')
/

-- RMAN Backups Still Running:

SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
FROM sys.v_$session_longops
WHERE compnam = 'dbms_backup_restore'
/

Script to monitor RMAN status, while a backup is running with details

Script to monitor RMAN status, while a backup is running with देतैल्स

SELECT dbfiles||' from '||numfiles "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from sys.v_$datafile),
(select count(*) dbfiles
from sys.v_$backup_datafile a, sys.v_$datafile b
where a.file# = b.file#
and a.completion_time > sysdate - 1),
(select count(*) cfiles from sys.v_$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from sys.v_$backup_spfile
where completion_time > sysdate - 1)
/

-- Archlog Files Backed up during past 24 Hours

SELECT backedup||' from '||archived "Archlog files backed up",
ondisk "Archlog files still on disk"
FROM (select count(*) archived
from sys.v_$archived_log where completion_time > sysdate - 1),
(select count(*) backedup from sys.v_$archived_log
where backup_count > 0
and completion_time > sysdate - 1),
(select count(*) ondisk from sys.v_$archived_log
where archived = 'YES' and deleted = 'NO')
/

-- RMAN Backups Still Running:

SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
FROM sys.v_$session_longops
WHERE compnam = 'dbms_backup_restore'
/

Script to List completed RMAN backups for the last 24-hours

Script to List completed RMAN backups for the last 24-हौर्स

select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
'Unknown type='||BACKUP_TYPE) TYPE,
to_char(a.start_time, 'DDMON HH24:MI') start_time,
to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
substr(handle, -35) handle,
nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks
from SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
SYS.V_$BACKUP_DATAFILE d, SYS.V_$BACKUP_REDOLOG l
where a.start_time between sysdate-1 and sysdate
and a.SET_STAMP = b.SET_STAMP
and a.SET_STAMP = d.SET_STAMP(+)
and a.SET_STAMP = l.SET_STAMP(+)
order by start_time, file#
/

Script to List completed RMAN backups for the last 24-hours

Script to List completed RMAN backups for the last 24-हौर्स

select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
'Unknown type='||BACKUP_TYPE) TYPE,
to_char(a.start_time, 'DDMON HH24:MI') start_time,
to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
substr(handle, -35) handle,
nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks
from SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
SYS.V_$BACKUP_DATAFILE d, SYS.V_$BACKUP_REDOLOG l
where a.start_time between sysdate-1 and sysdate
and a.SET_STAMP = b.SET_STAMP
and a.SET_STAMP = d.SET_STAMP(+)
and a.SET_STAMP = l.SET_STAMP(+)
order by start_time, file#
/

Script to generate create index statement

Script to generate create index statement

SELECT 'create '
|| t1.index_type
|| ' index '
|| t1.index_name
|| ' on '
|| t1.table_name
|| '('
|| t2.column_name
|| ') tablespace indx;'
FROM user_indexes t1, user_ind_columns t2
WHERE t1.index_name = t2.index_name;

Script to generate create index statement

Script to generate create index statement

SELECT 'create '
|| t1.index_type
|| ' index '
|| t1.index_name
|| ' on '
|| t1.table_name
|| '('
|| t2.column_name
|| ') tablespace indx;'
FROM user_indexes t1, user_ind_columns t2
WHERE t1.index_name = t2.index_name;

Script rebuilds the schema with minimal downtime

Script rebuilds the schema with minimal downtime

CREATE OR REPLACE PROCEDURE schema_rebuild authid current_user IS
CURSOR obj_list IS
SELECT decode( segment_type, 'TABLE', segment_name, table_name ) order_col1,
decode( segment_type, 'TABLE', 1, 2 ) order_col2,
decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) m_or_r,segment_type,segment_name
FROM user_segments, (SELECT table_name, index_name FROM user_indexes )
WHERE segment_type IN ( 'TABLE', 'INDEX' )
AND segment_name = index_name (+) ORDER BY 1, 2 ;

reb_str VARCHAR2(200) ;
username varchar2(20);


BEGIN
username:=user;
FOR i IN OBJ_LIST LOOP
IF i.segment_type = 'INDEX' THEN
reb_str := 'alter ' || i.segment_type || ' ' || i.segment_name ||' '|| i.m_or_r ||' online';
ELSE
reb_str := 'alter ' || i.segment_type || ' ' || i.segment_name ||' '|| i.m_or_r;
END IF;
EXECUTE IMMEDIATE reb_str;
reb_str := '';
END LOOP ;
dbms_stats.gather_schema_stats (ownname=>username, method_opt=>'FOR ALL INDEXED COLUMNS', cascade=>TRUE);
END ;
/

Script rebuilds the schema with minimal downtime

Script rebuilds the schema with minimal downtime

CREATE OR REPLACE PROCEDURE schema_rebuild authid current_user IS
CURSOR obj_list IS
SELECT decode( segment_type, 'TABLE', segment_name, table_name ) order_col1,
decode( segment_type, 'TABLE', 1, 2 ) order_col2,
decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) m_or_r,segment_type,segment_name
FROM user_segments, (SELECT table_name, index_name FROM user_indexes )
WHERE segment_type IN ( 'TABLE', 'INDEX' )
AND segment_name = index_name (+) ORDER BY 1, 2 ;

reb_str VARCHAR2(200) ;
username varchar2(20);


BEGIN
username:=user;
FOR i IN OBJ_LIST LOOP
IF i.segment_type = 'INDEX' THEN
reb_str := 'alter ' || i.segment_type || ' ' || i.segment_name ||' '|| i.m_or_r ||' online';
ELSE
reb_str := 'alter ' || i.segment_type || ' ' || i.segment_name ||' '|| i.m_or_r;
END IF;
EXECUTE IMMEDIATE reb_str;
reb_str := '';
END LOOP ;
dbms_stats.gather_schema_stats (ownname=>username, method_opt=>'FOR ALL INDEXED COLUMNS', cascade=>TRUE);
END ;
/

Tracking the execution of an Oracle import progress

Tracking the execution of an Oracle import प्रोग्रेस

SELECT
SUBSTR(sql_text, INSTR(sql_text,\'INTO \"\'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,\'yyyy-mm-dd hh24:mi:ss\'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,\'yyyy-mm-dd hh24:mi:ss\'))*24*60)) rows_per_minute
FROM
sys.v_$sqlarea
WHERE
sql_text like \'INSERT %INTO \"%\'
AND command_type = 2
AND open_versions > 0;

Tracking the execution of an Oracle import progress

Tracking the execution of an Oracle import प्रोग्रेस

SELECT
SUBSTR(sql_text, INSTR(sql_text,\'INTO \"\'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,\'yyyy-mm-dd hh24:mi:ss\'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,\'yyyy-mm-dd hh24:mi:ss\'))*24*60)) rows_per_minute
FROM
sys.v_$sqlarea
WHERE
sql_text like \'INSERT %INTO \"%\'
AND command_type = 2
AND open_versions > 0;

Script to Show Used/free space in MB by tablespace name

Script to Show Used/free space in Meg by tablespace name

SELECT Total।name \"Tablespace Name\",

nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name

Script to Show Used/free space in MB by tablespace name

Script to Show Used/free space in Meg by tablespace name

SELECT Total।name \"Tablespace Name\",

nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name

Query to see the Reserved, Used and Free space of your database

Query to see the Reserved, Used and Free space of your database


SELECT r.tablespace_name, reserved_space "RESERVED_SPACE(MB)",
reserved_space - free_space "USED_SPACE(MB)",
free_space "FREE_SPACE(MB)"
FROM (SELECT tablespace_name, SUM (BYTES)
/ (1024 * 1024) reserved_space
FROM dba_data_files
GROUP BY tablespace_name) r,
(SELECT tablespace_name, SUM (BYTES) / (1024 * 1024) free_space
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE r.tablespace_name = f.tablespace_name
ORDER BY r.tablespace_name;

Query to see the Reserved, Used and Free space of your database

Query to see the Reserved, Used and Free space of your database


SELECT r.tablespace_name, reserved_space "RESERVED_SPACE(MB)",
reserved_space - free_space "USED_SPACE(MB)",
free_space "FREE_SPACE(MB)"
FROM (SELECT tablespace_name, SUM (BYTES)
/ (1024 * 1024) reserved_space
FROM dba_data_files
GROUP BY tablespace_name) r,
(SELECT tablespace_name, SUM (BYTES) / (1024 * 1024) free_space
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE r.tablespace_name = f.tablespace_name
ORDER BY r.tablespace_name;