转载

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




正文到此结束
Loading...