Adds the default values of Large columns through online redefinition.

The previous article discussed the new features of adding columns to a large table in 11g,

Before 11g is mentioned, you can add rows and columns (with default values) by means of online redefinition of tables ).

Online permission redefinition requirements:

Grant create any table to user;
Grant alter any table to user;
Grant drop any table to user;
Grant lock any table to user;
Grant select any table to user;
Grant create any trigger to user;
Grant create any index to user;

1. Obtain the DDL of the original table using SQL.

You can use the following settings to remove the storage clause or not to execute

Begin
Dbms_Metadata.Set_Transform_Param (Dbms_Metadata.Session_Transform,
'Sqlterminator ',
True );
Dbms_Metadata.Set_Transform_Param (Dbms_Metadata.Session_Transform,
'Storage ',
False );
End;

Obtain the DDL statement of the original table (T in this example)

Set pagesize 5000
Sets long 50000
Select Dbms_Metadata.Get_Ddl (Object_Type => 'table', Name => 'T') from dual
Union all
Select Dbms_Metadata.Get_Dependent_Ddl (Object_Type => 'straint', Base_Object_Name => 'T') from dual
Union all
Select Dbms_Metadata.Get_Dependent_Ddl (Object_Type => 'index', Base_Object_Name => 'T') from dual
Union all
Select Dbms_Metadata.Get_Dependent_Ddl ('object _ GRANT ', 't', 'Scott') from dual;

2. Replace the table name T in the obtained statement with T2, and then create the table.
 
3. Add fields to table t2 (with default values)

Alter table t2 add MrDai varchar2 (10) default 'mrdai ';

4. Check whether the T table can be redefined. If the execution is successful, it indicates yes. Otherwise, an error will be reported, indicating why not.

Exec Dbms_Redefinition.Can_Redef_Table (USER, 'T ');

5. Start redefinition

Note: if there are uncommitted items in the original table, the process will remain waiting, waiting for the event to be enq: TX-row lock contention

Exec dbms_redefinition.start_redef_table (uname => USER, orig_table => 'T', int_table => 't2 ', options_flag => DBMS_REDEFINITION.cons_use_pk );

6. complete redefinition

Exec dbms_redefinition.finish_redef_table (uname => USER, orig_table => 'T', int_table => 't2 ');

View t after redefinition

SQL> desc t;
Name Null? Type
-----------------------------------------------------------------------------
OWNER VARCHAR2 (30)
OBJECT_NAME VARCHAR2 (128)
SUBOBJECT_NAME VARCHAR2 (30)
OBJECT_ID NOT NULL NUMBER
.
.
.
MRDAI VARCHAR2 (10)

The column has been added.