[20170728]oracle reserved words. txt

[20170728]oracle reserved words. txt

--//oracle has a lot of reserved words, what I remember most is the use of Rman backup table Space test,test is the reserved word in Rman.
--//and Rman are also reserved words in Rman. Do not use these reserved words if you try to avoid them in your app.

--//Explore whether Oracle internally will also be careless with these keywords.

1. Environment:
[Email protected]> @ 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

[Email protected]> @ 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

[Email protected]> 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. Check to see:
SELECT distinct Owner,table_name
From Dba_tab_columns
WHERE column_name in (SELECT KEYWORD from v$reserved_words);

--//output too much, ignore. I didn't think so much, or I had a problem with my query. Find one of the view 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
--//has 4 fields.

--//Official Link: 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 are reserved in any,
Check the RESERVED, Res_type, res_attr, and Res_semi columns.

Column Datatype Description
KEYWORD VARCHAR2 (+) Name of the KEYWORD
Length number length of the keyword
RESERVED VARCHAR2 (1) Indicates whether the keyword cannot be used as a 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 are 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 are not allowed as a identifier in certain situations, such as
In DML (Y) or whether the keyword are not reserved (N)
DUPLICATE VARCHAR2 (1) Indicates whether the keyword are 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

[Email protected]> select * from V$recover_file;
No rows selected

[Email protected]> 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:

--//This hint is very confusing, but pay special attention to the location of the asterisk below, pointing to online.
--//avoid it using double quotes, and note to capitalize:

[Email protected]> Select file#, "ONLINE", ERROR, time,con_id from V$recover_file;
No rows selected
--//other fields are no problem except the online field.

[Email protected]> 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


[Email protected]> ALTER DATABASE datafile 9 offline;
Database altered.

[Email protected]> 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

[Email protected]> 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

[Email protected]> recover datafile 9;
Media recovery complete.

[Email protected]> ALTER DATABASE datafile 9 online;
Database altered.

Anyway:
--//in the application as far as possible to circumvent these reserved words, avoid unnecessary trouble!!
--//some additional examples under 11g:

[Email protected]> @ &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

[Email protected]> 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 File:standard input

[Email protected]> 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 File:standard input

[Email protected]> 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

--//in Sqlplus's command is not the keyword of the Test,rman, to the Rman command becomes the keyword.

[20170728]oracle reserved words. txt