Skip to Main Content
  • Questions
  • Converting compressed securefile LOBS to basicfile

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 17, 2017 - 1:15 pm UTC

Last updated: October 19, 2017 - 2:13 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hello Tom,
We are migrating a database which is using Advance compression for LOBS.
We do not have licenses for the new environment to replicate the as-is.

What are the available options we have and how to convert compressed securefile LOBS to securefile with no compression or basicfile ?

Can you please guide us in here.

Thank you.

and Connor said...

You can use DBMS_REDEFINITION for this, eg

SQL> create table t ( pk int primary key, c clob )
  2  lob ( c  ) store as securefile ( compress  high ) ;

Table created.

SQL>
SQL> insert into t
  2  select rownum, rpad('x',1000,'x')
  3  from dual connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * from user_lobs where table_name = 'T'
  2  @pr
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : C
SEGMENT_NAME                  : SYS_LOB0000156682C00002$$
TABLESPACE_NAME               : USERS
INDEX_NAME                    : SYS_IL0000156682C00002$$
CHUNK                         : 8192
PCTVERSION                    :
RETENTION                     :
FREEPOOLS                     :
CACHE                         : NO
LOGGING                       : YES
ENCRYPT                       : NO
COMPRESSION                   : HIGH
DEDUPLICATION                 : NO
IN_ROW                        : YES
FORMAT                        : ENDIAN NEUTRAL
PARTITIONED                   : NO
SECUREFILE                    : YES
SEGMENT_CREATED               : YES
RETENTION_TYPE                : DEFAULT
RETENTION_VALUE               :

PL/SQL procedure successfully completed.


SQL>
SQL> create table t1 ( pk int, c clob )
  2  lob ( c  ) store as basicfile;

Table created.

SQL>
SQL>
SQL> exec   dbms_redefinition.start_redef_table(user, 't', 't1');

PL/SQL procedure successfully completed.

SQL>
SQL> set serverout on
SQL> declare
  2     error_count pls_integer := 0;
  3  begin
  4     dbms_redefinition.copy_table_dependents(user, 't', 't1', dbms_redefinition.cons_orig_params, true,true,true,false, error_count);
  5     dbms_output.put_line('errors := ' || to_char(error_count));
  6  end;
  7  /
errors := 0

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_redefinition.finish_redef_table(user, 't', 't1');

PL/SQL procedure successfully completed.

SQL> select * from user_lobs where table_name = 'T'
  2  @pr
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : C
SEGMENT_NAME                  : SYS_LOB0000156686C00002$$
TABLESPACE_NAME               : USERS
INDEX_NAME                    : SYS_IL0000156686C00002$$
CHUNK                         : 8192
PCTVERSION                    :
RETENTION                     : 900
FREEPOOLS                     :
CACHE                         : NO
LOGGING                       : YES
ENCRYPT                       : NONE
COMPRESSION                   : NONE
DEDUPLICATION                 : NONE
IN_ROW                        : YES
FORMAT                        : ENDIAN NEUTRAL
PARTITIONED                   : NO
SECUREFILE                    : NO
SEGMENT_CREATED               : YES
RETENTION_TYPE                : YES
RETENTION_VALUE               :

PL/SQL procedure successfully completed.


SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you so much

A reader, October 18, 2017 - 3:40 am UTC

Thank you for answering my query.
I have two followup questions.

1) Can we use securefile with NOCOMPRESSION instead of basicfile without Advance compression license ?

2) Can we convert these Lobs during import (impdp) into new DB ?
Connor McDonald
October 19, 2017 - 2:13 am UTC

1) Can we use securefile with NOCOMPRESSION instead of basicfile without Advance compression license ?

Yes

2) Can we convert these Lobs during import (impdp) into new DB ?

Yes.

SQL> create table t ( pk int primary key, c clob )
  2   lob ( c  ) store as securefile ( compress  high ) ;

Table created.

SQL>
SQL> insert into t
  2  select rownum, rpad('x',1000,'x')
  3  from dual connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

C:\c>expdp userid=mcdonac tables=t dumpfile=t directory=temp

Export: Release 12.2.0.1.0 - Production on Thu Oct 19 10:11:31 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "MCDONAC"."SYS_EXPORT_TABLE_01":  userid=mcdonac/******** tables=t dumpfile=t directory=temp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "MCDONAC"."T"                               1.923 MB    1000 rows
Master table "MCDONAC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\T.DMP
Job "MCDONAC"."SYS_EXPORT_TABLE_01" successfully completed at Thu Oct 19 10:11:49 2017 elapsed 0 00:00:16

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( pk int, c clob )
  2  lob ( c  ) store as basicfile;

Table created.

C:\>impdp userid=mcdonac dumpfile=t directory=temp table_exists_action=append

Import: Release 12.2.0.1.0 - Production on Thu Oct 19 10:12:04 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "MCDONAC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_IMPORT_FULL_01":  userid=mcdonac/******** dumpfile=t directory=temp table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "MCDONAC"."T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MCDONAC"."T"                               1.923 MB    1000 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "MCDONAC"."SYS_IMPORT_FULL_01" successfully completed at Thu Oct 19 10:12:09 2017 elapsed 0 00:00:03



More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database