RE: Naming standards -- that lead to implicit/explicit constraint and index creation.
- From: D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
- To: "Joel.Patterson@xxxxxxxxxxx" <Joel.Patterson@xxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Nov 2011 14:22:44 +0100
It seems attachments are silently removed when sending to the list, so I resend
it with the output inline.
[oracle1@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 UNQ
SQL>
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@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 rows
Master 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@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/INDEX
Processing 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@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 UNQ
SQL>
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@elin ~]$
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
--
//www.freelists.org/webpage/oracle-l
Other related posts:
