[20180130]sqlplus字段显示问题.txt

--//昨天下班别人问的问题,就是drop table后使用flashback table恢复,为什么不顺带把索引名字给修改回来.

--//我解答也许避免重名问题吧.不过我在跟踪时发现一些细节,做一些简单探究.

1.环境:

SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table deptx as select * from dept;

create index pk_deptx on deptx(deptno); drop table deptx ;

SCOTT@book> select OBJ#,OWNER#,ORIGINAL_NAME,DROPSCN,FLAGS from sys.recyclebin$;

        OBJ#       OWNER# ORIGINAL_NAME                         DROPSCN        FLAGS ------------ ------------ -------------------------------- ------------ ------------        91061           83 PK_DEPTX                          13278152690           18        91060           83 DEPTX                             13278152694           30 --//可以发现原始的对象名,oracle是知道的.

--//我在做一些跟踪时,发现执行如下:

SCOTT@book> show recyc ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME ---------------- ------------------------------ ------------ ------------------- DEPTX            BIN$Y/SRPk++5tPgU05kqMDL8Q==$0 TABLE        2018-01-30:08:37:28

--//执行做了过滤实际上执行如下,只不过加了where条件,不显示索引.(我取消了)

SELECT ORIGINAL_NAME ORIGNAME_PLUS_SHOW_RECYC

       ,OBJECT_NAME OBJECTNAME_PLUS_SHOW_RECYC        ,TYPE OBJTYPE_PLUS_SHOW_RECYC        ,DROPTIME DROPTIME_PLUS_SHOW_RECYC   FROM USER_RECYCLEBIN ;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ ------------------- PK_DEPTX         BIN$Y/SRPk+95tPgU05kqMDL8Q==$0 INDEX        2018-01-30:08:37:28 DEPTX            BIN$Y/SRPk++5tPgU05kqMDL8Q==$0 TABLE        2018-01-30:08:37:28

--//你可以注意ORIGNAME_PLUS_SHOW_RECYC实际上显示的是ORIGINAL NAME(中间有空格).很明显登录sqlplus时oracle定义了ORIGNAME_PLUS_SHOW_RECYC.

SCOTT@book> col ORIGNAME_PLUS_SHOW_RECYC COLUMN   origname_plus_show_recyc ON HEADING  'ORIGINAL NAME' FORMAT   a16

--//如果你使用toad,就没有上面的显示字段名.

--//问题是这个定义写在那里,那个文件,做一个简单探究.

$ strings $(which sqlplus) | grep -i ORIGNAME_PLUS_SHOW_RECYC

$ strings $(which oracle)  | grep -i ORIGNAME_PLUS_SHOW_RECYC --//没有sqlplus.oracle执行文件中.

$ strace -e trace=open -o /tmp/xx.out sqlplus scott/book

$ egrep -v "No such file or directory" /tmp/xx.out | cut -d\" -f2 | xargs -I {} strings {} | grep -i ORIGNAME_PLUS_SHOW_RECYC

origname_plus_show_recyc FORMAT a16 HEADING 'ORIGINAL NAME' SELECT ORIGINAL_NAME ORIGNAME_PLUS_SHOW_RECYC,OBJECT_NAME OBJECTNAME_PLUS_SHOW_RECYC,TYPE OBJTYPE_PLUS_SHOW_RECYC,DROPTIME DROPTIME_PLUS_SHOW_RECYC FROM USER_RECYCLEBIN WHERE CAN_UNDROP='YES' COLUMN origname_plus_show_recyc   FORMAT a16 HEADING 'ORIGINAL NAME' column origname_plus_show_recyc   FORMAT a16 HEADING 'ORIGINAL NAME'

--//无法知道是那个文件.修改如下:

$ egrep -v "No such file or directory" /tmp/xx.out | cut -d\" -f2 | xargs -I {} strings --print-file-name {} | grep -i ORIGNAME_PLUS_SHOW_RECYC /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libsqlplus.so: origname_plus_show_recyc FORMAT a16 HEADING 'ORIGINAL NAME' /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libsqlplus.so: SELECT ORIGINAL_NAME ORIGNAME_PLUS_SHOW_RECYC,OBJECT_NAME OBJECTNAME_PLUS_SHOW_RECYC,TYPE OBJTYPE_PLUS_SHOW_RECYC,DROPTIME DROPTIME_PLUS_SHOW_RECYC FROM USER_RECYCLEBIN WHERE CAN_UNDROP='YES'

--//很明显在使用sqlplus是调用了/u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libsqlplus.so.里面定义了origname_plus_show_recyc的显示 --//实际上链接http://blog.itpub.net/267265/viewspace-2146887/. --//VALUE_COL_PLUS_SHOW_SPPARAM应该也是存在这个文件里面的.

$ egrep -v "No such file or directory" /tmp/xx.out | cut -d\" -f2 | xargs -I {} strings --print-file-name {} | grep -i VALUE_COL_PLUS_SHOW_SPPARAM

/u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libsqlplus.so: VALUE_COL_PLUS_SHOW_SPPARAM FORMAT a28 word_wrapped HEADING VALUE /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libsqlplus.so: SELECT SID SID_COL_PLUS_SHOW_SPPARAM, NAME NAME_COL_PLUS_SHOW_SPPARAM, TYPE, DISPLAY_VALUE VALUE_COL_PLUS_SHOW_SPPARAM FROM V$SPPARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_SPPARAM,VALUE_COL_PLUS_SHOW_SPPARAM /u01/app/oracle/product/11.2.0.4/dbhome_1/sqlplus/admin/zzlogin.sql: column value_col_plus_show_spparam format a100 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//最后调用我定义的zzlogin.sql,修改显示宽度.