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