[20150107]关于print_table.txt
-- 有时候显示的列太多,使用sqlplus显示不是很方便,一般会把它竖着输出。
-- 比较常用的方法就是使用tom的脚本:
1.方法1:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958
CREATE OR REPLACE procedure SYSTEM.print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
execute immediate
'alter session set
nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
-- execute immediate 'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
NULL;
-- execute immediate 'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;
/
--我仅仅改动日期显示格式,这样比较符合国人的习惯。
--这个方法的缺点是要在数据库中建立存储过程。而是仅仅在数据库open状态才能使用。
2.方法2:
$ cat print_table.sql
--http://orasql.org/2013/04/02/sqlplus-tips-2/
-- show output
set termout on
-- but without echo
set echo off
-- without newpage on start:
set embedded on
-- scrolling control
set pause on
-- two lines between rows:
set newpage 2
-- text for prompt after each page:
set pause "Press Enter to view next row..."
-- new page on new "row_num"
break on row_num skip page
-- main query:
select *
from
xmltable( 'for $a at $i in /ROWSET/ROW
,$r in $a/*
return element ROW{
element ROW_NUM{$i}
,element COL_NAME{$r/name()}
,element COL_VALUE{$r/text()}
}'
passing xmltype(cursor( &1 ))
columns
row_num int
-- ,col_name varchar2(30)
,col_name varchar2(16)
,col_value varchar2(100)
);
-- disabling pause and breaks:
set pause off
clear breaks
--或者
cat print_table2.sql
-- http://orasql.org/scripts/print_table2.sql
set termout off
-- set arraysize for minimal value, so sqlplus did not fetch extra rows:
set arraysize 2
-- but without echo
set echo off
-- without newpage on start:
set embedded on
-- scrolling control
set pause on
-- two lines between rows:
set newpage 2
-- disabling xml rewriting:
alter session set events '19027 trace name context forever, level 0x1';
-- text for prompt after each page:
set pause "Press Enter to view next row..."
-- new page on new "row_num"
break on row_num skip page
-- show output
set termout on
-- main query:
select row_num
,t2.*
from
(select rownum row_num
, column_value x
from table(xmlsequence(cursor( &1 )))
) t1
,xmltable( 'for $r in /ROW/*
return element ROW{
element COL_NAME{$r/name()}
,element COL_VALUE{$r/text()}
}'
passing t1.x
columns
col_num for ordinality
,col_name varchar2(30)
,col_value varchar2(100)
)(+) t2;
-- disabling pause and breaks:
set pause off
clear breaks
--这种方法优点是不需要建立存储过程,但是缺点一样仅仅在数据库open状态才能使用。
3.如果有一种能在mount状态就能使用就ok了。
--正好前一阵子要解决字符集转换导入与导出的问题,看了http://www.anysql.net/相关内容:
--可以从他的站点下载http://www.anysql.net/download,Oracle文本导出工具。
$ cat ptsql
#/bin/bash -x
# echo $1
#sqluldr2 user=sys query="$1" text=form control=/dev/null NULL=NULL
sqluldr2 user=sys query="$1" text=form control=/dev/null NULL=NULL field=0x0a0x7c- record=0x0a0x0a rowpre=*-
--里面的参数自己看看文档,还是很好理解的,这个软件参数也太复杂与混乱了。$1也可以使用sql语句。
$ ptsql v\$database
*-DBID : 2071943378
|-NAME : TEST
|-CREATED : 2012-11-28 10:41:17
|-RESETLOGS_CHANGE# : 3011113647
|-RESETLOGS_TIME : 2012-11-05 11:58:00
|-PRIOR_RESETLOGS_CHANGE# : 15808173
|-PRIOR_RESETLOGS_TIME : 2012-08-10 11:04:07
|-LOG_MODE : ARCHIVELOG
|-CHECKPOINT_CHANGE# : 11993477652
|-ARCHIVE_CHANGE# : 11993412310
|-CONTROLFILE_TYPE : CURRENT
|-CONTROLFILE_CREATED : 2012-11-28 10:41:18
|-CONTROLFILE_SEQUENCE# : 856655792
|-CONTROLFILE_CHANGE# : 11993509631
|-CONTROLFILE_TIME : 2015-01-07 16:17:33
|-OPEN_RESETLOGS : NOT ALLOWED
|-VERSION_TIME : 2012-11-28 10:41:17
|-OPEN_MODE : READ WRITE
|-PROTECTION_MODE : MAXIMUM PERFORMANCE
|-PROTECTION_LEVEL : MAXIMUM PERFORMANCE
|-REMOTE_ARCHIVE : ENABLED
|-ACTIVATION# : 2154822220
|-SWITCHOVER# : 2154822220
|-DATABASE_ROLE : PRIMARY
|-ARCHIVELOG_CHANGE# : 11993477652
|-ARCHIVELOG_COMPRESSION : DISABLED
|-SWITCHOVER_STATUS : TO STANDBY
|-DATAGUARD_BROKER : ENABLED
|-GUARD_STATUS : NONE
|-SUPPLEMENTAL_LOG_DATA_MIN : NO
|-SUPPLEMENTAL_LOG_DATA_PK : NO
|-SUPPLEMENTAL_LOG_DATA_UI : NO
|-FORCE_LOGGING : YES
|-PLATFORM_ID : 13
|-PLATFORM_NAME : Linux x86 64-bit
|-RECOVERY_TARGET_INCARNATION# : 1
|-LAST_OPEN_INCARNATION# : 1
|-CURRENT_SCN : 11993509674
|-FLASHBACK_ON : YES
|-SUPPLEMENTAL_LOG_DATA_FK : NO
|-SUPPLEMENTAL_LOG_DATA_ALL : NO
|-DB_UNIQUE_NAME : test
|-STANDBY_BECAME_PRIMARY_SCN : 11377969284
|-FS_FAILOVER_STATUS : DISABLED
|-FS_FAILOVER_CURRENT_TARGET : NULL
|-FS_FAILOVER_THRESHOLD : 0
|-FS_FAILOVER_OBSERVER_PRESENT : NULL
|-FS_FAILOVER_OBSERVER_HOST : NULL
|-CONTROLFILE_CONVERTED : NO
|-PRIMARY_DB_UNIQUE_NAME : testdg
|-SUPPLEMENTAL_LOG_DATA_PL : NO
|-MIN_REQUIRED_CAPTURE_CHANGE# : NULL
--注意 $ 符号要转义一下,不然会当作shell变量。
--结合grep可以更好的获得输出显示,甚至包括column 过滤。
$ ptsql v\$database | grep SUPP
|-SUPPLEMENTAL_LOG_DATA_MIN : NO
|-SUPPLEMENTAL_LOG_DATA_PK : NO
|-SUPPLEMENTAL_LOG_DATA_UI : NO
|-SUPPLEMENTAL_LOG_DATA_FK : NO
|-SUPPLEMENTAL_LOG_DATA_ALL : NO
|-SUPPLEMENTAL_LOG_DATA_PL : NO
-- 再做一个例子来说明:
--比如查询V$SQL_SHARED_CURSOR查看不能共享光标的原因是一个考眼力的事情,使用sqluldr2变得简单了。
select * from dept;
alter session set optimizer_index_cost_adj=200;
select * from dept;
--这样由于参数发生了变化,oracle会生成新的子光标。
SCOTT@test> @share 3154rqzb8xudy
SQL_TEXT = select * from dept
SQL_ID = 3154rqzb8xudy
ADDRESS = 00000000BE88BCB8
CHILD_ADDRESS = 00000000AF798388
CHILD_NUMBER = 0
--------------------------------------------------
SQL_TEXT = select * from dept
SQL_ID = 3154rqzb8xudy
ADDRESS = 00000000BE88BCB8
CHILD_ADDRESS = 00000000BE5A9390
CHILD_NUMBER = 1
OPTIMIZER_MISMATCH = Y
--------------------------------------------------
PL/SQL procedure successfully completed.
--使用sqluldr2也可以实现。
$ ptsql "select * from v\$sql_shared_cursor where sql_id='3154rqzb8xudy'" | grep -v ": N$" -a
*-SQL_ID : 3154rqzb8xudy
|-ADDRESS : 緢几
|-CHILD_ADDRESS : 痽儓
|-CHILD_NUMBER : 0
|-REASON : 03Optimizer mismatch(12)2x216 100 200
*-SQL_ID : 3154rqzb8xudy
|-ADDRESS : 緢几
|-CHILD_ADDRESS : 綵搻
|-CHILD_NUMBER : 1
|-OPTIMIZER_MISMATCH : Y
|-REASON :
--一样可以实现。
--说明:$前要转义\. grep 的过滤要加入参数-a ,等于--binary-files=text。可以发现ADDRESS,CHILD_ADDRESS 出现乱码,这个估计
--是程序的bug,把它当作字符类型来转换了。我前面的脚本没有reason,看来要补充上去。
--总结:
--具体情况具体分析,总有一个适合你。
--实际上在mount状态,你也可以使用图形工具(像toad之类),这样就方便许多。