spexp
SPEXP tool is minimalistic export script which runs from SQL*Plus.
You can download it from here.
This script doesn’t create any objects in database and can be used even in nomount mode to export v$ views.
Examples:
1. export/import view dba_users
SQL> -- export SQL> @spexp -f dba_users *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start desc table Start to parse desc table Start to apply format for columns Start to create table creating cursor Start to create exporting cursor Start spooling file spexp_dba_users.sql Object/query has been exported into file spexp_dba_users.sql SQL> SQL> -- import SQL> @spexp_dba_users.sql *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start DDLs Start data import into table exp_dba_users Import is done SQL> SQL> -- check SQL> select * from dba_users minus select * from exp_dba_users; no rows selected
2. export/import query results with various datatypes
SQL> --export
SQL> @spexp -q "select n'abc', to_clob('abc'), interval '7' second, 0f/0f, rowid rid, 1e125, systimestamp tmsp, hextoraw('FFFFFF') from dual"
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start to describe columns
Start to apply format for columns
Start to create table creating cursor
Start to create exporting cursor
Start spooling file spexp_query_20130420092928.sql
Object/query has been exported into file spexp_query_20130420092928.sql
SQL>
SQL> -- check export file content
SQL> !cat spexp_query_20130420092928.sql
set termout on
prompt
prompt *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
prompt
prompt Start DDLs
set termout off define off feedback off
whenever sqlerror exit rollback
create table query_20130420092928 (
"N'ABC'" NCHAR(3),
"TO_CLOB('ABC')" CLOB,
"INTERVAL'7'SECOND" INTERVAL DAY(9) TO SECOND(9),
"0F/0F" BINARY_FLOAT,
"RID" ROWID,
"1E125" NUMBER,
"TMSP" TIMESTAMP(9) WITH TIME ZONE,
"HEXTORAW('FFFFFF')" RAW(3));
set termout on
prompt Start data import into table query_20130420092928
set termout off
alter session set cursor_sharing = force;
var n varchar2(1)
exec :n := chr(10)
insert into query_20130420092928 values (
utl_raw.cast_to_nvarchar2(hextoraw('004100420043'))
,to_clob(null)||('ABC')||
--
--
--
--
--
--
--
''
,to_dsinterval('+00 00:00:07.000000')
,-utl_raw.cast_to_binary_float(hextoraw('FFC00000'))
,'AAAAB0AABAAAAOhAAA'
,1E+125
,to_timestamp_tz('20130420092928618674 +04:00','YYYYMMDDHH24MISSFF TZR')
,'FFFFFF'
);
commit;
alter session set cursor_sharing = exact;
whenever sqlerror continue
set termout on feedback on define on
prompt Import is done
SQL>
SQL> -- import
SQL> @spexp_query_20130420092928.sql
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start DDLs
Start data import into table query_20130420092928
Import is done
SQL>
SQL> -- check imported data
SQL> select * from query_20130420092928;
N'A TO_CLOB('ABC') INTERVAL'7'SECOND 0F/0F RID 1E125 TMSP HEXTOR
--- -------------- ------------------------------ ----- ------------------ ---------- --------------------------------------- ------
ABC ABC +000000000 00:00:07.000000000 Nan AAAAB0AABAAAAOhAAA 1.000E+125 20-APR-13 09.29.28.618674000 AM +04:00 FFFFFF
1 row selected.
3. export part of table, set name for to_table and export file
SQL> -- export SQL> @spexp -f dba_objects -w object_type='CLUSTER' -t all_clusters_test -e my_export_file *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start desc table Start to parse desc table Start to apply format for columns Start to create table creating cursor Start to create exporting cursor Start spooling file my_export_file.sql Object/query has been exported into file my_export_file.sql SQL> SQL> -- import SQL> @my_export_file.sql *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start DDLs Start data import into table all_clusters_test Import is done SQL> SQL> -- check data SQL> select owner, object_type, count(*) from all_clusters_test group by owner, object_type order by 3 desc; OWNER OBJECT_TYPE COUNT(*) ------------------------------ ------------------- ---------- SYS CLUSTER 10 1 row selected.
4. export with hint (can be useful for export of v$ views with bad plans)
SQL> -- export
SQL> @spexp -f dual -h qb_name("HELLO")
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start desc table
Start to parse desc table
Start to apply format for columns
Start to create table creating cursor
Start to create exporting cursor
Start spooling file spexp_dual.sql
Object/query has been exported into file spexp_dual.sql
SQL>
SQL> -- check export query (formatted)
SQL> select sql_text from v$sql where sql_id = '04gjc7uqr1dgp';
SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select /*+qb_name("HELLO")*/ 'insert into exp_dual values (' a,
''''||replace(replace(to_char(substr(DUMMY,1,1200)), '''', ''''''),chr(10),'''||:n||''')||'''' a,
');'||decode(mod(rownum,1000),0,chr(10)||'commit;')||decode(mod(rownum,1000),0,chr(10)||'set termout on'||chr(10)||'prompt '||rownum||' rows imported'||chr(10)||'set termout off') a
from dual a
1 row selected.
5. export gv$session using short alias, export only certain columns
SQL> -- export
SQL> @spexp -s ses -plus_cols SID,SERIAL#,STATE,EVENT
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start desc table
Start to parse desc table
Start to apply format for columns
Start to create table creating cursor
Start to create exporting cursor
Start spooling file spexp_gv_session.sql
Object/query has been exported into file spexp_gv_session.sql
SQL>
SQL> -- import
SQL> @spexp_gv_session.sql
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start DDLs
Start data import into table exp_gv$session
Import is done
SQL>
SQL> -- check data
SQL> select * from exp_gv$session;
SID SERIAL# EVENT STATE
---------- ---------- ---------------------------------------------------------------- -------------------
2 1 pmon timer WAITING
3 1 rdbms ipc message WAITING
4 1 VKTM Logical Idle Wait WAITING
5 1 rdbms ipc message WAITING
6 1 DIAG idle wait WAITING
7 1 rdbms ipc message WAITING
8 1 DIAG idle wait WAITING
9 1 rdbms ipc message WAITING
10 1 rdbms ipc message WAITING
11 1 rdbms ipc message WAITING
12 1 rdbms ipc message WAITING
13 1 smon timer WAITING
14 1 rdbms ipc message WAITING
15 1 rdbms ipc message WAITING
16 1 rdbms ipc message WAITING
17 15 rdbms ipc message WAITING
20 3 Streams AQ: qmn coordinator idle wait WAITING
21 351 SQL*Net message from client WAITING
23 7 Streams AQ: waiting for time management or cleanup tasks WAITING
24 11 Streams AQ: qmn slave idle wait WAITING
26 1387 Space Manager: slave idle wait WAITING
28 15 SQL*Net message from client WAITING
29 13 SQL*Net message from client WAITING
30 495 VKRM Idle WAITING
34 327 SQL*Net message from client WAITING
36 739 SQL*Net message to client WAITED SHORT TIME
37 443 rdbms ipc message WAITING
40 703 SQL*Net message from client WAITING
41 107 SQL*Net message from client WAITING
29 rows selected.
6. export a lot of rows, checking rows counter
SQL> -- export SQL> @spexp -q "select level id from dual connect by level <= 1e4" *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start to describe columns Start to apply format for columns Start to create table creating cursor Start to create exporting cursor Start spooling file spexp_query_20130420100524.sql Object/query has been exported into file spexp_query_20130420100524.sql SQL> SQL> -- import with checking counting SQL> @spexp_query_20130420100524.sql *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start DDLs Start data import into table query_20130420100524 1000 rows imported 2000 rows imported 3000 rows imported 4000 rows imported 5000 rows imported 6000 rows imported 7000 rows imported 8000 rows imported 9000 rows imported 10000 rows imported Import is done SQL> SQL> -- export with disabled counting SQL> @spexp -q "select level id from dual connect by level <= 1e4" -np *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start to describe columns Start to apply format for columns Start to create table creating cursor Start to create exporting cursor Start spooling file spexp_query_20130420100558.sql Object/query has been exported into file spexp_query_20130420100558.sql SQL> SQL> -- import SQL> @spexp_query_20130420100558.sql *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start DDLs Start data import into table query_20130420100558 Import is done
7. estimate/export gv$active_session_history by short alias with debug output
SQL> -- estimate SQL> @spexp -s ash -est *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start desc table Start to parse desc table Start to count exported rows Rows to export : 1731 Start to apply format for columns Start to create table creating cursor Start to create exporting cursor Estimated time to export : +000000000 00:00:01.024147881 Estimated time to export : 1127196 SQL> SQL> -- export with debug info at level 1 SQL> @spexp -s ash -d1 *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** DEBUG ENABLED AT LEVEL 1 Variables after parsing ---------------------------------------------- -- documented -- from_table [gv$active_session_history] -- from_query [] -- to_table [exp_gv$active_session_history] -- where_clause [] -- hint_expr [] -- short [ash] -- drop_before_create [0] -- no_create [0] -- pmcols [0] -- cols [] -- debug_level [1] -- export_file_name [spexp_gv_active_session_history.sql] -- os [UNIX] -- estimate [0] -- convert_characterset [0] -- undocumented -- method_char [A] -- method_clob [A] -- method_nchar [R] -- method_nclob [R] -- sub [] -- max_clob_size [10000] -- piece_size [500] -- raw_method_piece_size [1200] -- raw_method_min_size [200] -- compress_min_size [300] -- no_optimize_clob_tail [0] -- clob_as_char [0] -- max_blob_size [2000] -- raw_piece_size [1240] -- raw_compress_min_size [1000] -- no_raw_compress [0] -- no_temp [0] -- no_auto_temp [0] -- no_clob_stmt [0] -- no_auto_clob_stmt [0] -- no_compress [0] -- no_auto_compress [0] -- no_dbms_sql [0] -- no_auto_dbms_sql [0] -- no_dbms_lob [0] -- no_auto_dbms_lob [0] -- export_plsql_block_size [0] -- commit_block_size [1000] -- export_prompt_size [1000] -- estimate_rows_to_check [1000] -- from_nls_characterset [] -- from_nls_nchar_characterset [] ---------------------------------------------- Start desc table descraw size is [2481] Start to parse desc table Start to apply format for columns Start to create table creating cursor Start to create exporting cursor Start spooling file spexp_gv_active_session_history.sql Elapsed time : +000000000 00:00:01.026240000 File size : 1123058 Object/query has been exported into file spexp_gv_active_session_history.sql
8. estimate/export many rows query with debug output
SQL> -- estimate SQL> @spexp -q "select level id from dual connect by level <= 1e4" -est *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start to describe columns Start to count exported rows Rows to export : 10000 Start to apply format for columns Start to create table creating cursor Start to create exporting cursor Estimated time to export : +000000000 00:00:00.232560000 Estimated time to export : 504620 SQL> SQL> -- export with debug output SQL> @spexp -q "select level id from dual connect by level <= 1e4" -d1 *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** DEBUG ENABLED AT LEVEL 1 Variables after parsing ---------------------------------------------- -- documented -- from_table [] -- from_query [select level id from dual connect by level <= 1e4] -- to_table [query_20130420100759] -- where_clause [] -- hint_expr [] -- short [] -- drop_before_create [0] -- no_create [0] -- pmcols [0] -- cols [] -- debug_level [1] -- export_file_name [spexp_query_20130420100759.sql] -- os [UNIX] -- estimate [0] -- convert_characterset [0] -- undocumented -- method_char [A] -- method_clob [A] -- method_nchar [R] -- method_nclob [R] -- sub [] -- max_clob_size [10000] -- piece_size [500] -- raw_method_piece_size [1200] -- raw_method_min_size [200] -- compress_min_size [300] -- no_optimize_clob_tail [0] -- clob_as_char [0] -- max_blob_size [2000] -- raw_piece_size [1240] -- raw_compress_min_size [1000] -- no_raw_compress [0] -- no_temp [0] -- no_auto_temp [0] -- no_clob_stmt [0] -- no_auto_clob_stmt [0] -- no_compress [0] -- no_auto_compress [0] -- no_dbms_sql [0] -- no_auto_dbms_sql [0] -- no_dbms_lob [0] -- no_auto_dbms_lob [0] -- export_plsql_block_size [0] -- commit_block_size [1000] -- export_prompt_size [1000] -- estimate_rows_to_check [1000] -- from_nls_characterset [] -- from_nls_nchar_characterset [] ---------------------------------------------- Start to describe columns Start to apply format for columns Start to create table creating cursor Start to create exporting cursor Start spooling file spexp_query_20130420100759.sql Elapsed time : +000000000 00:00:00.207295000 File size : 510101 Object/query has been exported into file spexp_query_20130420100759.sql
9. export/import with/without convertation from/to databases with different charactersets
SQL> -- export without convertation
SQL> @spexp -q "select n'abc', 'abc', to_clob('abc'), to_nclob('abc') from dual"
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start to describe columns
Start to apply format for columns
Start to create table creating cursor
Start to create exporting cursor
Start spooling file spexp_query_20130420103015.sql
Object/query has been exported into file spexp_query_20130420103015.sql
SQL>
SQL> -- export with convertation
SQL> @spexp -q "select n'abc', 'abc', to_clob('abc'), to_nclob('abc') from dual" -cc
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start to describe columns
Start to apply format for columns
Start to create table creating cursor
Start to create exporting cursor
Start spooling file spexp_query_20130420103020.sql
Object/query has been exported into file spexp_query_20130420103020.sql
SQL>
SQL> -- check export file with convertation
SQL> !cat spexp_query_20130420103020.sql
set termout on
prompt
prompt *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
prompt
prompt Start DDLs
set termout off define off feedback off
whenever sqlerror exit rollback
create table query_20130420103020 (
"N'ABC'" NCHAR(3),
"'ABC'" CHAR(3 CHAR),
"TO_CLOB('ABC')" CLOB,
"TO_NCLOB('ABC')" NCLOB);
set termout on
prompt Start data import into table query_20130420103020
set termout off
alter session set cursor_sharing = force;
var n varchar2(1)
exec :n := chr(10)
var c varchar2(30)
exec :c := 'AL32UTF8'
var nc varchar2(30)
exec :nc := 'AL16UTF16'
insert into query_20130420103020 values (
utl_i18n.raw_to_nchar(hextoraw('004100420043'),:nc)
,utl_i18n.raw_to_char(hextoraw('414243'),:c)
,to_clob(null)||(utl_i18n.raw_to_char((hextoraw('414243')),:c))||
--
--
--
--
--
--
--
utl_i18n.raw_to_char((hextoraw('')),:c)
,to_clob(null)||(utl_i18n.raw_to_nchar((hextoraw('004100420043')),:nc))||
--
--
utl_i18n.raw_to_nchar((hextoraw('')),:nc)
);
...
SQL> -- Import data into database with different charactersets from export with convertation
SQL> @spexp_query_20130420103020.sql
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start DDLs
Start data import into table query_20130420103020
Import is done
SQL>
SQL> -- Import data into database with different charactersets from export without convertation failed
SQL> @spexp_query_20130420103015.sql
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start DDLs
Start data import into table query_20130420103015
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options
[oracle@SB1 ~]$ # Comment "set" in the export file to get clue about the issue and re-run import
[oracle@SB1 ~]$ vi spexp_query_20130420103015.sql
[oracle@SB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 20 11:07:52 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options
SQL> @spexp_query_20130420103015.sql
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start DDLs
Start data import into table query_20130420103015
Session altered.
PL/SQL procedure successfully completed.
utl_raw.cast_to_nvarchar2(hextoraw('004100420043'))
*
ERROR at line 2:
ORA-12899: value too large for column "SYS"."QUERY_20130420103015"."N'ABC'"
(actual: 6, maximum: 3)
Commit complete.
Session altered.
Import is done
SQL>
SQL> -- change column size to get data loaded with wrong characterset and check what was loaded
SQL> !vi spexp_query_20130420103015.sql
SQL> drop table query_20130420103015;
Table dropped.
SQL> @spexp_query_20130420103015.sql
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start DDLs
Table created.
Start data import into table query_20130420103015
Session altered.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
Session altered.
Import is done
SQL> select * from query_20130420103015;
N'ABC' 'ABC' TO_CLOB('ABC') TO_NCLOB('ABC')
-------- --------- --------------- ----------------
A B C ABC ABC A B C
SQL> select * from query_20130420103020;
N'A 'AB TO_CLOB('ABC') TO_NCLOB('ABC')
--- --- --------------- -----------------
ABC ABC ABC ABC
10. export v$ views from database in nomount state
SQL> startup nomount ORACLE instance started. Total System Global Area 730714112 bytes Fixed Size 2231952 bytes Variable Size 423625072 bytes Database Buffers 301989888 bytes Redo Buffers 2867200 bytes SQL> -- export v$sql with drop before create option (add "drop table exp_v$sql purge") SQL> @spexp -f v$sql -d *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start desc table Start to parse desc table Start to apply format for columns Start to create table creating cursor Start to create exporting cursor Start spooling file spexp_v_sql.sql Object/query has been exported into file spexp_v_sql.sql SQL> -- open database and import data SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL> @spexp_v_sql.sql *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start DDLs Start data import into table exp_v$sql Import is done SQL> -- check data exported SQL> select sql_id from exp_v$sql; SQL_ID ------------- 0qudkj5c5c2y7 3rfhkbsgs085h 5wypgkppv498d 8yqf8655q0bt4 a02bs1yu60kvw gbhddtvxmcmmq 9babjv8yq8ru3 6vfrwcuaqhwwx 8y34h1712140p apx4kx7my95cr dy69uhhcq178c 5t10uu7v11s5t 0mtwvr49kxt2m 0afcutuabdy7q 786nas6zvdzq3 5a30ayk4c20tz 28u8wnwvx6192 7uzkcdy1b27fr dx79fa497qvdg a5bbgadrmazcc f2sh62sqdv14x bdfn08b2c72bv 0ucumxdzdb3n7 a5ks9fhw2v9s1 bpsrkyvyxzj8s 1yj0q0krbvsbq 26 rows selected.
11. export/import big clob with adjusting max_clob_size parameter
01:40:47 SQL> -- source table
01:40:47 SQL> select length(c) from tclob;
LENGTH(C)
----------
180109
359929
1871533
3743066
01:40:52 SQL> -- regular export
01:40:52 SQL> @spexp -f tclob -t t -d -d1
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
...
Elapsed time : +000000000 00:00:00.023662000
File size : 5571
Object/query has been exported into file spexp_tclob.sql
01:41:22 SQL>
01:41:34 SQL> @spexp_tclob.sql
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start DDLs
Start data import into table t
Import is done
01:41:35 SQL> select length(c) from t;
LENGTH(C)
----------
10800
10800
10800
10800
01:41:43 SQL> -- manual setting for max_clob_size
01:41:43 SQL> @spexp -f tclob -t t -d -max_clob_size 4e6 -sp_arraysize 1 -d1
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
...
Start spooling file spexp_tclob.sql
Elapsed time : +000000000 00:00:09.467524000
File size : 722552
Object/query has been exported into file spexp_tclob.sql
01:42:30 SQL>
01:42:36 SQL> @spexp_tclob.sql
*** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) ***
Start DDLs
Start data import into table t
Import is done
01:44:11 SQL> select length(c) from t;
LENGTH(C)
----------
180109
359929
1871533
3743066
12. using spool->fifo->gzip to reduce disk space using, works for linux
SQL> -- without gzip SQL> @spexp -f x$bh *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start desc table Start to parse desc table Start to apply format for columns Start to create table creating cursor Start to create exporting cursor Start spooling file spexp_x_bh.sql Elapsed time : +000000000 00:00:05.600941000 File size : 10834698 Object/query has been exported into file spexp_x_bh.sql SQL> SQL> -- with gzip SQL> @spexp -f x$bh -gz *** spexp.sql v1.0 beta - (c) Valentin Nikotin (valentinnikotin.com) *** Start desc table Start to parse desc table Start to apply format for columns Start to create table creating cursor Start to create exporting cursor Start spooling file spexp_tmpp_20130422220443.pip Elapsed time : +000000000 00:00:07.550789000 File size : 1172838 Object/query has been exported into file spexp_x_bh.sql.gz
Pingback: SPEXP | ValentinNikotin