Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- START WITH an unusable UNIQUE INDEX.
- Attempt an INSERT, which fails.
- The TABLE SEGMENT appears.
- TRUNCATE the TABLE.
- The INDEX SEGMENT appears, AND the INDEX IS usable.
- Connected.
- SQL> SET linesize 200
- SQL> SET trimspool ON
- SQL> COLUMN index_name format a30
- SQL> COLUMN segment_name format a30
- SQL> show parameter deferred_segment_creation
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- deferred_segment_creation BOOLEAN TRUE
- SQL> DROP TABLE t;
- TABLE dropped.
- SQL> CREATE TABLE t ( n NUMBER );
- TABLE created.
- SQL> CREATE UNIQUE INDEX tu ON t ( n )
- 2 unusable;
- INDEX created.
- SQL> TRUNCATE TABLE t;
- TABLE truncated.
- SQL> SELECT index_name, status FROM user_indexes
- 2 WHERE table_name = 'T';
- INDEX_NAME STATUS
- ------------------------------ --------
- TU UNUSABLE
- SQL> SELECT segment_name, segment_type FROM user_segments
- 2 WHERE ( segment_name = 'TU' AND segment_type = 'INDEX'
- 3 OR segment_name = 'T' AND segment_type = 'TABLE' );
- no ROWS selected
- SQL> INSERT INTO t ( n ) VALUES ( 0 );
- INSERT INTO t ( n ) VALUES ( 0 )
- *
- ERROR AT line 1:
- ORA-01502: INDEX 'U.TU' OR PARTITION OF such INDEX IS IN unusable state
- SQL> SELECT segment_name, segment_type FROM user_segments
- 2 WHERE ( segment_name = 'TU' AND segment_type = 'INDEX'
- 3 OR segment_name = 'T' AND segment_type = 'TABLE' );
- SEGMENT_NAME SEGMENT_TYPE
- ------------------------------ ------------------
- T TABLE
- SQL> TRUNCATE TABLE t;
- TABLE truncated.
- SQL> SELECT segment_name, segment_type FROM user_segments
- 2 WHERE ( segment_name = 'TU' AND segment_type = 'INDEX'
- 3 OR segment_name = 'T' AND segment_type = 'TABLE' );
- SEGMENT_NAME SEGMENT_TYPE
- ------------------------------ ------------------
- T TABLE
- TU INDEX
- SQL> SELECT index_name, status FROM user_indexes
- 2 WHERE table_name = 'T';
- INDEX_NAME STATUS
- ------------------------------ --------
- TU VALID
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement