You can create several tables and views and grant privileges in one operation using the
CREATE SCHEMA statement. The
CREATE SCHEMA statement is useful if you want to guarantee the creation of several tables, views, and grants in one operation. If an individual table, view or grant fails, the entire statement is rolled back. None of the objects are created, nor are the privileges granted.
CREATE SCHEMA statement can include only
CREATE VIEW, and
GRANT statements. You must have the privileges necessary to issue the included statements. You are not actually creating a schema, that is done when the user is created with a
CREATE USER statement. Rather, you are populating the schema.
The following statement creates two tables and a view that joins data from the two tables:
CREATE SCHEMA AUTHORIZATION scott CREATE TABLE dept ( deptno NUMBER(3,0) PRIMARY KEY, dname VARCHAR2(15), loc VARCHAR2(25)) CREATE TABLE emp ( empno NUMBER(5,0) PRIMARY KEY, ename VARCHAR2(15) NOT NULL, job VARCHAR2(10), mgr NUMBER(5,0), hiredate DATE DEFAULT (sysdate), sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(3,0) NOT NULL CONSTRAINT dept_fkey REFERENCES dept) CREATE VIEW sales_staff AS SELECT empno, ename, sal, comm FROM emp WHERE deptno = 30 WITH CHECK OPTION CONSTRAINT sales_staff_cnst GRANT SELECT ON sales_staff TO human_resources;
CREATE SCHEMA statement does not support Oracle Database extensions to the ANSI
CREATE TABLE and
CREATE VIEW statements, including the
See Also:Oracle Database SQL Language Reference for syntax and other information about the