[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命令变成了关键字.
[20170728]oracle保留字.txt的更多相关文章
-
[20171214]hashcat破解oracle口令.txt
[20171214]hashcat破解oracle口令.txt hashcat is the world's fastest and most advanced password recovery u ...
-
[20171213]john破解oracle口令.txt
[20171213]john破解oracle口令.txt --//跟别人讨论的oracle破解问题,我曾经提过不要使用6位字符以下的密码,其实不管那种系统低于6位口令非常容易破解.--//而且orac ...
-
oracle导入TXT文件
oracle导入TXT文件: 1.建好对应的表和字段:2.新建test.ctl文件,用记事本编辑写入: OPTIONS (skip) load data INFILE 'E:\8080.txt' -- ...
-
oracle 导入txt
没有Oraclehoume的情况下,执行下环境变量文件 sqlldr userid= DM/DM control = /home/oracle/libc/load.ctl load data infi ...
-
Oracle中的Spool缓冲池技术可以实现Oracle导出txt格式文件
利用Oracle中的Spool缓冲池技术可以实现Oracle数据导出到文本文件 1.在Oracle PL/SQL中输入缓冲开始命令,并指定输出的文件名: spool d:output.txt; 2.设 ...
-
Oracle导出txt文本文件
转自: http://blog.csdn.net/ahngzw77/article/details/8652722 对于SPOOL数据的SQL,最好要自己定义格式,以方便程序直接导入,SQL语句如: ...
-
oracle 写入txt
分几个步骤 1,创建输出路径,比如你要在/orcl/dir目录下输出,你就先要建立好这个路径,并在root用户下 chmod 777 /orcl/dir 2,sqlplus下以sysdba登录,执行以 ...
-
shell实现查询oracle数据库表,并写到本地txt文件
1.表结构 create table t_student( id ) primary key, name ), birthday date ); increment ; insert into t_s ...
-
使用sqlldr向Oracle导入大的文本(txt)文件
我们有多种方法可以向Oracle数据库里导入文本文件,但如果导入的文本文件过大,例如5G,10G的文本文件,有些方法就不尽如意了,例如PLSQL Developer中的导入文本功能,如果文本文件过大, ...
随机推荐
-
疑难问题解决备忘录(3)——ubuntu12.04配置vsftp本地用户登录
vsftpd.conf相关项设置 local_enabled = YES write_enable=YES pam_service_name=ftp pam_service_name按默认的vsftp ...
-
网页中插入FLASH(swf文件),并且让Flash不遮挡HTML元素
一:网页中插入flash代码如下: 当然里面的很多属性可以去掉,根据具体的需求而定. 我们在网页中经常遇到播放flash,要正常播放flash就要用到OBJECT和EMBED这两个标签.鉴于火狐及 ...
-
谷歌眼镜--UI指南
1>使用玻璃HTML模板 不是所有的内容都在几行文字来表达.有时候你需要结构化的内容发送到用户的时间轴,或者你需要控制对格式.为了适应这种情况,镜像API提供了一个 HTML 时间表的项目,接受 ...
-
Jquery实现的几款漂亮的时间轴
引言 最近项目中使用了很多前端的东西,对于我一个做后台开发的人员,这是一个很好的锻炼的机会.经过这段时间的学习,感觉前端的东西太多了,太强大了,做出来的东西太炫酷了.现在有很多开源的前端框架,做的都非 ...
-
js必须掌握的基础
好多人想要学习前端……自学或者培训那么我们在学习过程中到底需要掌握那些基础知识呢!下面分类了JS中必备的知识也是必须要了解学会的!看一看你是否已经将JS的基础知识都了如指掌了呢? 事件: onmous ...
-
web api 过滤器
/// <summary> /// 渠道过滤器 /// </summary> [AttributeUsage(AttributeTargets.Class | Attribut ...
-
转:SpringMVC之类型转换Converter(GenericConverter)
转: http://blog.csdn.net/fsp88927/article/details/37692215 SpringMVC 之类型转换 Converter 1.1 目录 1.1 目录 1. ...
-
CentOS静默安装Oracle 11gR2(x64)
环境 OS: CentOS 7.4; hosts: L134; IP: 192.168.1.134 DB: linux.x64_11gR2_database 安装依赖包 yum install -y ...
-
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1169 >; 1024)
### Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1169 > 1024). You ...
-
MongoDB的数据模型
文档的数据模型代表了数据的组织结构,一个好的数据模型能更好的支持应用程序.在MongoDB中,文档有两种数据模型,内嵌(embed)和引用(references). 内嵌 MongoDB的文档是无模式 ...