Yes, I know what you’re thinking.
“Here we go again. Another database dude about to get stuck into us telling us about the importance of foreign keys for data integrity”
and yes, I could easily do that. But you’ve seen that before (and by the way, it’s of course still totally valid – you should have all those integrity constraints defined!)
But here’s another reason, albeit a little tongue in cheek
First I create a simple user with a couple of tables.
SQL> drop user demo1 cascade; User dropped. SQL> SQL> grant create session, create table to demo1 identified by demo1; Grant succeeded. SQL> SQL> alter user demo1 quota unlimited on users; User altered. SQL> SQL> create table demo1.t1 ( x int, y int, constraint pk primary key ( x ) ) ; Table created. SQL> SQL> create table demo1.t2 ( x int, y int) ; Table created. SQL> SQL> alter table demo1.t2 add constraint pk2 primary key ( x ); Table altered. SQL> SQL> insert into demo1.t1 values (1,1); 1 row created. SQL> insert into demo1.t1 values (2,2); 1 row created. SQL> commit; Commit complete.
And now, I take a datapump export of that schema
expdp compression=all dumpfile=DATA_PUMP_DIR:demo1.dmp logfile=DATA_PUMP_DIR:demo1.log schemas=demo1 Export: Release 18.104.22.168.0 - Production on Mon Nov 23 08:51:44 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 22.214.171.124.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": sys/******** AS SYSDBA compression=all dumpfile=DATA_PUMP_DIR:demo1.dmp logfile=DATA_PUMP_DIR:demo1. log schemas=demo1 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER . . exported "DEMO1"."T1" 4.804 KB 2 rows . . exported "DEMO1"."T2" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: C:\ORACLE\ADMIN\NP12\DPDUMP\DEMO1.DMP Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 23 08:52:10 2015 elapsed 0 00:00:23
Now I want to clone that schema to another, but in order to do it as efficiently as possible, I’m going to exclude indexes and constraints (they will be added later).
impdp directory=DATA_PUMP_DIR exclude=index exclude=constraint exclude=ref_constraint exclude=index_statistics exclude=table_statistics content=all dumpfile=demo1.dmp logfile=demo1.implog remap_schema=demo1:demo2 Import: Release 126.96.36.199.0 - Production on Mon Nov 23 09:05:36 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: /as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 188.8.131.52.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options ORA-39002: invalid operation ORA-39168: Object path REF_CONSTRAINT was not found.
Well…this is a disaster! Because I had no referential integrity constraints, my import failed to find any…and hence could not exclude them! This is actually documented as expected behaviour (Datapump Import Fails With ORA-39168, Doc ID 430702.1) and is done as a safety mechanism – if the datapump file was corrupt, we don’t want to be half way through importing a file and find bits and pieces misssing, so the checks are done up front and the import abandoned.
So the moral of the story is … make sure you’ve got referential integrity constraints
Imagine receiving a data dump from your customer and choosing to exclude GRANTs or CONSTRAINTs during the Import. You’d end up scratching your head !
Is there a specific reason you are SYS to export data ?
I don’t think i’s a good practice to use SYS to export data first because SYS is a “special” account that should be used mainly for startup, shutdown, upgrade, backup, restore (or better use SYSBACKUP in 12c) second because it some old Oracle version (like 10.2) SYS cannot export data in a consistent way.
Totally valid point. On my laptop its pretty much what I can type the quickest 🙂
What I learned today is that GRANT can actually create a new user 🙂