[20170728]oracle保留字.txt--//oracle有許多保留字,我印象最深的就是使用rman備份表空間test,test就是rman裡面的保留字.--//還有rman也是rman裡面的保留字.如果在應用中儘量規避不要使用這些保留字.--//探究一下,oracle內部是否也會不小心這些 ...
[20170728]oracle保留字.txt
--//oracle有許多保留字,我印象最深的就是使用rman備份表空間test,test就是rman裡面的保留字.
--//還有rman也是rman裡面的保留字.如果在應用中儘量規避不要使用這些保留字.
--//探究一下,oracle內部是否也會不小心這些關鍵字.
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> @ desc v$reserved_words ;
Name Null? Type
---------- -------- ----------------------------
KEYWORD VARCHAR2(30)
LENGTH NUMBER
RESERVED VARCHAR2(1)
RES_TYPE VARCHAR2(1)
RES_ATTR VARCHAR2(1)
RES_SEMI VARCHAR2(1)
DUPLICATE VARCHAR2(1)
CON_ID NUMBER
SCOTT@test01p> select * from v$reserved_words where KEYWORD='TEST' or keyword='RMAN';
KEYWORD LENGTH R R R R D CON_ID
------------------------------ ---------- - - - - - ----------
TEST 4 N N N N N 0
2.查詢看看:
SELECT distinct owner,table_name
FROM dba_tab_columns
WHERE column_name IN (SELECT KEYWORD FROM v$reserved_words);
--//輸出太多,忽略.沒有想到如此之多,還是我查詢有問題.找其中一個視圖V$RECOVER_FILE.
SELECT owner,table_name,column_name
FROM dba_tab_columns
WHERE column_name IN (SELECT KEYWORD FROM v$reserved_words) and table_name ='V_$RECOVER_FILE';
OWNER TABLE_NAME COLUMN_NAME
----- --------------- --------------------
SYS V_$RECOVER_FILE ONLINE
SYS V_$RECOVER_FILE ERROR
SYS V_$RECOVER_FILE TIME
SYS V_$RECOVER_FILE CON_ID
--//有4個欄位.
--//官方鏈接:http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_2126.htm#REFRN30204
V$RESERVED_WORDS
V$RESERVED_WORDS displays a list of all SQL keywords. To determine whether a particular keyword is reserved in any way,
check the RESERVED, RES_TYPE, RES_ATTR, and RES_SEMI columns.
Column Datatype Description
KEYWORD VARCHAR2(30) Name of the keyword
LENGTH NUMBER Length of the keyword
RESERVED VARCHAR2(1) Indicates whether the keyword cannot be used as an identifier (Y) or whether the keyword is
not reserved (N)
RES_TYPE VARCHAR2(1) Indicates whether the keyword cannot be used as a type name (Y) or whether the keyword is not
reserved (N)
RES_ATTR VARCHAR2(1) Indicates whether the keyword cannot be used as an attribute name (Y) or whether the keyword
is not reserved (N)
RES_SEMI VARCHAR2(1) Indicates whether the keyword is not allowed as an identifier in certain situations, such as
in DML (Y) or whether the keyword is not reserved (N)
DUPLICATE VARCHAR2(1) Indicates whether the keyword is a duplicate of another keyword (Y) or whether the keyword is
not a duplicate (N)
SELECT *
FROM v$reserved_words
WHERE keyword IN ('ONLINE', 'ERROR', 'TIME', 'CON_ID');
KEYWORD LENGTH R R R R D CON_ID
------- ------- - - - - - ----------
CON_ID 6 N N N N N 0
ERROR 5 N N N N N 0
TIME 4 N N N N N 0
ONLINE 6 N N N Y N 0
SCOTT@test01p> select * from V$RECOVER_FILE;
no rows selected
SCOTT@test01p> select file#,ONLINE,ERROR, TIME,CON_ID from V$RECOVER_FILE;
select file#,ONLINE,ERROR, TIME,CON_ID from V$RECOVER_FILE
*
ERROR at line 1:
ORA-00936: missing expression
D:\tools\rlwrap>oerr ora 00936
00936, 00000, "missing expression"
// *Cause:
// *Action:
--//出現這個提示非常具有迷惑性,不過要特別註意下麵的星號的位置,指向ONLINE.
--//規避它使用雙引號,並且註意要大寫:
SCOTT@test01p> select file#,"ONLINE",ERROR, TIME,CON_ID from V$RECOVER_FILE;
no rows selected
--//其他欄位沒問題,除了ONLINE欄位.
SCOTT@test01p> select file#,"online",ERROR, TIME,CON_ID from V$RECOVER_FILE;
select file#,"online",ERROR, TIME,CON_ID from V$RECOVER_FILE
*
ERROR at line 1:
ORA-00904: "online": invalid identifier
SCOTT@test01p> alter database datafile 9 offline;
Database altered.
SCOTT@test01p> select file#,"online",ERROR, TIME,CON_ID from V$RECOVER_FILE;
select file#,"online",ERROR, TIME,CON_ID from V$RECOVER_FILE
*
ERROR at line 1:
ORA-00904: "online": invalid identifier
SCOTT@test01p> select file#,"ONLINE",ERROR, TIME,CON_ID from V$RECOVER_FILE;
FILE# ONLINE ERROR TIME CON_ID
---------- ------- ------- ------------------- ----------
9 OFFLINE 2017-07-27 21:01:22 3
SCOTT@test01p> recover datafile 9;
Media recovery complete.
SCOTT@test01p> alter database datafile 9 online;
Database altered.
總之:
--//在應用中儘量規避這些保留字,避免不必要的麻煩!!
--//在11g下再補充一些例子:
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
SCOTT@book> alter tablespace tea rename to test;
Tablespace altered.
RMAN> backup tablespace test ;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "test": expecting one of: "double-quoted-string, identifier, single-quoted-string"
RMAN-01007: at line 1 column 19 file: standard input
SCOTT@book> alter tablespace test rename to rman;
Tablespace altered.
RMAN> backup tablespace rman ;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "rman": expecting one of: "double-quoted-string, identifier, single-quoted-string"
RMAN-01007: at line 1 column 19 file: standard input
SCOTT@book> alter tablespace rman rename to tea;
Tablespace altered.
RMAN> backup tablespace tea;
Starting backup at 2017-07-28 08:42:12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=94 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=106 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=119 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/mnt/ramdisk/book/tea01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-07-28 08:42:14
channel ORA_DISK_1: finished piece 1 at 2017-07-28 08:42:15
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/backupset/2017_07_28/o1_mf_nnndf_TAG20170728T084214_dqo2364j_.bkp tag=TAG20170728T084214 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-07-28 08:42:15
Starting Control File and SPFILE Autobackup at 2017-07-28 08:42:15
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2017_07_28/o1_mf_s_950517735_dqo23786_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2017-07-28 08:42:16
--//在sqlplus的命令中不是的關鍵字的test,rman,到了rman命令變成了關鍵字.
