Situation:
We may need a table as it existed in the past as part of the below operation demand...
1. USER MISTAKE: Incorrect user input and the table in production is in bad state, so we need to go back in the past to retrieve the proper content and correct user mistake
2. ROOT CAUSE ANALYSIS: Application might have legitimate interest to look back in time to see what was value of a record as in the past
3. Application upgrade failure, DDL on the table to be reverted back
Solution:
Using Oracle FLASHBACK feature of oracle..
A. We can go back to past timestamp from current timestamp
B. Retrieve our data
C. Rollforward to the current timestamp
Prereqs:
1. You need Flashback enabled or Guranteed restore point created to go back into a particular time.
2. Archivelog mode should be ON and all the archivelogs from past timestamp until the current timestamp should be available.
Steps)
1. Verify if prereqs are met?
Command:
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
set lines 300
set pages 3000
set colsep ,
select name,open_mode,database_role,db_unique_name,name,flashback_on,log_mode,force_logging from v$database;
select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;
select sequence#,status,completion_time,first_change#,next_change# from v$archived_log order by 1;
Actual output:
SQL> alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
Session altered.
SQL> set lines 300 set pages 3000 set colsep ,SQL> SQL> SQL> select name,open_mode,database_role,db_unique_name,name,flashback_on,log_mode,force_logging from v$database;
NAME ,OPEN_MODE ,DATABASE_ROLE ,DB_UNIQUE_NAME ,NAME ,FLASHBACK_ON ,LOG_MODE ,FORCE_LOGGING ---------,--------------------,----------------,------------------------------,---------,------------------,------------,--------------------------------------- GGTGT04T ,READ WRITE ,PRIMARY ,GGTGT04T ,GGTGT04T ,RESTORE POINT ONLY,ARCHIVELOG ,YES
SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;
SCN,GUA,NAME ----------,---,-------------------------------------------------------------------------------------------------------------------------------- 1032103,YES,PRE_GGREPLICAT
SQL> select sequence#,status,completion_time,first_change#,next_change# from v$archived_log order by 1;
SEQUENCE#,S,COMPLETION_TIME ,FIRST_CHANGE#,NEXT_CHANGE# ----------,-,--------------------,-------------,------------ 1,A,30/SEP/2023 09:29:32, 1017565, 1093405
SQL> select * from v$log;
GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME , CON_ID ----------,----------,----------,----------,----------,----------,---,----------------,-------------,--------------------,------------,--------------------,---------- 1, 1, 1, 209715200, 512, 1,YES,INACTIVE , 1017565,26/AUG/2023 21:44:36, 1093405,30/SEP/2023 09:29:30, 0 2, 1, 2, 209715200, 512, 1,NO ,CURRENT , 1093405,30/SEP/2023 09:29:30, 1.8447E+19, , 0 3, 1, 0, 209715200, 512, 1,YES,UNUSED , 0, , 0, , 0
SQL>
|
2. Flashback the database to PRE_GGREPLICAT (go to past)
shu immediate;
startup mount;
flashback database to PRE_GGREPLICAT;
set lines 300
set pages 3000
set colsep ,
select name,open_mode,database_role,db_unique_name,name,flashback_on,log_mode,force_logging from v$database;
Actual output:
SQL> shu immediate; startup mount; Database closed. Database dismounted. ORACLE instance shut down. SQL> ORACLE instance started.
Total System Global Area,3221222464,bytes Fixed Size , 8901696,bytes Variable Size ,1107296256,bytes Database Buffers ,2097152000,bytes Redo Buffers , 7872512,bytes Database mounted. SQL> SQL> SQL> select name,open_mode,database_role,db_unique_name,name,flashback_on,log_mode,force_logging from v$database;
NAME ,OPEN_MODE ,DATABASE_ROLE ,DB_UNIQUE_NAME ,NAME ,FLASHBACK_ON ,LOG_MODE ,FORCE_LOGGING ---------,--------------------,----------------,------------------------------,---------,------------------,------------,--------------------------------------- GGTGT04T ,MOUNTED ,PRIMARY ,GGTGT04T ,GGTGT04T ,RESTORE POINT ONLY,ARCHIVELOG ,YES
SQL>
SQL> flashback database to restore point PRE_GGREPLICAT;
Flashback complete.
SQL> select name,open_mode,database_role,db_unique_name,name,flashback_on,log_mode,force_logging from v$database;
NAME ,OPEN_MODE ,DATABASE_ROLE ,DB_UNIQUE_NAME ,NAME ,FLASHBACK_ON ,LOG_MODE ,FORCE_LOGGING ---------,--------------------,----------------,------------------------------,---------,------------------,------------,--------------------------------------- GGTGT04T ,MOUNTED ,PRIMARY ,GGTGT04T ,GGTGT04T ,RESTORE POINT ONLY,ARCHIVELOG ,YES
SQL> select name,open_mode,checkpoint_change#,controlfile_change# from v$database;
NAME ,OPEN_MODE ,CHECKPOINT_CHANGE#,CONTROLFILE_CHANGE# ---------,--------------------,------------------,------------------- GGTGT04T ,MOUNTED , 1118165, 1032104
SQL> select distinct(checkpoint_change#) from v$datafile;
CHECKPOINT_CHANGE# ------------------ 1032104
SQL>
|
2. Open the DB in read only mode
alter database open read only;
set lines 300
set pages 3000
set colsep ,
select name,open_mode,database_role,db_unique_name,name,flashback_on,log_mode,force_logging from v$database;
Actual output:
SQL> alter database open read only;
Database altered.
SQL> select name,open_mode,database_role,db_unique_name,name,flashback_on,log_mode,force_logging from v$database;
NAME ,OPEN_MODE ,DATABASE_ROLE ,DB_UNIQUE_NAME ,NAME ,FLASHBACK_ON ,LOG_MODE ,FORCE_LOGGING ---------,--------------------,----------------,------------------------------,---------,------------------,------------,--------------------------------------- GGTGT04T ,READ ONLY ,PRIMARY ,GGTGT04T ,GGTGT04T ,RESTORE POINT ONLY,ARCHIVELOG ,YES
SQL> select parameter,value from v$option where upper(parameter) like '%VAULT%';
PARAMETER ,VALUE ----------------------------------------------------------------,---------------------------------------------------------------- Oracle Database Vault ,TRUE
SQL>
|
Note before flashback I had vault disabled, but after flashback it came alive. Since my db had vault activated always.
3. Export the schema ENCVAULT_TEST
exp OWNER=ENCVAULT_TEST buffer=100000 file=/tmp/encvault_test_preflashback1.dmp log=/tmp/encvault_test_preflashback1.log
>>> it will fail because we have TDE enabled.
Actual output:
[oracle@vcentos79-oracle-ggtgt ~]$ exp OWNER=ENCVAULT_TEST buffer=100000 file=/tmp/encvault_test_preflashback1.dmp log=/tmp/encvault_test_preflashback1.log
Export: Release 19.0.0.0.0 - Production on Sat Sep 30 14:41:19 2023 Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ... . exporting pre-schema procedural objects and actions ... . exporting cluster definitions . about to export ENCVAULT_TEST's tables via Conventional Path ... EXP-00111: Table ENCRYPT_TAB1 resides in an Encrypted Tablespace ENCRYPT_TS1 and will not be exported . exporting synonyms . exporting views ... Export terminated successfully with warnings. [oracle@vcentos79-oracle-ggtgt ~]$
|
4. If step 3 fails, export the data as csv using the pl/sql proc
variable p_sql varchar2(1000);
variable p_dir varchar2(1000);
variable p_file varchar2(2000);
exec :p_sql:='select * from ENCVAULT_TEST.ENCRYPT_TAB1';
exec :p_dir:='DATA_PUMP_DIR';
exec :p_file:='30Sep2023_actual_ENCRYPT_TAB1.csv';
exec :p_sql:='select * from ENCVAULT_TEST.GGTEST1';
exec :p_file:='30Sep2023_actual_GGTEST1.csv';
col :p_sql for a45
col :p_dir for a20
col :p_file for a45
select :p_sql,:p_dir,:p_file from dual;
declare
v_finaltxt VARCHAR2(4000);
v_v_val VARCHAR2(4000);
v_n_val NUMBER;
v_d_val DATE;
v_ret NUMBER;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;
v_fh UTL_FILE.FILE_TYPE;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, :p_sql, DBMS_SQL.NATIVE);
d := DBMS_SQL.EXECUTE(c);
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
ELSE
DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
END CASE;
END LOOP;
-- This part outputs the HEADER
v_fh := UTL_FILE.FOPEN(upper(:p_dir),:p_file,'w',32767);
FOR j in 1..col_cnt
LOOP
v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
END LOOP;
UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
-- This part outputs the DATA
LOOP
v_ret := DBMS_SQL.FETCH_ROWS(c);
EXIT WHEN v_ret = 0;
v_finaltxt := NULL;
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
ELSE
DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
END CASE;
END LOOP;
-- DBMS_OUTPUT.PUT_LINE(v_finaltxt);
UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
END LOOP;
UTL_FILE.FCLOSE(v_fh);
DBMS_SQL.CLOSE_CURSOR(c);
END;
/
Actual output:
SQL> variable p_sql varchar2(1000); variable p_dir varchar2(1000); variable p_file varchar2(2000);
exec :p_sql:='select * from ENCVAULT_TEST.ENCRYPT_TAB1'; exec :p_dir:='DATA_PUMP_DIR'; exec :p_file:='30Sep2023_actual_ENCRYPT_TAB1.csv';SQL> SQL> SQL> SQL> PL/SQL procedure successfully completed.
SQL> PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL> col :p_sql for a45 col :p_dir for a20 col :p_file for a30 select :p_sql,:p_dir,:p_file from dual;SQL> SQL> SQL>
:P_SQL ,:P_DIR ,:P_FILE ---------------------------------------------,--------------------,------------------------------ select * from ENCVAULT_TEST.ENCRYPT_TAB1 ,DATA_PUMP_DIR ,30Sep2023_actual_ENCRYPT_TAB1. , ,csv
SQL> col :p_file for a75 SQL> /
:P_SQL ,:P_DIR ,:P_FILE ---------------------------------------------,--------------------,--------------------------------------------------------------------------- select * from ENCVAULT_TEST.ENCRYPT_TAB1 ,DATA_PUMP_DIR ,30Sep2023_actual_ENCRYPT_TAB1.csv
SQL> declare v_finaltxt VARCHAR2(4000); v_v_val VARCHAR2(4000); v_n_val NUMBER; v_d_val DATE; v_ret NUMBER; c NUMBER; d NUMBER; col_cnt INTEGER; f BOOLEAN; rec_tab DBMS_SQL.DESC_TAB; col_num NUMBER; v_fh UTL_FILE.FILE_TYPE; BEGIN 2 3 4 5 6 7 8 9 10 11 12 13 14 15 c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, :p_sql, DBMS_SQL.NATIVE); d := DBMS_SQL.EXECUTE(c); DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab); FOR j in 1..col_cnt LOOP CASE rec_tab(j).col_type WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000); WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val); WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val); ELSE DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000); END CASE; END LOOP; -- This part outputs the HEADER v_fh := UTL_FILE.FOPEN(upper(:p_dir),:p_file,'w',32767); FOR j in 1..col_cnt LOOP v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),','); END LOOP; UTL_FILE.PUT_LINE(v_fh, v_finaltxt); -- This part outputs the DATA LOOP v_ret := DBMS_SQL.FETCH_ROWS(c); EXIT WHEN v_ret = 0; v_finaltxt := NULL; FOR j in 1..col_cnt LOOP CASE rec_tab(j).col_type WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val); v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',','); WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val); v_finaltxt := ltrim(v_finaltxt||','||v_n_val,','); WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val); v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),','); ELSE DBMS_SQL.COLUMN_VALUE(c,j,v_v_val); v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',','); END CASE; 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 END LOOP; -- DBMS_OUTPUT.PUT_LINE(v_finaltxt); UTL_FILE.PUT_LINE(v_fh, v_finaltxt); 55 56 57 END LOOP; UTL_FILE.FCLOSE(v_fh); 58 59 DBMS_SQL.CLOSE_CURSOR(c); END; 60 61 / declare * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SQL", line 1721 ORA-06512: at line 17
SQL>
|
4b. If step 4 fails use the below to dump the data to csv from sqlplus
set trims on
set trim on
set lines 1200
set headsep off
set pagesize 0
set trimspool on
set serveroutput on
spool C:\vagrant\ENCRYPT_TAB1_AT2.csv
variable p_sql varchar2(1000);
exec :p_sql:='select * from ENCVAULT_TEST.ENCRYPT_TAB1';
declare
v_finaltxt VARCHAR2(4000);
v_v_val VARCHAR2(4000);
v_n_val NUMBER;
v_d_val DATE;
v_ret NUMBER;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;
v_fh UTL_FILE.FILE_TYPE;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, :p_sql, DBMS_SQL.NATIVE);
d := DBMS_SQL.EXECUTE(c);
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
ELSE
DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
END CASE;
END LOOP;
-- This part outputs the HEADER
-- v_fh := UTL_FILE.FOPEN(upper(:p_dir),:p_file,'w',32767);
FOR j in 1..col_cnt
LOOP
v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
END LOOP;
-- UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
dbms_output.put_line(v_finaltxt);
-- This part outputs the DATA
LOOP
v_ret := DBMS_SQL.FETCH_ROWS(c);
EXIT WHEN v_ret = 0;
v_finaltxt := NULL;
FOR j in 1..col_cnt
LOOP
CASE rec_tab(j).col_type
WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
v_finaltxt := ltrim(v_finaltxt||','||v_v_val||'',',');
WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
ELSE
DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
v_finaltxt := ltrim(v_finaltxt||','||v_v_val||'',',');
END CASE;
END LOOP;
-- DBMS_OUTPUT.PUT_LINE(v_finaltxt);
-- UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
dbms_output.put_line(v_finaltxt);
END LOOP;
-- UTL_FILE.FCLOSE(v_fh);
DBMS_SQL.CLOSE_CURSOR(c);
END;
/
spool off
Actual output:
Info collected to CSV. Just trimmed down the query and header info from the output and copied the row data alone to the actual db server for next step.
>done
5. Mount the db back
shu immediate;
startup mount;
set lines 300
set pages 3000
set colsep ,
select name,open_mode,database_role,db_unique_name,name,flashback_on,log_mode,force_logging,checkpoint_change#,controlfile_change# from v$database;
select * from v$log;
Actual output:
SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount; ORACLE instance started.
Total System Global Area 3221222464 bytes Fixed Size 8901696 bytes Variable Size 1107296256 bytes Database Buffers 2097152000 bytes Redo Buffers 7872512 bytes Database mounted. SQL> set lines 300 set pages 3000 set colsep ,SQL> SQL> SQL> select name,open_mode,database_role,db_unique_name,name,flashback_on,log_mode,force_logging,checkpoint_change#,controlfile_change# from v$database;
NAME ,OPEN_MODE ,DATABASE_ROLE ,DB_UNIQUE_NAME ,NAME ,FLASHBACK_ON ,LOG_MODE ,FORCE_LOGGING ,CHECKPOINT_CHANGE#,CONTROLFILE_CHANGE# ---------,--------------------,----------------,------------------------------,---------,------------------,------------,---------------------------------------,------------------,------------------- GGTGT04T ,MOUNTED ,PRIMARY ,GGTGT04T ,GGTGT04T ,RESTORE POINT ONLY,ARCHIVELOG ,YES , 1118165, 1032104
SQL> select * from v$log;
GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIM,NEXT_CHANGE#,NEXT_TIME, CON_ID ----------,----------,----------,----------,----------,----------,---,----------------,-------------,---------,------------,---------,---------- 1, 1, 1, 209715200, 512, 1,YES,INACTIVE , 1017565,26-AUG-23, 1093405,30-SEP-23, 0 3, 1, 0, 209715200, 512, 1,YES,UNUSED , 0, , 0, , 0 2, 1, 2, 209715200, 512, 1,NO ,CURRENT , 1093405,30-SEP-23, 1.8447E+19, , 0
SQL>
|
6. Recover the db back to its original point in time (current timestamp)
recover database;
alter database open;
Actual output:
SQL> recover database; Media recovery complete. SQL> select name,open_mode,database_role,db_unique_name,name,flashback_on,log_mode,force_logging,checkpoint_change#,controlfile_change# from v$database;
NAME ,OPEN_MODE ,DATABASE_ROLE ,DB_UNIQUE_NAME ,NAME ,FLASHBACK_ON ,LOG_MODE ,FORCE_LOGGING ,CHECKPOINT_CHANGE#,CONTROLFILE_CHANGE# ---------,--------------------,----------------,------------------------------,---------,------------------,------------,---------------------------------------,------------------,------------------- GGTGT04T ,MOUNTED ,PRIMARY ,GGTGT04T ,GGTGT04T ,RESTORE POINT ONLY,ARCHIVELOG ,YES , 1118165, 1032104
SQL> select distinct(checkppi oint_change#) from vR $datafile;
CHECKPOINT_CHANGE# ------------------ 1118163
SQL> alter database open;
Database altered.
SQL> select name,open_mode,database_role,db_unique_name,name,flashback_on,log_mode,force_logging,checkpoint_change#,controlfile_change# from v$database;
NAME ,OPEN_MODE ,DATABASE_ROLE ,DB_UNIQUE_NAME ,NAME ,FLASHBACK_ON ,LOG_MODE ,FORCE_LOGGING ,CHECKPOINT_CHANGE#,CONTROLFILE_CHANGE# ---------,--------------------,----------------,------------------------------,---------,------------------,------------,---------------------------------------,------------------,------------------- GGTGT04T ,READ WRITE ,PRIMARY ,GGTGT04T ,GGTGT04T ,RESTORE POINT ONLY,ARCHIVELOG ,YES , 1118168, 1118425
SQL> |
7. Load the data back from csv file to another schema and tablespace
create user ENCVAULT_TEST_PREV identified by ENCVAULT_TEST_PREV default tablespace USERS;
alter user ENCVAULT_TEST_PREV quota unlimited on USERS;
drop table ENCVAULT_TEST_PREV.ENCRYPT_TAB1_EXT;
CREATE TABLE ENCVAULT_TEST_PREV.ENCRYPT_TAB1_EXT ("OWNER" VARCHAR2(128),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(128),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(23),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(128),
"SHARING" VARCHAR2(18),
"EDITIONABLE" VARCHAR2(1),
"ORACLE_MAINTAINED" VARCHAR2(1),
"APPLICATION" VARCHAR2(1),
"DEFAULT_COLLATION" VARCHAR2(100),
"DUPLICATED" VARCHAR2(1),
"SHARDED" VARCHAR2(1),
"CREATED_APPID" NUMBER,
"CREATED_VSNID" NUMBER,
"MODIFIED_APPID" NUMBER,
"MODIFIED_VSNID" NUMBER
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATA_PUMP_DIR
ACCESS PARAMETERS (
FIELDS TERMINATED BY "," LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
("OWNER" CHAR(128),
"OBJECT_NAME" CHAR(128),
"SUBOBJECT_NAME" CHAR(128),
"OBJECT_ID" CHAR(100),
"DATA_OBJECT_ID" CHAR(100),
"OBJECT_TYPE" CHAR(23),
"CREATED" DATE "DD/MM/YYYY HH24:MI:SS",
"LAST_DDL_TIME" DATE "DD/MM/YYYY HH24:MI:SS",
"TIMESTAMP" CHAR(19),
"STATUS" CHAR(7),
"TEMPORARY" CHAR(1),
"GENERATED" CHAR(1),
"SECONDARY" CHAR(1),
"NAMESPACE" CHAR(100),
"EDITION_NAME" CHAR(128),
"SHARING" CHAR(18),
"EDITIONABLE" CHAR(1),
"ORACLE_MAINTAINED" CHAR(1),
"APPLICATION" CHAR(1),
"DEFAULT_COLLATION" CHAR(100),
"DUPLICATED" CHAR(1),
"SHARDED" CHAR(1),
"CREATED_APPID" CHAR(100),
"CREATED_VSNID" CHAR(100),
"MODIFIED_APPID" CHAR(100),
"MODIFIED_VSNID" CHAR(100)
)
)
LOCATION (
'ENCRYPT_TAB1.csv'
)
);
drop table ENCVAULT_TEST_PREV.GGTEST1_EXT;
CREATE TABLE ENCVAULT_TEST_PREV.test_EXT (
"EID" NUMBER,
"PINCODE" NUMBER,
"BRANCHCODE" VARCHAR2(4000),
"ENAME" VARCHAR2(4000)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATA_PUMP_DIR
ACCESS PARAMETERS (
FIELDS TERMINATED BY "," LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION ('30Sep2023_actual_GGTEST1.csv'));
create table ENCVAULT_TEST_PREV.ENCRYPT_TAB1
as
select * from ENCVAULT_TEST.ENCRYPT_TAB1 where 1=2;
insert into ENCVAULT_TEST_PREV.ENCRYPT_TAB1
select * from ENCVAULT_TEST_PREV.ENCRYPT_TAB1_EXT;
commit;
create table ENCVAULT_TEST_PREV.GGTEST1
as
select * from ENCVAULT_TEST.GGTEST1 where 1=2;
insert into ENCVAULT_TEST_PREV.GGTEST1
select * from ENCVAULT_TEST.GGTEST1;t
commit;
Actual output:
SQL> create user ENCVAULT_TEST_PREV identified by ENCVAULT_TEST_PREV default tablespace USERS;
User created.
SQL> alter user ENCVAULT_TEST_PREV quota unlimited on USERS;
User altered.
SQL> drop table ENCVAULT_TEST_PREV.ENCRYPT_TAB1_EXT; drop table ENCVAULT_TEST_PREV.ENCRYPT_TAB1_EXT * ERROR at line 1: ORA-00942: table or view does not exist
SQL> CREATE TABLE ENCVAULT_TEST_PREV.ENCRYPT_TAB1_EXT ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(18), "EDITIONABLE" VARCHAR2(1), 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 "ORACLE_MAINTAINED" VARCHAR2(1), "APPLICATION" VARCHAR2(1), "DEFAULT_COLLATION" VARCHAR2(100), "DUPLICATED" VARCHAR2(1), "SHARDED" VARCHAR2(1), "CREATED_APPID" NUMBER, "CREATED_VSNID" NUMBER, "MODIFIED_APPID" NUMBER, "MODIFIED_VSNID" NUMBER ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY DATA_PUMP_DIR ACCESS PARAMETERS ( 19 20 21 22 23 24 25 26 27 28 29 30 31 32 FIELDS TERMINATED BY "," LDRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ("OWNER" CHAR(128), "OBJECT_NAME" CHAR(128), "SUBOBJECT_NAME" CHAR(128), "OBJECT_ID" CHAR(100), "DATA_OBJECT_ID" CHAR(100), "OBJECT_TYPE" CHAR(23), "CREATED" DATE "DD/MM/YYYY HH24:MI:SS", "LAST_DDL_TIME" DATE "DD/MM/YYYY HH24:MI:SS", "TIMESTAMP" CHAR(19), "STATUS" CHAR(7), "TEMPORARY" CHAR(1), "GENERATED" CHAR(1), "SECONDARY" CHAR(1), "NAMESPACE" CHAR(100), "EDITION_NAME" CHAR(128), "SHARING" CHAR(18), "EDITIONABLE" CHAR(1), "ORACLE_MAINTAINED" CHAR(1), "APPLICATION" CHAR(1), "DEFAULT_COLLATION" CHAR(100), "DUPLICATED" CHAR(1), "SHARDED" CHAR(1), "CREATED_APPID" CHAR(100), "CREATED_VSNID" CHAR(100), "MODIFIED_APPID" CHAR(100), "MODIFIED_VSNID" CHAR(100) ) ) LOCATION ( 'ENCRYPT_TAB1.csv' ) ); 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
Table created.
SQL> create table ENCVAULT_TEST_PREV.ENCRYPT_TAB1 as select * from ENCVAULT_TEST.ENCRYPT_TAB1 where 1=2; 2 3
Table created.
SQL> insert into ENCVAULT_TEST_PREV.ENCRYPT_TAB1 select * from ENCVAULT_TEST_PREV.ENCRYPT_TAB1_EXT; 2
10 rows created.
SQL> select count(1) from ENCVAULT_TEST_PREV.ENCRYPT_TAB1;
COUNT(1) ---------- 10
SQL>
SQL> select * from ENCVAULT_TEST_PREV.ENCRYPT_TAB1;
OWNER -------------------------------------------------------------------------------------------------------------------------------- .. 11, 11,INDEX ,24-AUG-23,24-AUG-23 2023-08-24:19:32:14,VALID ,N,N,N, 4, ,NONE , ,Y,N ,N,N, , , ,
10 rows selected.
SQL>
|
Just to make sure if the records are properly loaded with proper column width and info. I compared the output of ENCVAULT_TEST_PREV.ENCRYPT_TAB1 & ENCVAULT_TEST.ENCRYPT_TAB1
and learnt that they are very clean. I will upload the notepad++ diff output below for reference.
A quick view on the restored and actual table comparison in notepad++ (luckily I didnt make any change to the table in the recent past, they dont show difference because of it).
YouTube video:
No comments:
Post a Comment