Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use Oracle set unused", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use Oracle set unused" this article.

The usage of Oracle set unused

Syntax: ALTER TABLE table SET UNUSED (multiple COLlist) or ALTER TABLE table SET UNUSED COLUMN col single; ALTER TABLE table DROP UNUSED COLUMNS;set unused does not actually delete fields. In addition to alter table drop field, you can also alter table set unused field;alter table drop unused;set unused system overhead is relatively low, faster, so you can first set unuased, and then in the system load is small, and then drop. If you have this requirement, delete some fields on a table, but because this form has a very large amount of data, if you directly execute ALTER TABLE ABC DROP (COLUMN) during rush hour You may receive ORA-01562-failed to extend rollback segment number string because you may consume the entire RBS in the process of deleting the field, causing such an error, so this is not a good idea, even if you desperately increase the RBS space to deal with the problem. My recommended practice: 1 > CREATE TABLE T1 (A NUMBER,B NUMBER); SQL > begin 2 for i in 1. 100000 3 loop 4 insert into T1 values (iPower100); 5 end loop;6 commit;7 end;SQL > select count (*) from T1 SET UNUSED COLUMN A CASCADE CONSTRAINTS; count (*) 1000002 > SQL > ALTER TABLE T1 SET UNUSED COLUMN A CASCADE CONSTRAINTS; do not immediately drop column, should first set unused so that column can not be used, avoid the system peak time to deal with delete field data, it should be noted that once you set unused column, this field can no longer be used. 3 > the point is, if you have 1 million entries in your field, we should avoid writing so many undo log at a time, so I'm going to commit every thousand entries deleted.

Just now someone asked me how to fix the field set to UNUSED. I considered that the following methods can be restored (make a backup before performing the following steps). Don't try an inexperienced DBA easily.

1. Create the experimental table TTTA

SQL > CREATE TABLE TTTA (An INTEGER,B INTEGER,C VARCHAR2 (10), D INTEGER)

The table has been created.

SQL > INSERT INTO TTTA VALUES (1, 2, 3, 4)

1 line has been created.

SQL > INSERT INTO TTTA VALUES (2, 3, 4, 5)

1 line has been created.

SQL > COMMIT

The submission is complete. ALTER TABLE TTTA SET UNUSED COLUMN C

2. Restore SQL > SELECT OBJ# FROM OBJ$ WHERE NAME='TTTA' as follows

OBJ#-32067

SELECT COL#,INTCOL#,NAME FROM COL$ WHERE OBJ#=32067;COL# INTCOL# NAME- 1 1 A2 2 B0 3 SYS_C00003_08031720:09:55$ Field 3 4 D by UNUSED

SQL > SELECT COLS FROM TAB$ WHERE OBJ#=32067

COLS-3-the number of fields has changed to 3

SQL > UPDATE COL$ SET COL#=INTCOL# WHERE OBJ#=32067

4 rows have been updated.

SQL > UPDATE TAB$ SET COLS=COLS+1 WHERE OBJ#=32067

1 row has been updated.

UPDATE COL$ SET NAME='C' WHERE OBJ#=32067 AND COL#=3

UPDATE COL$ SET PROPERTY=0 WHERE OBJ#=32067;SQL > COMMIT

3. Restart the database SQL > SELECT * FROM SCOTT.TTTA

A B C D-1 2 3 42 3 4 5

Recovery complete

The above is all the contents of this article "how to use Oracle set unused". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.