CREATE SCHEMA和CURRENT_SCHEMA

create schema是为了实现把创建多个表和多个视图多个授权放在一个事务内实现，
如果任何一个出现错误，全部创建和授权语句回滚。

schema是当前登陆的用户名，即使通过修改CURRENT_SCHEMA也是不行的。

CREATE SCHEMA AUTHORIZATION LOVE2008
CREATE TABLE CHERRY(ID NUMBER,NAME VARCHAR2(20))
CREATE VIEW LOVE_VIEW AS SELECT * FROM SYSTEM.LOVE_ONE;

注意：在创建VIEW的时候，不能用CREATE OR REPLACE创建。

--查看当前SESSION的SCHEMA
SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;

 

下面测试一个修改了CURRENT_SCHEMA的问题：
SQL> CONN LOVE2008/LOVE2008
Connected.
SQL> show user
USER is "LOVE2008"
SQL> select sys_context('USERENV','CURRENT_SCHEMA') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
LOVE2008

--修改当前会话的SCHEMA为SYSTEM

SQL> ALTER SESSION SET CURRENT_SCHEMA=SYSTEM;

Session altered.

SQL> SHOW USER
USER is "LOVE2008"
SQL> SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') CURRENT_SCHEMA FROM DUAL;

CURRENT_SCHEMA
--------------------------------------------------------------------------------
SYSTEM

SQL> SELECT TABLE_NAME FROM DBA_TABLES WHERE WNER='LOVE2008';

TABLE_NAME
------------------------------
IND_CUSTOMER_KEEP_FLAG
II
LOVE_TABLE
TEST1
TEST2
TEST3
WZK_PARTTION
WZK_PARTTION2
WZK_TEST

9 rows selected.

SQL> SELECT TABLE_NAME FROM DBA_TABLES WHERE WNER='SYSTEM' AND TABLE_NAME LIKE 'TEST%';

no rows selected

SQL> SELECT * FROM TEST1;
SELECT * FROM TEST1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> SELECT * FROM LOVE2008.TEST1;

        ID NAME
---------- ----------
         1 wzk
         2 cherry

SQL> CREATE TABLE WZK_ONE (ID NUMBER);

Table created.

--默认在这里添加了SYSTEM

SQL> SELECT * FROM WZK_ONE;

no rows selected

--创建表也创建在了SYSTEM上

SQL> SELECT TABLE_NAME,OWNER FROM DBA_TABLES WHERE TABLE_NAME='WZK_ONE';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
WZK_ONE                        SYSTEM

SQL>

操作数据库是通过SCHEMA来的，而不是通过USER来的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/728254/viewspace-257029/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/728254/viewspace-257029/