当我们登录数据库的时候,默认的模式就是当前登陆的用户。但是如果我们以SYSDBA或者SYSOPER的身份登录数据库的时候,确不是这样。以SYSDBA身份登录的默认模式是SYS,以SYSOPER身份登录的默认模式是PUBLIC。
SQL> show user
USER is "SYS"
创建DEMO用户,并且授予CONNECT,RESOURCE权限
SQL> GRANT CONNECT,RESOURCE TO DEMO IDENTIFIED BY DEMO;
Grant succeeded.
授予DEMO用户SYSDBA权限
SQL> GRANT SYSDBA TO DEMO;
Grant succeeded.
以普通用户身份登录数据库
SQL> CONN DEMO/DEMO
Connected.
SQL> SHOW USER
USER is "DEMO"
可以看到当前的模式为DEMO
创建一个表并且插入一条数据
SQL> CREATE TABLE TEST(ID INT,NAME VARCHAR2(10)) TABLESPACE DATA;
Table created.
SQL> INSERT INTO TEST VALUES(1,'YSP');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM TEST;
ID NAME
---------- ----------
1 YSP
退出SQL*PLUS,然后已SYSDBA的身份登录数据库
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
[oracle@node2 ~]$ sqlplus "demo/demo as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 11 20:50:05 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
我们看一下当前的模式是什么。
SQL> SHOW USER
USER is "SYS"
此时我们查询刚刚创建的表将会提示表或者视图不存在的错误。
SQL> SELECT * FROM TEST;
SELECT * FROM TEST
*
ERROR at line 1:
ORA-00942: table or view does not exist
这时我们在表的前面得加个模式名。
SQL> SELECT * FROM DEMO.TEST;
ID NAME
---------- ----------
1 YSP
当我们以SYSOPER的身份登录数据库的时候当前的模式为PUBLIC.
SQL> CONN / AS SYSDBA
Connected.
SQL> SHOW USER
USER is "SYS"
SQL> GRANT SYSOPER TO DEMO;
Grant succeeded.
SQL> CONN DEMO/DEMO AS SYSOPER;
Connected.
SQL> SHOW USER
USER is "PUBLIC"
SQL> CONN DEMO/DEMO
Connected.
SQL> SHOW USER
USER is "DEMO"
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16162908/viewspace-594769/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16162908/viewspace-594769/