q友問題之刪除外加雙引號表一點小想法--dump--ascii--to_number

q友問題

請教個問題
我怎麼刪除
dsg使用者下的表名為 segm.tawces的表（我登不了dsg使用者）
只能在sys使用者下刪除
drop table dsg. segm.tawces purge 報錯
我是不是直接escape轉義即可

結論

1,正常建立表和在表名字元加雙引號，建立不同，
2，不同之處於
   A,不正常建立只能建立一種雙引號括起來的字元，要麼是大寫，要麼是小寫，再次建立會提示物件已存在
   B，
3，可見dump的十六進位制是對真實字串以每個字元的16進位制進行儲存的，可以用ascii及to_nubmer進行檢視
SQL> select object_name,length(object_name),object_id,dump(object_name,16) from dba_objects where owner='SCOTT' and lower(object_name) like '%t_spec%';


OBJECT_NAME                    LENGTH(OBJECT_NAME)  OBJECT_ID DUMP(OBJECT_NAME,16)
------------------------------ ------------------- ---------- --------------------------------------------------
T_SPEC                                           6      46418 Typ=1 Len=6: 54,5f,53,50,45,43
t_spec                                           6      46419 Typ=1 Len=6: 74,5f,73,70,65,63


SQL> select to_char(ascii('t'),'xxxxxxxx') from dual;


TO_CHAR(A
---------
       74


SQL> select to_char(ascii('T'),'xxxxxxxx') from dual;


TO_CHAR(A
---------
       54


4，也就是正常建立表是以大寫字元儲存的，而非正常建立（即在正常建立的基礎上外加比引號）它是以小寫字元儲存的，可見ORACLE並不會把外加的雙引號儲存進去，由此可見ORACLE的聰明       

測試

SQL> select * from v$version where rownum=1;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi


SQL> show user
USER is "SCOTT"


SQL> create table t_spec(a int);


Table created.


SQL> select object_name,object_id,dump(object_name) from dba_objects where owner='SCOTT' and object_name like '%T_SPEC%';


OBJECT_NAME                     OBJECT_ID DUMP(OBJECT_NAME)
------------------------------ ---------- --------------------------------------------------
T_SPEC                              46418 Typ=1 Len=6: 84,95,83,80,69,67






SQL> create table 't_spec'(a int);
create table 't_spec'(a int)
             *
ERROR at line 1:
ORA-00903: invalid table name




SQL> create table "t_spec"(a int);


Table created.


SQL> select object_name,object_id,dump(object_name) from dba_objects where owner='SCOTT' and lower(object_name) like '%t_spec%';


OBJECT_NAME                     OBJECT_ID DUMP(OBJECT_NAME)
------------------------------ ---------- --------------------------------------------------
T_SPEC                              46418 Typ=1 Len=6: 84,95,83,80,69,67
t_spec                              46419 Typ=1 Len=6: 116,95,115,112,101,99


SQL> select to_char('849583806967','xxxxxxxxxxxx') from dual;


TO_CHAR('8495
-------------
   c5cf2419f7


---可見小寫字元對應上述的"t_spec"
SQL> select dump('t_spec') from dual;


DUMP('T_SPEC')
-----------------------------------
Typ=96 Len=6: 116,95,115,112,101,99


---大寫字元對應上述的t_spec(即正常建立表)
SQL> select dump('T_SPEC') from dual;


DUMP('T_SPEC')
-------------------------------
Typ=96 Len=6: 84,95,83,80,69,67


SQL> drop table scott."t_spec"
  2  ;


Table dropped.




SQL> select object_name,length(object_name),object_id,dump(object_name) from dba_objects where owner='SCOTT' and lower(object_name) like '%t_spec%';


OBJECT_NAME                    LENGTH(OBJECT_NAME)  OBJECT_ID DUMP(OBJECT_NAME)
------------------------------ ------------------- ---------- --------------------------------------------------
T_SPEC                                           6      46418 Typ=1 Len=6: 84,95,83,80,69,67
t_spec                                           6      46419 Typ=1 Len=6: 116,95,115,112,101,99




SQL> conn scott/system
Connected.
SQL> create table "T_SPEC"(a int);
create table "T_SPEC"(a int)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object




--可見dump的十六進位制是對真實字串以每個字元的16進位制進行儲存的，可以用ascii及to_nubmer進行檢視
SQL> select object_name,length(object_name),object_id,dump(object_name,16) from dba_objects where owner='SCOTT' and lower(object_name) like '%t_spec%';


OBJECT_NAME                    LENGTH(OBJECT_NAME)  OBJECT_ID DUMP(OBJECT_NAME,16)
------------------------------ ------------------- ---------- --------------------------------------------------
T_SPEC                                           6      46418 Typ=1 Len=6: 54,5f,53,50,45,43
t_spec                                           6      46419 Typ=1 Len=6: 74,5f,73,70,65,63


SQL> select to_char(ascii('t'),'xxxxxxxx') from dual;


TO_CHAR(A
---------
       74


SQL> select to_char(ascii('T'),'xxxxxxxx') from dual;


TO_CHAR(A
---------
       54