Lab: CTAS with identity column
Recap:
Its a continuation to last video, we left with a puzzle where we try to perform a CTAS on a table with identity column and find the new table defination resulting in a table with no identity column.
So how we workaround it and then how we load the data is what we are going to see in this excercise.
Steps:
1) Perform CTAS to reproduce the issue
set long 20000 lines 1200 pages 30000 colsep , time on timing on trim on trims on
col owner for a30
col table_name for a20
col sequence_name for a20
col sequence_owner for a30
create table identitytab1
(
appuserid number(20,0) generated always as identity minvalue 1 maxvalue 10000 increment by 1 start with 1 cache 10 not null
,appusrname varchar2(10)
);
select * from user_sequences order by 1;
select dbms_metadata.get_ddl('TABLE','IDENTITYTAB1') from dual;
--- specifically note the keyword "generated always as"
Output:
SQL> set long 20000 lines 1200 pages 30000 colsep , time on timing on trim on trims on
col owner for a30
col table_name for a20
col sequence_name for a20
col sequence_owner for a3023:03:43 SQL> 23:03:43 SQL> 23:03:43 SQL> 23:03:43 SQL>
23:03:44 SQL> create table identitytab1
(
appuserid number(20,0) generated always as identity minvalue 1 maxvalue 10000 increment by 1 start with 1 cache 10 not null
,appusrname varchar2(10)
);23:03:48 2 23:03:48 3 23:03:48 4 23:03:48 5
Table created.
Elapsed: 00:00:00.23
23:03:49 SQL> select * from user_sequences order by 1;
SEQUENCE_NAME , MIN_VALUE, MAX_VALUE,INCREMENT_BY,C,O,CACHE_SIZE,LAST_NUMBER,S,E,S,S,K
--------------------,----------,----------,------------,-,-,----------,-----------,-,-,-,-,-
ISEQ$$_73532 , 1, 10000, 1,N,N, 10, 11,N,N,N,N,N
ISEQ$$_73545 , 1, 10000, 1,N,N, 10, 11,N,N,N,N,N
ISEQ$$_73553 , 1, 10000, 1,N,N, 10, 6,N,N,N,N,N
ISEQ$$_73557 , 1, 10000, 1,N,N, 10, 16,N,N,N,N,N
ISEQ$$_73574 , 1, 10000, 1,N,N, 10, 1,N,N,N,N,N
Elapsed: 00:00:00.03
23:03:54 SQL> select object_id from user_objects where object_name='IDENTITYTAB1';
OBJECT_ID
----------
73574
Elapsed: 00:00:00.06
23:04:28 SQL> select dbms_metadata.get_ddl('TABLE','IDENTITYTAB1') from dual;
DBMS_METADATA.GET_DDL('TABLE','IDENTITYTAB1')
--------------------------------------------------------------------------------
CREATE TABLE "OPDB1_GGTEST_USER"."IDENTITYTAB1"
( "APPUSERID" NUMBER(20,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 1
0000 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER NOCYCLE NOKEEP NOSCALE NOT
NULL ENABLE,
"APPUSRNAME" VARCHAR2(10)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "OPDB1_GGTEST_TBSPC"
Elapsed: 00:00:04.46
23:04:46 SQL>
2) Load some test data
insert into identitytab1 (appusrname)
values ('Paul');
commit;
Output:
23:04:46 SQL> 23:04:46 SQL> insert into identitytab1 (appusrname)
values ('Paul');23:04:57 2
1 row created.
Elapsed: 00:00:00.06
23:04:58 SQL>
23:05:23 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
23:05:58 SQL>
3) Perform a failure test case
Try loading a table with identity column value passed...
insert into identitytab1
values (2,'Rose');
by now you learnt that we cant supply the value for a field with "generated always" identity column.
output:
23:04:58 SQL> insert into identitytab1
values (2,'Rose');
23:05:13 2 insert into identitytab1
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
Elapsed: 00:00:00.00
23:05:13 SQL> !oerr ora 32795
32795,0000, "cannot insert into a generated always identity column"
// *Cause: An attempt was made to insert a value into an identity column
// created with GENERATED ALWAYS keywords.
// *Action: A generated always identity column cannot be directly inserted.
// Instead, the associated sequence generator must provide the value.
23:05:23 SQL>
4) Now perform a CTAS
create table ctasidentity
as select * from identitytab1;
select dbms_metadata.get_ddl('TABLE','CTASIDENTITY') from dual;
--- now compare the DDL, you will see the missing identity field defination.
Output:
23:05:58 SQL> create table ctasidentity
as select * from identitytab1;23:06:04 2
Table created.
Elapsed: 00:00:00.21
23:06:05 SQL> select dbms_metadata.get_ddl('TABLE','CTASIDENTITY') from dual;
DBMS_METADATA.GET_DDL('TABLE','CTASIDENTITY')
--------------------------------------------------------------------------------
CREATE TABLE "OPDB1_GGTEST_USER"."CTASIDENTITY"
( "APPUSERID" NUMBER(20,0) NOT NULL ENABLE, <<<<<<<<<< defination lost
"APPUSRNAME" VARCHAR2(10)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OPDB1_GGTEST_TBSPC"
Elapsed: 00:00:00.18
23:06:14 SQL>
5) Work around is to use the DDL from step 1, change the table name and create the table we need manually
drop table ctasidentity purge;
create table ctasidentity
(
appuserid number(20,0) generated always as identity minvalue 1 maxvalue 10000 increment by 1 start with 1 cache 10 not null
,appusrname varchar2(10)
);
--hmm should we "generated always as" here?
NO, we should use "GENERATED BY DEFAULT AS" instead, since this allows user also to pass the value. But wait I will end up reusing the sequence number in that case?
So we need to get the max value of the field appuserid from original table and adjust table defination in such a way that those sequences arent reused. Look at "??" below in the table.
create table ctasidentity
(
appuserid number(20,0) GENERATED BY DEFAULT AS identity minvalue 1 maxvalue 10000 increment by 1 start with 2 cache 10 not null
,appusrname varchar2(10)
);
Output:
23:06:14 SQL> drop table ctasidentity purge;
Table dropped.
Elapsed: 00:00:00.46
23:06:32 SQL> create table ctasidentity
(
appuserid number(20,0) generated always as identity minvalue 1 maxvalue 10000 increment by 1 start with 1 cache 10 not null
,appusrname varchar2(10)
);23:06:36 2 23:06:36 3 23:06:36 4 23:06:36 5
Table created.
Elapsed: 00:00:00.02
23:06:37 SQL>
output:
select * from identitytab1;23:07:09 2
insert into ctasidentity
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
Elapsed: 00:00:00.00
23:07:11 SQL> drop table ctasidentity purge;
Table dropped.
Elapsed: 00:00:00.03
23:08:42 SQL> select max(appuserid) from identitytab1;
MAX(APPUSERID)
--------------
1
Elapsed: 00:00:00.00
23:09:29 SQL> create table ctasidentity
(
appuserid number(20,0) GENERATED BY DEFAULT AS identity minvalue 1 maxvalue 10000 increment by 1 start with 2 cache 10 not null
,appusrname varchar2(10)
);23:09:43 2 23:09:43 3 23:09:43 4 23:09:43 5
Table created.
Elapsed: 00:00:00.02
23:09:44 SQL>
23:09:44 SQL> select dbms_metadata.get_ddl('TABLE','CTASIDENTITY') from dual;
DBMS_METADATA.GET_DDL('TABLE','CTASIDENTITY')
--------------------------------------------------------------------------------
CREATE TABLE "OPDB1_GGTEST_USER"."CTASIDENTITY"
( "APPUSERID" NUMBER(20,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVAL
UE 10000 INCREMENT BY 1 START WITH 2 CACHE 10 NOORDER NOCYCLE NOKEEP NOSCALE
NOT NULL ENABLE,
"APPUSRNAME" VARCHAR2(10)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "OPDB1_GGTEST_TBSPC"
Elapsed: 00:00:00.18
23:09:54 SQL>
6) Table is created we load the table using "Insert with select"
insert into ctasidentity
select * from identitytab1;
commit;
select * from ctasidentity;
Output:
23:09:54 SQL> insert into ctasidentity
select * from identitytab1;23:10:07 2
1 row created.
Elapsed: 00:00:00.02
23:10:08 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
23:10:15 SQL> select max(appuserid) from ctasidentity;
MAX(APPUSERID)
--------------
1
Elapsed: 00:00:00.00
23:10:25 SQL> select * from user_sequences order by 1;
SEQUENCE_NAME , MIN_VALUE, MAX_VALUE,INCREMENT_BY,C,O,CACHE_SIZE,LAST_NUMBER,S,E,S,S,K
--------------------,----------,----------,------------,-,-,----------,-----------,-,-,-,-,-
ISEQ$$_73532 , 1, 10000, 1,N,N, 10, 11,N,N,N,N,N
ISEQ$$_73545 , 1, 10000, 1,N,N, 10, 11,N,N,N,N,N
ISEQ$$_73553 , 1, 10000, 1,N,N, 10, 6,N,N,N,N,N
ISEQ$$_73557 , 1, 10000, 1,N,N, 10, 16,N,N,N,N,N
ISEQ$$_73574 , 1, 10000, 1,N,N, 10, 11,N,N,N,N,N
ISEQ$$_73579 , 1, 10000, 1,N,N, 10, 2,N,N,N,N,N <<< new sequence
6 rows selected.
Elapsed: 00:00:00.00
23:10:35 SQL> select object_id from user_objects where object_name='CTASIDENTITY';
OBJECT_ID
----------
73579
Elapsed: 00:00:00.03
23:10:55 SQL> select * from ctasidentity;
APPUSERID,APPUSRNAME
----------,----------
1,Paul
Elapsed: 00:00:00.01
23:11:20 SQL> select * from identitytab1;
APPUSERID,APPUSRNAME
----------,----------
1,Paul
Elapsed: 00:00:00.00
23:11:28 SQL>
7) now load some more records
insert into ctasidentity (appusrname)
values ('Chris');
-- the advantage or disadvantage of "generated by default"
insert into ctasidentity
values (4,'Tom');
-- this is also allowed ;) so you have to be careful to adjust the sequence next
Output:
23:11:44 SQL> insert into ctasidentity (appusrname)
values ('Chris');23:12:00 2
1 row created.
Elapsed: 00:00:00.00
23:12:01 SQL> select * from ctasidentity;
APPUSERID,APPUSRNAME
----------,----------
1,Paul
2,Chris
Elapsed: 00:00:00.00
23:12:08 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
23:12:12 SQL>
23:12:17 SQL> insert into ctasidentity
values (4,'Tom');23:12:30 2
1 row created.
Elapsed: 00:00:00.00
23:12:31 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
23:12:40 SQL> select * from ctasidentity;
APPUSERID,APPUSRNAME
----------,----------
1,Paul
2,Chris
4,Tom
Elapsed: 00:00:00.00
23:12:47 SQL>
In summary:
1.Dont try to insert a value on the identity column with "generated always as" keyword
2.The CTAS wont replicate the identity column, so better recreate the table using original DDL with just the idetity column tweeked as per your requirement for data copy
3.When you need the source data from original table, create the identity column with "generated by default as" but beware of the pitfalls.
YouTube:
Thank you!
