Ask Anantha

In release 8.1, a new SQL function, TO_LOB, copies data from a LONG column in a table to a LOB column. The datatype of the LONG and LOB must correspond for a successful copy. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data. In the next example we show how to migrate a table with one LONG to a CLOB datatype. Create the LOB Tablespace CREATE TABLESPACE lob1 DATAFILE '/lh4/lob1.dbf' SIZE 2048064K REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M PERMANENT ONLINE; Disable temporarily all Foreign Keys set feed off; spool gen_dis_cons.sql; SELECT 'ALTER TABLE ' table_name ' DISABLE CONSTRAINT ' constraint_name ';' FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%' / spool off; set feed on; @gen_dis_cons.sql; Convert LONG to LOB in temporary Table Create a temporary table with converted BLOB field. CREATE TABLE lob_tmp TABLESPACE tab AS SELECT id, TO_LOB(bdata) bdata FROM document; Drop and Rena...

During a migration it may be important, that nobody can connect to the database. You may have two possibilities to achieve this On Table Level using LOCK TABLE On Database Level using RESTRICTED SESSION Exclusive Table Locks (X) An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows: $ sqlplus scott/tiger SQL> GRANT ALL ON emp TO PUBLIC; SQL> LOCK TABLE emo IN EXCLUSIVE MODE; $ sqlplus test/test SQL> SELECT * FROM scott.emp; (This works) SQL> DELETE FROM scott.emp WHERE empno = 7369; (Waiting ...) Permitted Operations Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table. Prohibited Operations An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of loc...