Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kunal.

Asked: October 03, 2015 - 3:45 am UTC

Last updated: October 21, 2015 - 12:11 pm UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked


We have a table which content 6lac records from last 2 years now the requirement is to archive the old data. Only 6,months data we want to keep and old data archive if required we fatch for future use.So, how can I perform this activity ?
Note- the data in table continue select, insert and update.

I suggested to create same dummy table and then truncate the old data from main table and if require to get old data in future then the dummy table was there.
Am i right?
If any other solution please guide me.

Thanks
Kunal

and Connor said...

If you have a partitioning license, I'd use dbms_redefinition to convert the table to partitioned, and then ongoing archival and maintenance becomes much easier.

If you do not have a partitioning license, then I'd still consider a form of partitioning but using "partitioned views" (google the term for lots of info) - you need to do the work yourself to manage the individual objects.



Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

archive table data

Kunal Bhatt, October 03, 2015 - 7:55 am UTC

I have not received any solution in this reply.

I want to get suggetion to do it steps.
Connor McDonald
October 04, 2015 - 1:41 am UTC

Seeing as you are looking for a series of steps to do your task, I would perhaps suggest you enlist the services of Oracle Consulting in your area. They can provide a resource on your site with the necessary expertise to help you perform the DBA function.

Archive table

Kunal, October 10, 2015 - 9:01 am UTC

Dear team,

I have tested wieth the help table redefinition but i checked the all data in the last partition. The syntax is -

SQL> CREATE TABLE PAYAGGDVADMIN.par_test
2 (
3 ORDERREQUESTID VARCHAR2(20 BYTE) NOT NULL,
4 AGGREGATORID VARCHAR2(10 BYTE) NOT NULL,
5 MERCHANTID VARCHAR2(20 BYTE) NOT NULL,
6 OPERATINGMODEID VARCHAR2(10 BYTE) NOT NULL,
7 COUNTRYCODE VARCHAR2(10 BYTE) NOT NULL,
8 CURRENCYCODE VARCHAR2(10 BYTE) NOT NULL,
9 MERCHANTORDERNO VARCHAR2(100 BYTE) NOT NULL,
MERCHANTCUSTOMERID VARCHAR2(100 BYTE) NOT NULL,
10 11 MERCHANTPOSTEDAMOUNT NUMBER(17,2) NOT NULL,
12 MERCHSUCCESSURL VARCHAR2(200 BYTE) NOT NULL,
13 MERCHFAILUREURL VARCHAR2(200 BYTE) NOT NULL,
14 MERCHOTHERDETAILS VARCHAR2(500 BYTE) NOT NULL,
15 PAYMODECODE VARCHAR2(10 BYTE),
16 ACCESSMEDIUM VARCHAR2(10 BYTE) NOT NULL,
17 SOURCEURL VARCHAR2(200 BYTE) NOT NULL,
18 PROCSTATUS CHAR(1 BYTE) NOT NULL,
CREATIONDATE DATE NOT NULL,
19 20 PROCESSEDDATE DATE NOT NULL,
21 TRANSACTIONSOURCE VARCHAR2(10 BYTE) NOT NULL)
22 PARTITION BY RANGE (CREATIONDATE)
23 (
24 PARTITION unpar_test1 VALUES LESS THAN (TO_DATE('10/07/2012', 'DD/MM/YYYY')),
25 PARTITION unpar_test2 VALUES LESS THAN (TO_DATE('15/07/2012', 'DD/MM/YYYY')),
26 PARTITION MA VALUES LESS THAN (MAXVALUE)) tablespace PAYAGGDVDATA;


1.All data in the last partition only which is MA Why it is ?

2.Can i alter the non partition table and add partition to add data it may be ? the table is OLTP.

Thanks
Kunal

Connor McDonald
October 10, 2015 - 10:14 am UTC

Sorry - but you continually send us a snippet of SQL with perhaps 1 sentence alongside it, and expect us to somehow understand what you are trying to achieve.

Can you please do the following:

- send us a create-table command
- send us a series of inserts to load the table with data
- show some queries demonstrating the problem

and add LOTS AND LOTS of commentary around all of it telling us what the problem is and what you are trying to achieve.

Otherwise we are really struggling to help you

Partition table

Kunal, October 20, 2015 - 3:58 am UTC

1. create test table

create table scott.test as select * from scott.emp;


2. gather table stats
conn sys as sysdba
pass:

exec dbms_stats.gather_table_stats('scott','TEST');

3. Create partition table
first existing table
conn scott
pass:
SQL> desc test;
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)



SQL> show user
USER is "SCOTT"
SQL>
CREATE TABLE PAR_TEST
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
PARTITION BY RANGE (HIREDATE)
(
PARTITION JFMA1980 VALUES LESS THAN (TO_DATE('30/04/1980', 'DD/MM/YYYY')),
PARTITION MJJA1980 VALUES LESS THAN (TO_DATE('30/08/1980', 'DD/MM/YYYY')),
PARTITION SOND1980 VALUES LESS THAN (TO_DATE('31/12/1980', 'DD/MM/YYYY')),
PARTITION JFMA1981 VALUES LESS THAN (TO_DATE('30/04/1981', 'DD/MM/YYYY')),
PARTITION MJJA1981 VALUES LESS THAN (TO_DATE('30/08/1981', 'DD/MM/YYYY')),
PARTITION SOND1981 VALUES LESS THAN (TO_DATE('31/12/1981', 'DD/MM/YYYY')),
PARTITION JFMA1982 VALUES LESS THAN (TO_DATE('30/04/1982', 'DD/MM/YYYY')),
PARTITION MJJA1982 VALUES LESS THAN (TO_DATE('30/08/1982', 'DD/MM/YYYY')),
PARTITION SOND1982 VALUES LESS THAN (TO_DATE('31/12/1982', 'DD/MM/YYYY')),
PARTITION JFMA1983 VALUES LESS THAN (TO_DATE('30/04/1983', 'DD/MM/YYYY')),
PARTITION MJJA1983 VALUES LESS THAN (TO_DATE('30/08/1983', 'DD/MM/YYYY')),
PARTITION SOND1983 VALUES LESS THAN (TO_DATE('31/12/1983', 'DD/MM/YYYY')),
PARTITION JFMA1984 VALUES LESS THAN (TO_DATE('30/04/1984', 'DD/MM/YYYY')),
PARTITION MJJA1984 VALUES LESS THAN (TO_DATE('30/08/1984', 'DD/MM/YYYY')),
PARTITION SOND1984 VALUES LESS THAN (TO_DATE('31/12/1984', 'DD/MM/YYYY'))
);

Table created.

Table created.




4. To check redefinition may be possible or not (sys user)

SQL> conn sys as sysdba
Enter password: ****
Connected.
SQL>
SQL>EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','TEST');

PL/SQL procedure successfully completed.


5. Create a materilized view based a CTAS, as we can see below with

SQL> BEGIN DBMS_REDEFINITION.start_redef_table
2 (
3 uname => 'scott',
4 orig_table => 'test',
5 int_table => 'par_test');
6 END;
7 /

PL/SQL procedure successfully completed.


CONNECT WITH scott USER THEN CHECK

SQL> conn scott@orcl
Enter password:
Connected.

SQL>select mview_name,container_name, build_mode from user_mviews;

MVIEW_NAME CONTAINER_NAME BUILD_MOD
------------------------------ ------------------------------ ---------
PAR_TEST PAR_TEST PREBUILT


6. Insert 1000 or n number records into the master table (test)

--This will use the mview log created by the DBMS_REDEFINITION.start_redef_table.
--Check the MLOG$_<table name> table to confirm these online updates have been recorded.

SQL> SELECT * FROM MLOG$_TEST;

EMPNO SNAPTIME$ D O CHANGE_VECTOR$$
---------- --------- - - ---------------------------------------------------------------------------
123 01-JAN-00 I N FEFF

7.
--Run the dbms_redefinition.sync_interim_table to populate the new table structure (implements a MVIEW FAST REFRESH) with the online updates. This will purge the mview log of each record applied.
--This can be run many times and should be, before we do the Finish_REDEF_TABLE.
WITH SYS AS SYSDBA USER


SQL> BEGIN
2 DBMS_REDEFINITION.sync_interim_table(
3 uname => 'scott',
4 orig_table => 'test',
5 int_table => 'par_test');
6 END;
7 /

PL/SQL procedure successfully completed.

8. Check the table sync or not

SELECT COUNT(*) FROM <ORIGINAL_TABLE>;

SELECT COUNT(*) FROM <NEW_TABLE>;

9.

ALTER TABLE par_test ADD (CONSTRAINT PAR_TESTPK1 PRIMARY KEY (a,y));


SQL> ALTER TABLE scott.PAR_TEST ADD CONSTRAINT PAR_TESTPK1 PRIMARY KEY (ORDERREQUESTID);

Table altered.

SQL> show user
USER is "SYS"

SQL> exec dbms_stats.gather_table_stats('SCOTT','PAR_TEST');

PL/SQL procedure successfully completed.
10.
--Finish_redef_table swaps the table names so the interim table becomes the original table name.
--After completing this step, the original table is redefined with the attributes and data of the interim table.
--The original table is locked briefly during this procedure.

SQL> BEGIN
2 dbms_redefinition.finish_redef_table(
3 uname => 'scott',
4 orig_table => 'test',
5 int_table => 'par_test');
6 END;
7 /

PL/SQL procedure successfully completed.

11.

Note, both tables will now be synchronised.

SELECT COUNT(*) FROM <PARTITION_TABLE>;

SELECT COUNT(*) FROM <ORIGINAL_TABLE>;

SQL> SELECT COUNT(*) FROM SCOTT.TEST;

COUNT(*)
----------
11

SQL> SELECT COUNT(*) FROM SCOTT.PAR_TEST;

COUNT(*)
----------
11

12.
--Dictionary views to confirm the change of strructure of our original table "UNPAR_TABLE".
CONN SCOTT
PASS:

SQL> SELECT partitioned FROM user_tables WHERE table_name = 'TEST';

PAR
---
YES

SQL> SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'TEST';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
JFMA1980 0
JFMA1981 3
JFMA1982 0
JFMA1983 0
JFMA1984 1
MJJA1980 0
MJJA1981 2
MJJA1982 0
MJJA1983 0
MJJA1984 0
SOND1980 0

PARTITION_NAME NUM_ROWS
------------------------------ ----------
SOND1981 3
SOND1982 0
SOND1983 1
SOND1984 0

15 rows selected.


13.

--At this point the Interim table can be dropped.
SQL> conn scott
Enter password: *****
Connected.
SQL>
SQL>
SQL>
SQL> drop TABLE par_test cascade constraints;

Table dropped.

I followed these steps .

But when create a trigger to call sequence no. Into empno it created but not call that trigger and insert nxt sequence no. Into that column.

Thanks
Connor McDonald
October 20, 2015 - 5:58 am UTC

OK, this is much better, but I'm still going to have to make some guesses here, because

a) ALTER TABLE par_test ADD (CONSTRAINT PAR_TESTPK1 PRIMARY KEY (a,y));

No idea what 'a' and 'y' area

b) ALTER TABLE scott.PAR_TEST ADD CONSTRAINT PAR_TESTPK1 PRIMARY KEY (ORDERREQUESTID);

No idea what 'orderrequestid' is

c) "But when create a trigger to call sequence no. Into empno it created..."

I'm not sure what you are askng here.

So here is what I am *assuming* is the question - that after you did the redefinition, an existing trigger to populate a column with sequence was not transferred over. So first we'll replicate that by editing your test case:

SQL> create table scott.test as select * from scott.emp;

Table created.

SQL> alter table scott.test add primary key ( empno) ;

Table altered.

SQL> create sequence scott.test_seq;

Sequence created.

SQL> create or replace trigger scott.test_trg
  2  before insert on scott.test
  3  for each row
  4  begin
  5    :new.empno := test_seq.nextval;
  6  end;
  7  /

Trigger created.

SQL> exec dbms_stats.gather_table_stats('scott','TEST');

PL/SQL procedure successfully completed.

SQL> CREATE TABLE scott.PAR_TEST
  2  (
  3  EMPNO NUMBER(4) PRIMARY KEY,
  4  ENAME VARCHAR2(10),
  5  JOB VARCHAR2(9),
  6  MGR NUMBER(4),
  7  HIREDATE DATE,
  8  SAL NUMBER(7,2),
  9  COMM NUMBER(7,2),
 10  DEPTNO NUMBER(2)
 11  )
 12  PARTITION BY RANGE (HIREDATE)
 13  (
 14  PARTITION JFMA1980 VALUES LESS THAN (TO_DATE('30/04/1980', 'DD/MM/YYYY')),
 15  PARTITION MJJA1980 VALUES LESS THAN (TO_DATE('30/08/1980', 'DD/MM/YYYY')),
 16  PARTITION SOND1980 VALUES LESS THAN (TO_DATE('31/12/1980', 'DD/MM/YYYY')),
 17  PARTITION JFMA1981 VALUES LESS THAN (TO_DATE('30/04/1981', 'DD/MM/YYYY')),
 18  PARTITION MJJA1981 VALUES LESS THAN (TO_DATE('30/08/1981', 'DD/MM/YYYY')),
 19  PARTITION SOND1981 VALUES LESS THAN (TO_DATE('31/12/1981', 'DD/MM/YYYY')),
 20  PARTITION JFMA1982 VALUES LESS THAN (TO_DATE('30/04/1982', 'DD/MM/YYYY')),
 21  PARTITION MJJA1982 VALUES LESS THAN (TO_DATE('30/08/1982', 'DD/MM/YYYY')),
 22  PARTITION SOND1982 VALUES LESS THAN (TO_DATE('31/12/1982', 'DD/MM/YYYY')),
 23  PARTITION JFMA1983 VALUES LESS THAN (TO_DATE('30/04/1983', 'DD/MM/YYYY')),
 24  PARTITION MJJA1983 VALUES LESS THAN (TO_DATE('30/08/1983', 'DD/MM/YYYY')),
 25  PARTITION SOND1983 VALUES LESS THAN (TO_DATE('31/12/1983', 'DD/MM/YYYY')),
 26  PARTITION JFMA1984 VALUES LESS THAN (TO_DATE('30/04/1984', 'DD/MM/YYYY')),
 27  PARTITION MJJA1984 VALUES LESS THAN (TO_DATE('30/08/1984', 'DD/MM/YYYY')),
 28  PARTITION SOND1984 VALUES LESS THAN (TO_DATE('31/12/1984', 'DD/MM/YYYY'))
 29  );

Table created.

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','TEST');

PL/SQL procedure successfully completed.

SQL> BEGIN DBMS_REDEFINITION.start_redef_table
  2   (
  3   uname => 'scott',
  4   orig_table => 'test',
  5   int_table => 'par_test');
  6   END;
  7   /

PL/SQL procedure successfully completed.

SQL> insert into scott.test select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> select * from scott.test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
         1 SMITH      CLERK           7902 17-DEC-80        800                    20
         2 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
         3 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
         4 JONES      MANAGER         7839 02-APR-81       2975                    20
         5 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
         6 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
         7 CLARK      MANAGER         7839 09-JUN-81       2450                    10
         8 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
         9 KING       PRESIDENT            17-NOV-81       5000                    10
        10 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
        11 ADAMS      CLERK           7788 12-JAN-83       1100                    20
        12 JAMES      CLERK           7698 03-DEC-81        950                    30
        13 FORD       ANALYST         7566 03-DEC-81       3000                    20
        14 MILLER     CLERK           7782 23-JAN-82       1300                    10

28 rows selected.

SQL> BEGIN
  2   DBMS_REDEFINITION.sync_interim_table(
  3   uname => 'scott',
  4   orig_table => 'test',
  5   int_table => 'par_test');
  6   END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT COUNT(*) FROM scott.test;

  COUNT(*)
----------
        28

SQL>
SQL> SELECT COUNT(*) FROM scott.par_Test;

  COUNT(*)
----------
        28

SQL> exec dbms_stats.gather_table_stats('SCOTT','PAR_TEST');

PL/SQL procedure successfully completed.

SQL> BEGIN
  2   dbms_redefinition.finish_redef_table(
  3   uname => 'scott',
  4   orig_table => 'test',
  5   int_table => 'par_test');
  6   END;
  7   /

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM SCOTT.TEST;

  COUNT(*)
----------
        28

SQL> SELECT COUNT(*) FROM SCOTT.PAR_TEST;

  COUNT(*)
----------
        28

SQL> SELECT partition_name, num_rows FROM all_tab_partitions WHERE table_name = 'TEST' and table_owner = 'SCOTT

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
SOND1984                                0
SOND1983                                0
SOND1982                                2
SOND1981                               10
SOND1980                                2
MJJA1984                                0
MJJA1983                                0
MJJA1982                                0
MJJA1981                                4
MJJA1980                                0
JFMA1984                                0
JFMA1983                                2
JFMA1982                                2
JFMA1981                                6
JFMA1980                                0

15 rows selected.

SQL> select owner, table_name from dba_triggers
  2  where trigger_name = 'TEST_TRG';

OWNER                                  TABLE_NAME
-----------------------------------    -------------------------
SCOTT                                  PAR_TEST

SQL> drop TABLE scott.par_test purge;

Table dropped.


So you can see the trigger did not get transferred over as part of the redefintion. That is *correct* because there is a separate routine for that.

Let's re-run the whole process with that extra step

SQL> drop table scott.test purge;

Table dropped.

SQL> create table scott.test as select * from scott.emp;

Table created.

SQL> alter table scott.test add primary key ( empno) ;

Table altered.

SQL> drop sequence scott.test_seq;

Sequence dropped.

SQL> create sequence scott.test_seq;

Sequence created.

SQL>
SQL> create or replace trigger scott.test_trg
  2  before insert on scott.test
  3  for each row
  4  begin
  5    :new.empno := test_seq.nextval;
  6  end;
  7  /

Trigger created.

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats('scott','TEST');

PL/SQL procedure successfully completed.

SQL>
SQL> CREATE TABLE scott.PAR_TEST
  2  (
  3  EMPNO NUMBER(4) PRIMARY KEY,
  4  ENAME VARCHAR2(10),
  5  JOB VARCHAR2(9),
  6  MGR NUMBER(4),
  7  HIREDATE DATE,
  8  SAL NUMBER(7,2),
  9  COMM NUMBER(7,2),
 10  DEPTNO NUMBER(2)
 11  )
 12  PARTITION BY RANGE (HIREDATE)
 13  (
 14  PARTITION JFMA1980 VALUES LESS THAN (TO_DATE('30/04/1980', 'DD/MM/YYYY')),
 15  PARTITION MJJA1980 VALUES LESS THAN (TO_DATE('30/08/1980', 'DD/MM/YYYY')),
 16  PARTITION SOND1980 VALUES LESS THAN (TO_DATE('31/12/1980', 'DD/MM/YYYY')),
 17  PARTITION JFMA1981 VALUES LESS THAN (TO_DATE('30/04/1981', 'DD/MM/YYYY')),
 18  PARTITION MJJA1981 VALUES LESS THAN (TO_DATE('30/08/1981', 'DD/MM/YYYY')),
 19  PARTITION SOND1981 VALUES LESS THAN (TO_DATE('31/12/1981', 'DD/MM/YYYY')),
 20  PARTITION JFMA1982 VALUES LESS THAN (TO_DATE('30/04/1982', 'DD/MM/YYYY')),
 21  PARTITION MJJA1982 VALUES LESS THAN (TO_DATE('30/08/1982', 'DD/MM/YYYY')),
 22  PARTITION SOND1982 VALUES LESS THAN (TO_DATE('31/12/1982', 'DD/MM/YYYY')),
 23  PARTITION JFMA1983 VALUES LESS THAN (TO_DATE('30/04/1983', 'DD/MM/YYYY')),
 24  PARTITION MJJA1983 VALUES LESS THAN (TO_DATE('30/08/1983', 'DD/MM/YYYY')),
 25  PARTITION SOND1983 VALUES LESS THAN (TO_DATE('31/12/1983', 'DD/MM/YYYY')),
 26  PARTITION JFMA1984 VALUES LESS THAN (TO_DATE('30/04/1984', 'DD/MM/YYYY')),
 27  PARTITION MJJA1984 VALUES LESS THAN (TO_DATE('30/08/1984', 'DD/MM/YYYY')),
 28  PARTITION SOND1984 VALUES LESS THAN (TO_DATE('31/12/1984', 'DD/MM/YYYY'))
 29  );

Table created.

SQL>
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','TEST');

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN DBMS_REDEFINITION.start_redef_table
  2   (
  3   uname => 'scott',
  4   orig_table => 'test',
  5   int_table => 'par_test');
  6   END;
  7   /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> insert into scott.test select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from scott.test;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
         1 SMITH      CLERK           7902 17-DEC-80        800                    20
         2 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
         3 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
         4 JONES      MANAGER         7839 02-APR-81       2975                    20
         5 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
         6 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
         7 CLARK      MANAGER         7839 09-JUN-81       2450                    10
         8 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
         9 KING       PRESIDENT            17-NOV-81       5000                    10
        10 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
        11 ADAMS      CLERK           7788 12-JAN-83       1100                    20
        12 JAMES      CLERK           7698 03-DEC-81        950                    30
        13 FORD       ANALYST         7566 03-DEC-81       3000                    20
        14 MILLER     CLERK           7782 23-JAN-82       1300                    10

28 rows selected.

SQL>
SQL>
SQL> BEGIN
  2   DBMS_REDEFINITION.sync_interim_table(
  3   uname => 'scott',
  4   orig_table => 'test',
  5   int_table => 'par_test');
  6   END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT COUNT(*) FROM scott.test;

  COUNT(*)
----------
        28

SQL>
SQL> SELECT COUNT(*) FROM scott.par_Test;

  COUNT(*)
----------
        28

SQL>
SQL>

SQL> exec dbms_stats.gather_table_stats('SCOTT','PAR_TEST');

PL/SQL procedure successfully completed.

SQL>
SQL> variable x number
SQL>
SQL> begin
  2  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
  3   uname => 'scott',
  4   orig_table => 'test',
  5   int_table => 'par_test',
  6   copy_indexes=>0,
  7   copy_constraints=>false,
  8   num_errors=>:x
  9   );
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> print x

         X
----------
         0

SQL>
SQL>
SQL> BEGIN
  2   dbms_redefinition.finish_redef_table(
  3   uname => 'scott',
  4   orig_table => 'test',
  5   int_table => 'par_test');
  6   END;
  7   /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> SELECT COUNT(*) FROM SCOTT.TEST;

  COUNT(*)
----------
        28

SQL> SELECT COUNT(*) FROM SCOTT.PAR_TEST;

  COUNT(*)
----------
        28

SQL>
SQL> SELECT partition_name, num_rows FROM all_tab_partitions WHERE table_name = 'TEST' and table_owner = 'SCOTT';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
JFMA1980                                0
JFMA1981                                6
JFMA1982                                2
JFMA1983                                2
JFMA1984                                0
MJJA1980                                0
MJJA1981                                4
MJJA1982                                0
MJJA1983                                0
MJJA1984                                0
SOND1980                                2
SOND1981                               10
SOND1982                                2
SOND1983                                0
SOND1984                                0

15 rows selected.

SQL>
SQL> select owner, table_name from dba_triggers
  2  where trigger_name = 'TEST_TRG';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCOTT                          TEST

SQL>
SQL> drop TABLE scott.par_test purge;

Table dropped.

SQL>
SQL>


See where the trigger is now ?

Hope this helps.

Archive table

kunal, October 21, 2015 - 6:02 am UTC

Thanks for reply,

I update you that was my test case; but i have done on our UAT database to follow this steps.

This is the main table's script on which i want to create a partitions
----------------------------------------------------------------------
ALTER TABLE PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT
DROP PRIMARY KEY CASCADE;
DROP TABLE PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT CASCADE CONSTRAINTS;

CREATE TABLE PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT
(
STMTRECORDID VARCHAR2(10 BYTE) NOT NULL,
PAYGATEWAYNAME VARCHAR2(100 BYTE) NOT NULL,
ATRN VARCHAR2(20 BYTE) NOT NULL,
PAYGTWTRACENO VARCHAR2(40 BYTE) NOT NULL,
GTWTXNSTATUS VARCHAR2(10 BYTE) NOT NULL,
GTWAMOUNT NUMBER(17,2) NOT NULL,
GTWAMOUNTCCY VARCHAR2(10 BYTE) NOT NULL,
DEBITACCOUNTNO VARCHAR2(100 BYTE),
GTWTRANSACIONDATE DATE,
STATUSCODE VARCHAR2(10 BYTE) NOT NULL,
REMARKS VARCHAR2(200 BYTE) NOT NULL,
CREATIONDATE DATE NOT NULL,
CREATEDBY VARCHAR2(100 BYTE) NOT NULL,
CREATEDBYSESSIONID VARCHAR2(100 BYTE) NOT NULL,
MODIFIEDDATE DATE,
MODIFIEDBY VARCHAR2(100 BYTE),
MODIFIEDBYSESSIONID VARCHAR2(100 BYTE),
GTWCONVERTEDAMOUNT NUMBER(17,2),
GTWCONVERTEDAMOUNTCCY VARCHAR2(100 BYTE),
DEBITACCOUNTBRANCHCODE VARCHAR2(100 BYTE),
GTWREFERENCENUMBER VARCHAR2(20 BYTE)
)
TABLESPACE PAYAGGDATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX PAYAGGADMIN.AGGMERCHORDRRPAYGTWSTMT_INDEX ON PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT
(ATRN, STATUSCODE)
LOGGING
TABLESPACE PAYAGGDATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX PAYAGGADMIN.AGGMERCHPAYGTWATRN ON PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT
(ATRN)
LOGGING
TABLESPACE PAYAGGDATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX PAYAGGADMIN.AGGMERCHORDERPAYGTWSTMT_INDEX ON PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT
(STATUSCODE)
LOGGING
TABLESPACE PAYAGGDATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE UNIQUE INDEX PAYAGGADMIN.AGGMERCHORDERPAYGTWSTMTPK ON PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT
(STMTRECORDID)
LOGGING
TABLESPACE PAYAGGDATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE OR REPLACE TRIGGER PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT_TRG
BEFORE INSERT
ON PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
-- For Toad: Highlight column STMTRECORDID
:new.STMTRECORDID:= AGGMERCHANTORDERPAYGTWSTM_SEQ.nextval;
END AGGMERCHANTORDERPAYGTWSTMT_TRG;
/




DROP SYNONYM PAYAGGREAD.AGGMERCHANTORDERPAYGTWSTMT;

CREATE SYNONYM PAYAGGREAD.AGGMERCHANTORDERPAYGTWSTMT FOR PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT;


DROP SYNONYM PAYAGGUSER.AGGMERCHANTORDERPAYGTWSTMT;

CREATE SYNONYM PAYAGGUSER.AGGMERCHANTORDERPAYGTWSTMT FOR PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT;


ALTER TABLE PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT ADD (
CONSTRAINT GTWTXNSTATUS_PAYGTWSTMT
CHECK (GTWTXNSTATUS in ('SUCCESS','FAILED','REFUND')),
CONSTRAINT AGGMERCHORDERPAYGTWSTMTPK
PRIMARY KEY
(STMTRECORDID)
USING INDEX
TABLESPACE PAYAGGDATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
));

ALTER TABLE PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT ADD (
CONSTRAINT AGGMERCHORDERPAYGTWSTMTATRNFK
FOREIGN KEY (ATRN)
REFERENCES PAYAGGADMIN.AGGREGATORMERCHANTORDER (ATRN),
CONSTRAINT AGGMERORDERPAYGTWSTMTSTSFK
FOREIGN KEY (STATUSCODE)
REFERENCES PAYAGGADMIN.STATUSMASTER (STATUSCODE));

GRANT SELECT ON PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT TO PAYAGGREAD;

GRANT DELETE, INSERT, SELECT, UPDATE ON PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT TO PAYAGGUSER;
============================================

Partition steps
******************

1.

SQL> SELECT COUNT(*) FROM PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT;

COUNT(*)
----------
6250

2. gather table stats

SQL>exec dbms_stats.gather_table_stats('PAYAGGADMIN','AGGMERCHANTORDERPAYGTWSTMT');

PL/SQL procedure successfully completed.

3.

3. Create partition table

create table PAYAGGADMIN.PAR_AGGMERCHANTORDERPAYGTWSTMT (

CREATE TABLE PAYAGGADMIN.PAR_AGGMERCHANTORDERPAYGTWSTMT
(
STMTRECORDID VARCHAR2(10 BYTE) NOT NULL,
PAYGATEWAYNAME VARCHAR2(100 BYTE) NOT NULL,
ATRN VARCHAR2(20 BYTE) NOT NULL,
PAYGTWTRACENO VARCHAR2(40 BYTE) NOT NULL,
GTWTXNSTATUS VARCHAR2(10 BYTE) NOT NULL,
GTWAMOUNT NUMBER(17,2) NOT NULL,
GTWAMOUNTCCY VARCHAR2(10 BYTE) NOT NULL,
DEBITACCOUNTNO VARCHAR2(100 BYTE),
GTWTRANSACIONDATE DATE,
STATUSCODE VARCHAR2(10 BYTE) NOT NULL,
REMARKS VARCHAR2(200 BYTE) NOT NULL,
CREATIONDATE DATE NOT NULL,
CREATEDBY VARCHAR2(100 BYTE) NOT NULL,
CREATEDBYSESSIONID VARCHAR2(100 BYTE) NOT NULL,
MODIFIEDDATE DATE,
MODIFIEDBY VARCHAR2(100 BYTE),
MODIFIEDBYSESSIONID VARCHAR2(100 BYTE),
GTWCONVERTEDAMOUNT NUMBER(17,2),
GTWCONVERTEDAMOUNTCCY VARCHAR2(100 BYTE),
DEBITACCOUNTBRANCHCODE VARCHAR2(100 BYTE),
GTWREFERENCENUMBER VARCHAR2(20 BYTE))
PARTITION BY RANGE (CREATIONDATE)
(
PARTITION YEAR2012 VALUES LESS THAN (TO_DATE('12/31/2012','MM/DD/YYYY')) TABLESPACE PAYAGGDATA,
PARTITION YEAR2013 VALUES LESS THAN (TO_DATE('12/31/2013','MM/DD/YYYY')) TABLESPACE PAYAGGDATA,
PARTITION YEAR2014 VALUES LESS THAN (TO_DATE('12/31/2014','MM/DD/YYYY')) TABLESPACE PAYAGGDATA,
PARTITION YEAR2015 VALUES LESS THAN (TO_DATE('12/31/2015','MM/DD/YYYY')) TABLESPACE PAYAGGDATA,
PARTITION YEAR2016 VALUES LESS THAN (TO_DATE('12/31/2016','MM/DD/YYYY')) TABLESPACE PAYAGGDATA,
PARTITION YEAR2017 VALUES LESS THAN (TO_DATE('12/31/2017','MM/DD/YYYY')) TABLESPACE PAYAGGDATA
);


4. To check redefinition may be possible or not (sys user)

SQL>EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('PAYAGGADMIN','AGGMERCHANTORDERPAYGTWSTMT');

5. Create a materilized view based a CTAS, as we can see below with

BEGIN DBMS_REDEFINITION.start_redef_table
(
uname => 'PAYAGGADMIN',
orig_table => 'AGGMERCHANTORDERPAYGTWSTMT',
int_table => 'PAR_AGGMERCHANTORDERPAYGTWSTMT');
END;
/



SQL> conn PAYAGGADMIN@PAYAGGUAT
Enter password:
Connected.

SQL> select mview_name,container_name, build_mode from user_mviews;

MVIEW_NAME CONTAINER_NAME BUILD_MOD
------------------------------ ------------------------------ ---------
PAR_AGGMERCHANTORDERPAYGTWSTMT PAR_AGGMERCHANTORDERPAYGTWSTMT PREBUILT





6. Insert 1000 or n number records into the master table (NONNON_TAB)

--This will use the mview log created by the DBMS_REDEFINITION.start_redef_table.
--Check the MLOG$_<table name> table to confirm these online updates have been recorded.
payaggadmin user

SQL> select count(*) from MLOG$_AGGMERCHANTORDERPAYGTWSTMT;
select count(*) from MLOG$_AGGMERCHANTORDERPAYGTWSTMT
*
ERROR at line 1:
ORA-00972: identifier is too long


SQL> SELECT TNAME FROM TAB WHERE TNAME LIKE '%MLOG%';

TNAME
------------------------------
MLOG$_AGGMERCHANTORDERPAYG

SQL> SELECT COUNT(*) FROM MLOG$_AGGMERCHANTORDERPAYG;

COUNT(*)
----------
0


7.
--Run the dbms_redefinition.sync_interim_table to populate the new table structure (implements a MVIEW FAST REFRESH) with the online updates. This will purge the mview log of each record applied.
--This can be run many times and should be, before we do the Finish_REDEF_TABLE.
WITH SYS AS SYSDBA USER

BEGIN
DBMS_REDEFINITION.sync_interim_table(
uname => 'PAYAGGADMIN',
orig_table => 'AGGMERCHANTORDERPAYGTWSTMT',
int_table => 'PAR_AGGMERCHANTORDERPAYGTWSTMT');
END;
/

8.

--The dependent objects will need to be created against the new table. This is done using the COPY_TABLE_DEPENDENTS procedure. You can decide which dependencies should be copied.

SQL> SET SERVEROUTPUT ON
DECLARE
SQL> 2 l_errors NUMBER;
3 BEGIN
4 DBMS_REDEFINITION.copy_table_dependents(
5 uname => 'PAYAGGADMIN',
6 orig_table => 'AGGMERCHANTORDERPAYGTWSTMT',
7 int_table => 'PAR_AGGMERCHANTORDERPAYGTWSTMT',
8 copy_indexes => DBMS_REDEFINITION.cons_orig_params,
9 copy_triggers => TRUE,
10 copy_constraints => TRUE,
11 copy_privileges => TRUE,
12 ignore_errors => FALSE,
13 num_errors => l_errors,
14 copy_statistics => FALSE,
15 copy_mvlog => FALSE);
16 DBMS_OUTPUT.put_line('Errors=' || l_errors);
17 END;
18 /
DECLARE
*
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 984
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1899
ORA-06512: at line 4



9.
Check the table sync or not

ORIGINAL_TABLE = AGGMERCHANTORDERPAYGTWSTMT
NEW_PARTITON_TABLE = PAR_AGGMERCHANTORDERPAYGTWSTMT

SELECT COUNT(*) FROM <ORIGINAL_TABLE>;

SELECT COUNT(*) FROM <NEW_TABLE>;

10.
Same primary of main table check and create on this table

ALTER TABLE PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT ADD CONSTRAINT PRIMARY KEY (STMTRECORDID);

ALTER TABLE PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT ADD (
CONSTRAINT GTWTXNSTATUS_PAYGTWSTMT
CHECK (GTWTXNSTATUS in ('SUCCESS','FAILED','REFUND')),
CONSTRAINT AGGMERCHORDERPAYGTWSTMTPK
PRIMARY KEY
(STMTRECORDID)

SQL> ALTER TABLE PAYAGGADMIN.PAR_AGGMERCHANTORDERPAYGTWSTMT ADD CONSTRAINT PAR_AGGMERCHORDERPAYGTWSTMTPK PRIMARY KEY (STMTRECORDID);

Table altered.


11.

SQL> show user
USER is "SYS"
SQL>exec dbms_stats.gather_table_stats('PAYAGGADMIN','PAR_AGGMERCHANTORDERPAYGTWSTMT');

12.

BEGIN
dbms_redefinition.finish_redef_table(
uname => 'PAYAGGADMIN',
orig_table => 'AGGMERCHANTORDERPAYGTWSTMT',
int_table => 'PAR_AGGMERCHANTORDERPAYGTWSTMT');
END;
/


13.

Note, both tables will now be synchronised.

SELECT COUNT(*) FROM <PARTITION_TABLE>;

SELECT COUNT(*) FROM <ORIGINAL_TABLE>;

14.

CONN PAYAGGADMIN
PASS:


SQL>SELECT partitioned FROM user_tables WHERE table_name = 'AGGMERCHANTORDERPAYGTWSTMT';

PAR
---
YES


SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'AGGMERCHANTORDERPAYGTWSTMT';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
YEAR2012 0
YEAR2013 0
YEAR2014 35
YEAR2015 96
YEAR2016 0
YEAR2017 0


13.

Rename the constraint name -

SELECT * FROM all_cons_columns WHERE TABLE_NAME='AGGMERCHANTORDERPAYGTWSTMT'

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
------------------------------------------------------------------------
PAYAGGDVADMIN PARPAR_TABPK NONNON_TAB ORDERREQUESTID 1


SELECT * FROM all_cons_columns WHERE TABLE_NAME='PAR_AGGMERCHANTORDERPAYGTWSTMT'

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
------------------------------------------------------------------------
PAYAGGDVADMIN PARPAR_TABPK PARPAR_TAB ORDERREQUESTID 1

ALTER TABLE AGGMERCHANTORDERPAYGTWSTMT RENAME CONSTRAINT "PAR_AGGMERCHORDERPAYGTWSTMTPK" TO "AGGMERCHORDERPAYGTWSTMTPK"


14.

--At this point the Interim table can be dropped.

DROP TABLE PAR_AGGMERCHANTORDERPAYGTWSTMT cascade constraints;

***************************************

SELECT * FROM USER_CONSTRAINTS WHERE OWNER='PAYAGGADMIN' AND TABLE_NAME='AGGMERCHANTORDERPAYGTWSTMT';

SELECT * FROM USER_INDEXES WHERE TABLE_OWNER='PAYAGGADMIN' AND TABLE_NAME='AGGMERCHANTORDERPAYGTWSTMT';

SELECT * FROM USER_TRIGGERS WHERE TABLE_OWNER='PAYAGGADMIN' AND TABLE_NAME='AGGMERCHANTORDERPAYGTWSTMT';





ALTER TABLE PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT ADD (
CONSTRAINT GTWTXNSTATUS_PAYGTWSTMT
CHECK (GTWTXNSTATUS in ('SUCCESS','FAILED','REFUND')));


ALTER TABLE PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT ADD (
CONSTRAINT AGGMERCHORDERPAYGTWSTMTATRNFK
FOREIGN KEY (ATRN)
REFERENCES PAYAGGADMIN.AGGREGATORMERCHANTORDER (ATRN),
CONSTRAINT AGGMERORDERPAYGTWSTMTSTSFK
FOREIGN KEY (STATUSCODE)
REFERENCES PAYAGGADMIN.STATUSMASTER (STATUSCODE));


TAKE FROM BACKUP TABLE
----------------------

CREATE OR REPLACE TRIGGER PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT_TRG
BEFORE INSERT
ON PAYAGGADMIN.AGGMERCHANTORDERPAYGTWSTMT REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
-- For Toad: Highlight column STMTRECORDID
:new.STMTRECORDID:= AGGMERCHANTORDERPAYGTWSTM_SEQ.nextval;
END AGGMERCHANTORDERPAYGTWSTMT_TRG;
/
----------DONE----------------------------------------------------------------------------------------------------------------------------------------

1. It has completed successfully but when insert the record from front-end that it returns an error like STMTRECORDID can not be null; because it is not calling trigger ?
2. Manually insert the record successfully and i can see it into the table to select but not showing into the partition with this query

SELECT PARTITION_NAME,NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='AGGMERCHANTORDERPAYGTWSTMT';

When i gather statistics with this

exec dbms_stats.gather_table_stats('PAYAGGADMIN','AGGMERCHANTORDERPAYGTWSTMT');

Then i can see that record into the partition So, i need every time to collect to gather statistics ?

Now, i thik i have clear all the picture regarding the partition and facing issue So, please Share your knowledge regarding the same.

Thanks
Kunal



Connor McDonald
October 21, 2015 - 12:11 pm UTC

Your output suggests that your call to: DBMS_REDEFINITION.copy_table_dependents failed. That would explain why some objects were not copied over.

A reader, October 21, 2015 - 3:04 pm UTC

Thanks for reply.

As I know it is not failed because it tried to make not null the coloumn but tha column already not null. I after done this activity I checked trigger was there but it is not calling; my question is this.

Thank
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.