Advertisement
ExaGridDba

Table segment exists after error. Index valid after truncate

Jun 8th, 2015
278
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. START WITH an unusable UNIQUE INDEX.
  2. Attempt an INSERT, which fails.
  3. The TABLE SEGMENT appears.
  4.  
  5. TRUNCATE the TABLE.
  6. The INDEX SEGMENT appears, AND the INDEX IS usable.
  7.  
  8. Connected.
  9. SQL> SET linesize 200
  10. SQL> SET trimspool ON
  11. SQL> COLUMN index_name format a30
  12. SQL> COLUMN segment_name format a30
  13. SQL> show parameter deferred_segment_creation
  14.  
  15. NAME                                 TYPE        VALUE
  16. ------------------------------------ ----------- ------------------------------
  17. deferred_segment_creation            BOOLEAN     TRUE
  18. SQL> DROP TABLE t;
  19.  
  20. TABLE dropped.
  21.  
  22. SQL> CREATE TABLE t ( n NUMBER );
  23.  
  24. TABLE created.
  25.  
  26. SQL> CREATE UNIQUE INDEX tu ON t ( n )
  27.   2  unusable;
  28.  
  29. INDEX created.
  30.  
  31. SQL> TRUNCATE TABLE t;
  32.  
  33. TABLE truncated.
  34.  
  35. SQL> SELECT index_name, status FROM user_indexes
  36.   2  WHERE table_name = 'T';
  37.  
  38. INDEX_NAME                     STATUS
  39. ------------------------------ --------
  40. TU                             UNUSABLE
  41.  
  42. SQL> SELECT segment_name, segment_type FROM user_segments
  43.   2  WHERE ( segment_name = 'TU' AND segment_type = 'INDEX'
  44.   3          OR segment_name = 'T' AND segment_type = 'TABLE' );
  45.  
  46. no ROWS selected
  47.  
  48. SQL> INSERT INTO t ( n ) VALUES ( 0 );
  49. INSERT INTO t ( n ) VALUES ( 0 )
  50. *
  51. ERROR AT line 1:
  52. ORA-01502: INDEX 'U.TU' OR PARTITION OF such INDEX IS IN unusable state
  53.  
  54.  
  55. SQL> SELECT segment_name, segment_type FROM user_segments
  56.   2  WHERE ( segment_name = 'TU' AND segment_type = 'INDEX'
  57.   3          OR segment_name = 'T' AND segment_type = 'TABLE' );
  58.  
  59. SEGMENT_NAME                   SEGMENT_TYPE
  60. ------------------------------ ------------------
  61. T                              TABLE
  62.  
  63. SQL> TRUNCATE TABLE t;
  64.  
  65. TABLE truncated.
  66.  
  67. SQL> SELECT segment_name, segment_type FROM user_segments
  68.   2  WHERE ( segment_name = 'TU' AND segment_type = 'INDEX'
  69.   3          OR segment_name = 'T' AND segment_type = 'TABLE' );
  70.  
  71. SEGMENT_NAME                   SEGMENT_TYPE
  72. ------------------------------ ------------------
  73. T                              TABLE
  74. TU                             INDEX
  75.  
  76. SQL> SELECT index_name, status FROM user_indexes
  77.   2  WHERE table_name = 'T';
  78.  
  79. INDEX_NAME                     STATUS
  80. ------------------------------ --------
  81. TU                             VALID
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement