[20170728]oracle保留字.txt

[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個欄位.

--//官方連結:
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命令變成了關鍵字.