201710000111320.modify the table structure to delete the relevant questions. txt, 20171113.txt

201710000111320.modify the table structure to delete the relevant questions. txt, 20171113.txt

2017100001113133 modify the table structure and delete the related question txt

-- // Maintain the table structure to delete fields first
Alter table <table_name> set unused (<column_name> );

-- // Delete the column when idle.
Alter table <table_name> drop unused columns checkpoint <n>;

-- // Reference:
Https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11662

Removing Unused Columns

The alter table... drop unused columns statement is the only action allowed on unused columns. It physically removes
Unused columns from the table and reclaims disk space.

In the alter table statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint
To be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount
Undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

Alter table hr. admin_emp drop unused columns checkpoint 250;
 
-- // You can see from the document that adding the CHECKPOINT keyword can reduce the consumption of undo space to a certain extent. Of course, you can also use:
Alter table <table_name> drop column column_name;
-- // You can use the following code to delete multiple columns:
Alter table <table_name> DROP (<column_name1>, <column_name2> );

-- // Note: it is strange that there is no column keyword here. A simple example:
SCOTT @ book> create table t (a number, B number, c number, d number );
Table created.

SCOTT @ book> alter table t drop column B;
Table altered.

SCOTT @ book> alter table t drop (c );
Table altered.

SCOTT @ book> alter table t drop column (d, e );
Alter table t drop column (d, e)
*
ERROR at line 1:
The ORA-00904: invalid identifier

SCOTT @ book> alter table t drop column (d );
Alter table t drop column (d)
*
ERROR at line 1:
The ORA-00904: invalid identifier

SCOTT @ book> alter table t drop (d, e );
Table altered.

-- // It is generally not recommended to delete columns directly. This is very slow, but one exception is that if the data type is incorrect after the column is added or the definition length is too small, you should select drop column.
-- // It is not big. I thought there was no DML or few at this time. It should be very fast to change it back immediately. Of course, full table scan is inevitable. I would like to use an example to illustrate this.
-- // Operation details.

1. Environment:
SCOTT @ book> @ & r/ver1
PORT_STRING VERSION BANNER
----------------------------------------------------------------------------------------------------------------------------
X86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

-- // Drop table t purge;
Create table t (id number, v1 varchar2 (10), v2 varchar2 (20 ));

SCOTT @ book> insert into t select rownum, lpad ('A', 10, 'A'), lpad ('B', 20, 'B ') from xmltable ('1 to 10000000 ');
10000000 rows created.

SCOTT @ book> commit;
Commit complete.

2. Add a column and delete it to see the size of the generated redo:
SCOTT @ book> alter table t add (flag number (1, 0 ));
Table altered.

SCOTT @ book> column name format a54
SCOTT @ book> @ & r/viewredo
Name statistic # VALUE
--------------------------------------------------------------------------
User commits 6 0
Redo size 194 724
Redo wastage 199 0
Data blocks consistent reads-undo records applied 326 0

SCOTT @ book> alter table t drop (flag );
Table altered.

SCOTT @ book> @ & r/viewredo
Name statistic # VALUE
--------------------------------------------------------------------------
User commits 6 1
Redo size 194 4272
Redo wastage 199 0
Data blocks consistent reads-undo records applied 326 0

-- // Although it takes 2 or 3 seconds, the generated redo is only 4 k up and down.
SCOTT @ book> select SEGMENT_NAME, BYTES from dba_segments where owner = user and segment_name = 'T ';
SEGMENT_NAME BYTES
------------------------------
T 494927872

-- // The table size is 472 MB. It mainly takes time to scan data blocks.

3. If you have previously executed alter table <table_name> set unused (<column_name>);, add a column and delete it to see the size of the redo generated:

SCOTT @ book> alter table t set unused (v1 );
Table altered.

SCOTT @ book> alter table t add (flag number (1, 0 ));
Table altered.

SCOTT @ book> SELECT col #, segcol #, name, intcol #, type # FROM sys. col $ WHERE obj # IN (SELECT object_id FROM dba_objects WHERE object_name = 'T' AND owner = user );
COL # SEGCOL # name intcol # TYPE #
----------------------------------------------------------------------
1 1 ID 1 2
0 2 sys_c1_2_17111309: 43: 04 $2 1
2 3 V2 3 1
3 4 FLAG 4 2

-- // You can find that the field v1 is changed to sys_c1_2_17111309: 43: 04 $ (the last string is time)
SCOTT @ book> @ & r/viewredo

Name statistic # VALUE
-------------------------------------------------------------------------
User commits 6 0
Redo size 194 724
Redo wastage 199 0
Data blocks consiste nt reads-undo reco rds applied 326 0

SCOTT @ book> alter table t drop (flag );
Table altered.

SCOTT @ book> column name format a53
SCOTT @ book> @ & r/viewredo
Name statistic # VALUE
-------------------------------------------------------------------------
User commits 6 1
Redo size 194 2290947720
Redo wastage 199 0
Data blocks consistent reads-undo records applied 326 3

-- // Oh, I forgot to enable the timing settings. However, from redo size = 2290947720, we can see that the required time is not too short.
-- // 2290947720/1024/1024/1024 = 2.1336113288998603828, almost 2G.

SCOTT @ book> select sysdate from dual;
SYSDATE
-------------------
2017-11-13 09:55:06

SCOTT @ book> @ & r/log_history
Date Day Total H0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h2h23 Avg
------------------------------------------------------------------------------------------------------------------------------------------
00:00:00 Mon 59 0 0 0 0 0 0 0 0 59 0 0 0 0 0 0 0 0 0 2.46
00:00:00 Fri 2 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0. 08
00:00:00 Thu 7 0 0 0 0 0 0 0 0 0 5 1 0 0 0 0 0. 29
00:00:00 Wed 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0. 04

-- // The number of redo switches generated from to know the size of the redo records (I tested a redo 50 m ).

SCOTT @ book> SELECT col #, segcol #, name, intcol #, type # FROM sys. col $ WHERE obj # IN (SELECT object_id FROM dba_objects WHERE object_name = 'T' AND owner = user );
COL # SEGCOL # name intcol # TYPE #
-----------------------------------------
1 1 ID 1 2
2 2 V2 2 1

-- // We can also see from here that although alter table t drop (flag) is executed, it is for a flag field, in fact, one thing we did by the way is to drop the columns SET to set unused, which leads to a large number
-- // A large number of redo operations are generated. Be cautious before some maintenance operations to reduce unnecessary maintenance problems.