Oracle useful stuff II

============================================
Index
============================================
Copy large amount amount of data from TABLE_A to TABLE_B
Select from CLOB

===================================================
Copy large amount amount of data from TABLE_A to TABLE_B
===================================================
For example:

CREATE TABLE MY_TABLE ...

ALTER TABLE MY_TABLE NOLOGGING;

INSERT /*+ APPEND */ INTO MY_TABLE SELECT * FROM OTHER_TABLE;

COMMIT;

ALTER TABLE MY_TABLE LOGGING;

BEGIN  
  DBMS_STATS.gather_table_stats('MY_USER', 'MY_TABLE', method_opt => 'FOR ALL COLUMNS SIZE 254');

END;

NOLOGGING => No archiving is taking place.
/*+ APPEND */ => Data is written via direct-path into db blocks.


Table Mode    Insert Mode     ArchiveLog mode      Result
-----------   -------------   ------------------   --------------
LOGGING       APPEND          ARCHIVE LOG          redo generated
NOLOGGING     APPEND          ARCHIVE LOG          no redo
LOGGING       no append       ARCHIVE LOG          redo generated
NOLOGGING     no append       ARCHIVE LOG          redo generated
LOGGING       APPEND          NOARCHIVE LOG mode   no redo
NOLOGGING     APPEND          NOARCHIVE LOG mode   no redo
LOGGING       no append       NOARCHIVE LOG mode   redo generated
NOLOGGING     no append       NOARCHIVE LOG mode   redo generated


Logging versus Nologging Reference 



Direct-Path Insert
The APPEND hint tells the optimizer to perform a direct-path insert, which improves the performance of INSERT .. SELECT for a number of reasons:

- Data is appended to the end of the table.
- Data is written directly to the data files, without using the buffer cache.
- Referential integrity constraints are not considered.
- No trigger processing is performed.

============================================
Select text from CLOB
============================================
SELECT TO_CHAR(clob_column) FROM ...

SELECT TO_CHAR(DBMS_LOB.substr(HIST_SQLTEXT.sql_text,4000,1)) as sql_text
FROM...

Where 4000 is length and 1 is offset.

============================================
Handle ORA-00054
============================================

When issued a DDL command, such as ALTER TABLE SHRINK, an error ORA-00054 might be thrown:
ORA-00054 resource busy and acquire with NOWAIT specified

ALTER TABLE MY_TABLE ENABLE ROW MOVEMENT;
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

The solution, new from Oracle 11g is to increase the DDL_LOCK_TIMEOUT parameter.
It tels Oracle that the DDL would wait for the object to become available.

SQL> ALTER SESSION SET DDL_LOCK_TIMEOUT = 600;
Session altered.
SQL> ALTER TABLE MY_TABLE ENABLE ROW MOVEMENT;

Table altered.

============================================
Update many columns at once

============================================
UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...

[WHERE conditions];