Thursday, February 14, 2019

Oracle 12c Create Table with Auto Increment and Flashback

CREATE TABLE table_name ( 
    id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 NOCACHE ) NOT NULL, 
    ctimestamp TIMESTAMP DEFAULT current_timestamp 
) NOLOGGING; 

CREATE UNIQUE INDEX table_name_pk ON table_name (id ASC ); 

ALTER TABLE table_name ADD CONSTRAINT table_name_pk PRIMARY KEY ( id ); 

SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "NOW" FROM DUAL; 

CREATE TABLESPACE archivetest_tabspace DATAFILE SIZE 10M SEGMENT SPACE MANAGEMENT AUTO; 

CREATE FLASHBACK ARCHIVE archivetest TABLESPACE archivetest_tabspace QUOTA 50M RETENTION 7 DAY; 

ALTER TABLE table_name FLASHBACK ARCHIVE archivetest; 

SELECT * FROM table_name AS OF TIMESTAMP TO_TIMESTAMP ('2018-11-29 14:05:11', 'YYYY-MM-DD HH24:MI:SS');