RE: Naming standards -- that lead to implicit/explicit constraint and index creation.
Date: Thu, 17 Nov 2011 14:22:44 +0100
Message-ID: <4814386347E41145AAE79139EAA398981939BEE6E3_at_ws03-exch07.iconos.be>
It seems attachments are silently removed when sending to the list, so I resend it with the output inline.
[oracle1_at_elin ~]$ sqlplus fdh
SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 17 13:53:11 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop table t;
Table dropped.
SQL> create table t (veld1 number(10,0), veld2 number(10,0));
Table created.
SQL> alter table t add primary key (veld1);
Table altered.
SQL> create index t_i_nonunique on t (veld1, veld2);
Index created.
SQL> insert into t values (1, 2);
1 row created.
SQL> insert into t values (2, 3);
1 row created.
SQL> commit;
Commit complete.
SQL> select index_name, index_type, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME INDEX_TYPE UNIQUENES ------------------------------ --------------------------- --------- T_I_NONUNIQUE NORMAL NONUNIQUE SYS_C0014660 NORMAL UNIQUE
SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'T';
CONSTRAINT_NAME C INDEX_NAME ------------------------------ - ------------------------------ SYS_C0014660 P SYS_C0014660 SQL> column index_owner format a20 SQL> column index_name format a20 SQL> column is_system_generated format a5 heading GEN SQL> column is_unique format a5 heading UNQSQL>
SQL> select
2 obj_i.owner index_owner,
3 obj_i.object_name index_name,
4 decode(bitand(ind.property,4096),4096, 'YES', 'NO') is_system_generated, 5 decode(bitand(ind.property,1),1, 'YES', 'NO') is_unique 6 from
7 dba_objects obj_t,
8 dba_objects obj_i,
9 sys.ind$ ind
10 where
11 ind.bo# = obj_t.object_id
12 and ind.obj# = obj_i.object_id
13 and obj_t.owner = 'FDH'
14 and obj_t.object_name = 'T'
15 order by
16 index_owner, index_name;
INDEX_OWNER INDEX_NAME GEN UNQ
-------------------- -------------------- ----- ----- FDH SYS_C0014660 YES YES FDH T_I_NONUNIQUE NO NO
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle1_at_elin ~]$ expdp system schemas=FDH dumpfile=implicit_test.dmp
Export: Release 11.2.0.2.0 - Production on Thu Nov 17 13:56:00 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=FDH dumpfile=implicit_test.dmp 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 . . exported "FDH"."T" 5.507 KB 2 rowsMaster table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/oracle/oracle1/admin/gunnar/dpdump/implicit_test.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:56:23
[oracle1_at_elin ~]$ impdp system remap_schema=FDH:FDH99 dumpfile=implicit_test.dmp
Import: Release 11.2.0.2.0 - Production on Thu Nov 17 13:57:55 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** remap_schema=FDH:FDH99 dumpfile=implicit_test.dmp 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/TABLE_DATA . . imported "FDH99"."T" 5.507 KB 2 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error: ORA-01403: no data found ORA-01403: no data found
Failing sql is:
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';BEGIN DELET Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:58:00
[oracle1_at_elin ~]$ sqlplus fdh99
SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 17 13:58:26 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 150
set pages 9999
SQL> SQL>
SQL> select index_name, index_type, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME INDEX_TYPE UNIQUENES ------------------------------ --------------------------- --------- T_I_NONUNIQUE NORMAL NONUNIQUE
SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'T';
CONSTRAINT_NAME C INDEX_NAME ------------------------------ - ------------------------------ SYS_C0014675 P T_I_NONUNIQUE SQL> column index_owner format a20 SQL> column index_name format a20 SQL> column is_system_generated format a5 heading GEN SQL> column is_unique format a5 heading UNQSQL>
SQL> select
2 obj_i.owner index_owner,
3 obj_i.object_name index_name,
4 decode(bitand(ind.property,4096),4096, 'YES', 'NO') is_system_generated, 5 decode(bitand(ind.property,1),1, 'YES', 'NO') is_unique 6 from
7 dba_objects obj_t,
8 dba_objects obj_i,
9 sys.ind$ ind
10 where
11 ind.bo# = obj_t.object_id
12 and ind.obj# = obj_i.object_id
13 and obj_t.owner = 'FDH99'
14 and obj_t.object_name = 'T'
15 order by
16 index_owner, index_name;
INDEX_OWNER INDEX_NAME GEN UNQ
-------------------- -------------------- ----- ----- FDH99 T_I_NONUNIQUE NO NO
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle1_at_elin ~]$
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 17 2011 - 07:22:44 CST