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