Data pump export - constraint_index example

View All Public Scripts Login to Run Script

Script Name Data pump export - constraint_index example
Visibility Unlisted - anyone with the share link can access
Description Creates primary key in three ways: * Inline with create table statement * Manually * Inline, then dropped and recreated manually
Area Oracle 19c
Contributor tbaur@halogensoftware.com
Created Monday June 03, 2019

Statement 1

CREATE TABLE CONS_IN_PLACE 
( 
    SUBJECT_ID NUMBER NOT NULL ENABLE,  
	ID NUMBER NOT NULL ENABLE,  
	MANAGER_ID NUMBER, 
    constraint PK_CONS_IN_PLACE primary key (SUBJECT_ID, ID) 
)

Table created.

Statement 2

CREATE INDEX IDX_WRONG_INDEX  
ON CONS_IN_PLACE (SUBJECT_ID, ID, MANAGER_ID)

Index created.

Statement 3

CREATE TABLE CONS_MANUAL 
( 
    SUBJECT_ID NUMBER NOT NULL ENABLE,  
	ID NUMBER NOT NULL ENABLE,  
	MANAGER_ID NUMBER 
)

Table created.

Statement 4

CREATE UNIQUE INDEX PK_CONS_MANUAL 
ON CONS_MANUAL (SUBJECT_ID, ID)

Index created.

Statement 5

ALTER TABLE CONS_MANUAL 
ADD CONSTRAINT PK_CONS_MANUAL PRIMARY KEY (Subject_id, id) 
USING INDEX PK_CONS_MANUAL

Table altered.

Statement 6

CREATE TABLE CONS_WITH_DROP 
( 
    SUBJECT_ID NUMBER NOT NULL ENABLE,  
	ID NUMBER NOT NULL ENABLE,  
	MANAGER_ID NUMBER, 
    constraint PK_CONS_WITH_DROP primary key (SUBJECT_ID, ID) 
)

Table created.

Statement 7

CREATE INDEX IDX_WRONG_INDEX_DROP  
ON CONS_WITH_DROP (SUBJECT_ID, ID, MANAGER_ID)

Index created.

Statement 8

ALTER TABLE CONS_WITH_DROP DROP CONSTRAINT PK_CONS_WITH_DROP

Table altered.

Statement 9

CREATE UNIQUE INDEX PK_CONS_WITH_DROP  
ON CONS_WITH_DROP (SUBJECT_ID, ID)

Index created.

Statement 10

ALTER TABLE CONS_WITH_DROP ADD CONSTRAINT PK_CONS_WITH_DROP PRIMARY KEY (SUBJECT_ID, ID) 
USING INDEX PK_CONS_WITH_DROP

Table altered.

Statement 11

select table_name, index_name, uniqueness, constraint_index FROM user_indexes


INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED VISIBILITY DOMIDX_MANAGEMENT SEGMENT_CREATED ORPHANED_ENTRIES INDEXING AUTO CONSTRAINT_INDEX
IDX_WRONG_INDEX NORMAL SQL_QKBRYSUWGGGCEZLVSELRBKMPO CONS_IN_PLACE TABLE NONUNIQUE DISABLED  - LIVESQL_USERS 2 255  -  -  -  -  -  -  -  -  - 10 YES 0 0 0 0 0 0 VALID 0 0 03-JUN-19 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO  -  -  -  -  - YES  -  -  - NO NO NO VISIBLE  - NO NO FULL NO NO
IDX_WRONG_INDEX_DROP NORMAL SQL_QKBRYSUWGGGCEZLVSELRBKMPO CONS_WITH_DROP TABLE NONUNIQUE DISABLED  - LIVESQL_USERS 2 255  -  -  -  -  -  -  -  -  - 10 YES 0 0 0 0 0 0 VALID 0 0 03-JUN-19 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO  -  -  -  -  - YES  -  -  - NO NO NO VISIBLE  - NO NO FULL NO NO
PK_CONS_IN_PLACE NORMAL SQL_QKBRYSUWGGGCEZLVSELRBKMPO CONS_IN_PLACE TABLE UNIQUE DISABLED  - LIVESQL_USERS 2 255  -  -  -  -  -  -  -  -  - 10 YES  -  -  -  -  -  - VALID  -  -  - 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO  -  -  -  -  - NO  -  -  - NO NO NO VISIBLE  - NO NO FULL NO YES
PK_CONS_MANUAL NORMAL SQL_QKBRYSUWGGGCEZLVSELRBKMPO CONS_MANUAL TABLE UNIQUE DISABLED  - LIVESQL_USERS 2 255  -  -  -  -  -  -  -  -  - 10 YES 0 0 0 0 0 0 VALID 0 0 03-JUN-19 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO  -  -  -  -  - YES  -  -  - NO NO NO VISIBLE  - NO NO FULL NO NO
PK_CONS_WITH_DROP NORMAL SQL_QKBRYSUWGGGCEZLVSELRBKMPO CONS_WITH_DROP TABLE UNIQUE DISABLED  - LIVESQL_USERS 2 255  -  -  -  -  -  -  -  -  - 10 YES 0 0 0 0 0 0 VALID 0 0 03-JUN-19 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO  -  -  -  -  - YES  -  -  - NO NO NO VISIBLE  - NO NO FULL NO NO

5 rows selected.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database

INDEX_NAME	INDEX_TYPE	TABLE_OWNER	TABLE_NAME	TABLE_TYPE	UNIQUENESS	COMPRESSION	PREFIX_LENGTH	TABLESPACE_NAME	INI_TRANS	MAX_TRANS	INITIAL_EXTENT	NEXT_EXTENT	MIN_EXTENTS	MAX_EXTENTS	PCT_INCREASE	PCT_THRESHOLD	INCLUDE_COLUMN	FREELISTS	FREELIST_GROUPS	PCT_FREE	LOGGING	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS	AVG_LEAF_BLOCKS_PER_KEY	AVG_DATA_BLOCKS_PER_KEY	CLUSTERING_FACTOR	STATUS	NUM_ROWS	SAMPLE_SIZE	LAST_ANALYZED	DEGREE	INSTANCES	PARTITIONED	TEMPORARY	GENERATED	SECONDARY	BUFFER_POOL	FLASH_CACHE	CELL_FLASH_CACHE	USER_STATS	DURATION	PCT_DIRECT_ACCESS	ITYP_OWNER	ITYP_NAME	PARAMETERS	GLOBAL_STATS	DOMIDX_STATUS	DOMIDX_OPSTATUS	FUNCIDX_STATUS	JOIN_INDEX	IOT_REDUNDANT_PKEY_ELIM	DROPPED	VISIBILITY	DOMIDX_MANAGEMENT	SEGMENT_CREATED	ORPHANED_ENTRIES	INDEXING	AUTO	CONSTRAINT_INDEX
IDX_WRONG_INDEX	NORMAL	SQL_QKBRYSUWGGGCEZLVSELRBKMPO	CONS_IN_PLACE	TABLE	NONUNIQUE	DISABLED	-	LIVESQL_USERS	2	255	-	-	-	-	-	-	-	-	-	10	YES	0	0	0	0	0	0	VALID	0	0	03-JUN-19	1	1	NO	N	N	N	DEFAULT	DEFAULT	DEFAULT	NO	-	-	-	-	-	YES	-	-	-	NO	NO	NO	VISIBLE	-	NO	NO	FULL	NO	NO
IDX_WRONG_INDEX_DROP	NORMAL	SQL_QKBRYSUWGGGCEZLVSELRBKMPO	CONS_WITH_DROP	TABLE	NONUNIQUE	DISABLED	-	LIVESQL_USERS	2	255	-	-	-	-	-	-	-	-	-	10	YES	0	0	0	0	0	0	VALID	0	0	03-JUN-19	1	1	NO	N	N	N	DEFAULT	DEFAULT	DEFAULT	NO	-	-	-	-	-	YES	-	-	-	NO	NO	NO	VISIBLE	-	NO	NO	FULL	NO	NO
PK_CONS_IN_PLACE	NORMAL	SQL_QKBRYSUWGGGCEZLVSELRBKMPO	CONS_IN_PLACE	TABLE	UNIQUE	DISABLED	-	LIVESQL_USERS	2	255	-	-	-	-	-	-	-	-	-	10	YES	-	-	-	-	-	-	VALID	-	-	-	1	1	NO	N	N	N	DEFAULT	DEFAULT	DEFAULT	NO	-	-	-	-	-	NO	-	-	-	NO	NO	NO	VISIBLE	-	NO	NO	FULL	NO	YES
PK_CONS_MANUAL	NORMAL	SQL_QKBRYSUWGGGCEZLVSELRBKMPO	CONS_MANUAL	TABLE	UNIQUE	DISABLED	-	LIVESQL_USERS	2	255	-	-	-	-	-	-	-	-	-	10	YES	0	0	0	0	0	0	VALID	0	0	03-JUN-19	1	1	NO	N	N	N	DEFAULT	DEFAULT	DEFAULT	NO	-	-	-	-	-	YES	-	-	-	NO	NO	NO	VISIBLE	-	NO	NO	FULL	NO	NO
PK_CONS_WITH_DROP	NORMAL	SQL_QKBRYSUWGGGCEZLVSELRBKMPO	CONS_WITH_DROP	TABLE	UNIQUE	DISABLED	-	LIVESQL_USERS	2	255	-	-	-	-	-	-	-	-	-	10	YES	0	0	0	0	0	0	VALID	0	0	03-JUN-19	1	1	NO	N	N	N	DEFAULT	DEFAULT	DEFAULT	NO	-	-	-	-	-	YES	-	-	-	NO	NO	NO	VISIBLE	-	NO	NO	FULL	NO	NO