Drop multiple columns faster in Oracle

From documentation: http://docs.oracle.com/database/122/ADMIN/managing-tables.htm#GUID-74A86E52-E2D2-405E-B888-94164E3973B9

“If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE...SET UNUSED statement.

This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. In most cases, constraints, indexes, and statistics defined on the column are also removed. The exception is that any internal indexes for LOB columns that are marked unused are not removed.

To mark the hiredate and mgr columns as unused, execute the following statement:

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);

You can later remove columns that are marked as unused by issuing an ALTER TABLE...DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.

The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.

SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER                       TABLE_NAME                  COUNT
--------------------------- --------------------------- -----
HR                          ADMIN_EMP                       2

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 of 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;

ORA-01184: logfile group 5 already exists

Command:  

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 11 (‘+DATA’) size 52428800;

Error:

ORA-01184: logfile group 5 already exists

Troubleshoot:

SYS> SELECT thread#, group#
FROM gv$log;

THREAD# GROUP#
———- ———-
1                 1
1                 2
1                 3
1                 4
1                 5
1                 6
1                 7
1                 8
1                 9
1                 10

As we see there are just 10 groups.

Check standby redo logs:

SYS> SELECT group#
FROM v$standby_log;

GROUP#
———-
11

So we have standby redo log with group id 11.

Solution: 

Add logfile group with number more than 11.

I prefer to save numbers for standby logs and add new log with number 22(10 redo log groups+(10+1) recommended number of standby logs+ 1)

SYS> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 22 (‘+DATA’) SIZE 52428800;

Database altered.

DDL Trigger to audit schema changes

  1. Create sequence for assigning numbers to the events:
    -- Create sequence
    create sequence SYS.DSQ_DDLEVENTS
    minvalue 1
    maxvalue 9999999999999999999999999999
    start with 1
    increment by 1
    cache 100;
  2. Create ddl events table
    -- Create table
    create table SYS.DDL_EVENTS
    (
     eventid NUMBER,
     inst_id   NUMBER,
     eventdate DATE,
     oraloginuser VARCHAR2(100),
     oradictobjname VARCHAR2(100),
     oradictobjowner VARCHAR2(100),
     oradictobjtype VARCHAR2(100),
     orasysevent VARCHAR2(100),
     machine VARCHAR2(100),
     program VARCHAR2(100),
     osuser VARCHAR2(100)
    )
    tablespace USERS;
  3. Create table for saving SQL statements(this is necessary because triggered sql statements may have several lines)
    -- Create table
    create table SYS.DDL_EVENTS_SQL
    ( eventid NUMBER,
     sqlline NUMBER,
     sqltext VARCHAR2(4000)
    )
    tablespace USERS;
  4. Create DDL trigger(it doesn’t degrade performance at all, by my experience)
    CREATE OR REPLACE TRIGGER sys.audit_ddl_trg
    AFTER DDL ON DATABASE
    DECLARE
    l_eventId NUMBER;
     l_sqlText ORA_NAME_LIST_T;
    BEGIN
    if ORA_SYSEVENT!='TRUNCATE' then
    
     SELECT dsq_ddlEvents.NEXTVAL INTO l_eventId FROM SYS.DUAL;
    INSERT INTO ddl_events (eventId,
     inst_id,
     EVENTDATE,
     ORALOGINUSER,
     ORADICTOBJNAME,
     ORADICTOBJOWNER,
     ORADICTOBJTYPE,
     ORASYSEVENT,
     machine,
     program,
     osuser
     )
     ( SELECT l_eventId,
     inst_id,
     SYSDATE,
     ORA_LOGIN_USER,
     ORA_DICT_OBJ_NAME,
     ORA_DICT_OBJ_OWNER,
     ORA_DICT_OBJ_TYPE,
     ORA_SYSEVENT,
     machine,
     program,
     osuser
     FROM SYS.DUAL
     right outer join
     SYS.GV$SESSION s on (1=1)
     WHERE s.sid=SYS_CONTEXT('USERENV','SID')
     and SYS_CONTEXT('USERENV','INSTANCE')=s.inst_id);
     FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
     INSERT INTO ddl_events_sql
     ( eventId, sqlLine, sqlText )
     VALUES
     ( l_eventId, l, l_sqlText(l) );
     END LOOP;
     end if;
    
     exception when others then
     null;
    END;

PIN_PACKS-TRIGGER-INVALID, DBMS_SHARED_POOL.KEEP must be declared 10g

To resolve the issue, do the following:

1. Connect as SYSDBA

$sqlplus / as sysdba

2. Create DBMS_SHARED_POOL package , grant necessary privileges and create dba_keepsizes view.

@?/rdbms/admin/dbmspool.sql

3. Recompile the trigger

alter trigger PIN_PACKS compile;

DBMS_SQLPA – PACKAGE BODY – INVALID 10g

To resolve the issue, do the following:

1. Connect as SYSDBA

$ sqlplus / as sysdba

2. Drop plan table

SQL> drop table plan_table;

Table dropped.

 

3. Recreate plan table

SQL> @?/rdbms/admin/utlxplan

Table created.

4. Re-create dbms_sqlpa package and public synonym

SQL> @?/rdbms/admin/prvtspao.plb

PL/SQL procedure successfully completed.

Package created.

No errors.

Synonym created.

No errors.

Package body created.

No errors.

5. Recompile all objects

SQL> @?/rdbms/admin/utlrp.sql