RE: Naming standards -- that lead to implicit/explicit constraint and index creation.
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <Freek.DHooge@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Nov 2011 11:10:43 -0500
Thanks Freek,
Your query heading changed from implicit to system generated which I
appreciate. All comments and additions are welcomed.
I'm going to refine my script that generated the output in email sent
11/17@7:25am. My head needs to clear a bit. If anyone wants it when I'm done,
let me know.
I changed your example below to name the index (via the constraint create).
ind$.property still returns YES, (because the index is implicitly created). The
index does import in this case -- and changes to NO upon import (because name
exists in export file -- note user assumption).
So it appears that the ideal scenario is for the index to be created first
(named), 'and' then explicitly attach the constraint to the index. (I don't
think you can create an index on a table first with a system generated name).
This is the only way for the ind$.property to return NO before export/import
(that I have discovered so far).
In your below example I made one change: (except also on 10.2.0.4)
alter table t add constraint named_pk primary key (veld1);
Before Export
INDEX_NAME GEN UNQ
-------------------- ----- -----
NAMED_PK YES YES -- index was created with create constraint
stmt. (if not named, this index disappears).
T_I_NONUNIQUE NO NO
After Import
INDEX_NAME GEN UNQ
-------------------- ----- -----
NAMED_PK NO YES -- index already had name... so no longer
system generated.
T_I_NONUNIQUE NO NO
Here is a couple results for now just to try and get them down quickly.
a. If the index is named via the create constraint statement (implicitly
creating an index with the same name), it will not be dropped. It is not
necessary to 'explicitly' attach the constraint to the index to prevent this --
however dropping constraints will still drop the index.
b. The corollary to 'a.' is, if the constraint is not named, the constraint
name is system generated and the index name takes on the same name as the
constraint. Then the index can be lost upon import given there is an index
laying around it can use.
b1. If no other index is laying around that can be used, the index will
not be technically 'lost', but recreated with a 'new' system generated name.
c. If you 'do' explicitly attach the constraint to the index, then you can
drop the constraint and the index will not drop. Statistics will not be lost,
and no indexes will be lost.
(Caveat here, I did not see a statistics error in import log, but no
statistics where imported -- 10g vs. 11g, or parameter difference).
d. It appears the statistics are lost because either the new name is
different, or there is one less index, -- thus invalidating them.
d1. For example: when only the primary key index existed with and the name
was system generated. That name changed to a new system generated number
(name) -- I'm assuming the statistics where attached to the old name, and thus
caused the error, and/or did not import.
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: D'Hooge Freek [mailto:Freek.DHooge@xxxxxxxxx]
Sent: Thursday, November 17, 2011 8:23 AM
To: Patterson, Joel; oracle-l@xxxxxxxxxxxxx
Subject: RE: Naming standards -- that lead to implicit/explicit constraint and
index creation.
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_ed 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:
