|
|
|
|
|
|
| Search the Reference Library pages: |
|
|
|
|
| Oracle Schema |
| Version 11.1 |
| |
| General |
| Purpose |
Create multiple tables and views and perform multiple grants in your own schema in a single
transaction. If all statements execute successfully, then the database commits them as a single transaction. If any statement
results in an error, then the database rolls back all of the statements.
|
| |
| Create Schema |
Change Schema |
CREATE SCHEMA AUTHORIZATION <schema_name>
<create table or view or grant statement>; |
conn / as sysdba
CREATE USER uwclass
IDENTIFIED BY uwclass
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 10M ON uwdata;
GRANT create session TO uwclass;
GRANT create table TO uwclass;
GRANT create view TO uwclass;
conn uwclass/uwclass
-- first one that doesn't work (t3 does not exist)
CREATE SCHEMA AUTHORIZATION uwclass
CREATE TABLE t1
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE TABLE t2
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE VIEW t1t2_view AS
SELECT t1.tid, t2.last_name FROM t1, t3 WHERE t1.tid = t2.tid
GRANT select ON t1t2_view TO system;
-- then one that does
CREATE SCHEMA AUTHORIZATION uwclass
CREATE TABLE t1
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE TABLE t2
(tid NUMBER(10) PRIMARY KEY, last_name VARCHAR2(20))
CREATE VIEW t1t2_view
AS
SELECT t1.tid, t2.last_name FROM t1, t2 WHERE t1.tid = t2.tid
GRANT select ON t1t2_view TO system; |
|
| |
|
| |
|
-----
|