[20180130]sqlplus欄位顯示問題.txt

[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,修改顯示寬度.