OCP Blog
Super DBA
How to convert LONG column to CLOB
by Vazha Mantua Wednesday, December 28, 2011 4:24 PM

Today We will show 2 methods how convert long column to clob.

METHOD I: Simple method using TO_LOB procedure:

CREATE TABLE NBA_LONG (ID NUMBER, TEXT_L LONG);
CREATE TABLE NBA_CLOB (ID NUMBER, TEXT_C CLOB);

INSERT INTO NBA_LONG VALUES (1, 'LONG SHOULD BE CONVERTED TO CLOB');
INSERT INTO NBA_CLOB SELECT ID, TO_LOB(TEXT_L) FROM NBA_LONG;
COMMIT;

As you see in table NBA_CLOB column text_c contain information of column text_l from table NBA_LONG.

----


METHOD II: With using DBMS_REDEFINITION package


CREATE TABLE SCOTT.NBA_LONG (ID NUMBER PRIMARY KEY, TEXT_L LONG);
CREATE TABLE SCOTT.NBA_CLOB (ID NUMBER, TEXT_C CLOB);
INSERT INTO SCOTT.NBA_LONG VALUES (1, 'LONG SHOULD BE CONVERTED TO CLOB');
COMMIT;

 

DECLARE
VM BINARY_INTEGER;
BEGIN
--VERIFY THAT THE TABLE NBA_LONG IS A CANDIDATE FOR ON LINE REDEFINITION
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','NBA_LONG');
--START THE REDEFINITION PROCESS
DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT','NBA_LONG','NBA_CLOB','ID ID,TO_LOB(TEXT_L) TEXT_C', DBMS_REDEFINITION.CONS_USE_PK);
--AUTOMATICALLY CREATE ANY TRIGGERS, INDEXES AND CONSTRAINTS ON NBA_CLOB
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT','NBA_LONG','NBA_CLOB', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, FALSE, VM);
--OPTIONALLY FOR SYNCHRONIZE
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'NBA_LONG','NBA_CLOB');
-- COMPLETE THE REDEFINITION
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'NBA_LONG','NBA_CLOB');
END;

Tags: , , , , , , ,

virtual column in oracle 11g database
by Vazha Mantua Monday, December 26, 2011 9:04 PM

Good Day All,

In this article we show you example of virtual indexes in oracle 11g database. When queried table virtual columns appear to be normal table columns, but their values are not stored in disk, They are generated at runtime. but of course, any indexes that create on virtual columns will require storage space.

Let see example

create table vm_table_with_vc
(id number,
person_name varchar2(30),
age number,
age_virtual NUMBER GENERATED ALWAYS AS (age*2) virtual
);

 

As you see column age_virtual is virtual column, which multiply value of “age” column in 2 times.

Now we can insert into table and see results:

insert into vm_table_with_vc(id,person_name,age) values (1,'Lionel Messi',24);
commit;

select * from vm_table_with_vc and result is:

ID PERSON_NAME AGE AGE_VIRTUAL
1 Lionel Messi 24 48

As you see in column “age_virtual” is automatically shown value=24*2=48

We can simple create index on virtual columns and this index will be like as function-based indexes.

create index vm_table_with_vc_I1 on vm_table_with_vc(age_virtual);

If we select dba_segments view ,we will check that index is stored in database.

 

Tags: , , ,

Filter by APML

Calendar

<<  November 2017  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar

TextBox