转载

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

正文到此结束