Oracle 19c

 

To demonstrate the CREATE SCHEMA command, let's create 2 users on a test database.  I love to create 2 users at once with this simple command:

 

grant create session to fd1, fd2 identified by oracle, oracle;

Grant succeeded.

 

30-NOV-19 lesfous SYSTEM > select account_status,username,created from dba_users where created > sysdate-1;

 

ACCOUNT_STATUS                   USERNAME               CREATED

-------------------------------- ---------------------- ------------------

OPEN                             FD1                    30-NOV-19

OPEN                             FD2                    30-NOV-19

 

Let's grant them some rights so as to do some work:

grant SELECT_CATALOG_ROLE, select ANY dictionary, create any table,CREATE any view, create any synonym, create trigger to FD2;
grant SELECT_CATALOG_ROLE, select ANY dictionary, create any table,CREATE any view, create any synonym, create trigger to FD1;

 

Let's grant them some quota on a tablespace so they can create data in it:

SYSTEM > alter user fd2 quota unlimited on users;

User altered.

 

SYSTEM > alter user fd1 quota unlimited on users;

User altered

 

Now let's use the CREATE SCHEMA command for user FD2 with a simple example found on the Internet:

The first thing I wanted to share with you is that the CREATE SCHEMA command should really be named CREATE SCHEMA AUTHORIZATION because the AUTHORIZATION keyword is mandatory.  So here goes:

FD2 > CREATE SCHEMA AUTHORIZATION fd2

CREATE TABLE new_product (product VARCHAR2(900), color VARCHAR2(10) , quantity NUMBER)

CREATE VIEW redproduct_view AS SELECT product, quantity FROM new_product WHERE color = 'RED'

GRANT select ON redproduct_view  TO fd1; 

 

Schema created.

 

The above command demonstates the 3 things you can do with a CREATE SCHEMA: CREATE TABLEs, CREATE VIEWs, and GRANT rights to other users.

Let's populate this new table:

FD2 > insert into new_product values ('Beads','BLUE',100);

1 row created.

FD2 > insert into new_product values ('Neckerchief','RED',2);

1 row created.

FD2 > insert into new_product values ('Waxstrips','BLUE',300);

1 row created.

FD2 > insert into new_product values ('Ding-A-Ling','RED',4);

1 row created.

 

After commiting those 4 INSERTs, I connect with FD1 and check the contents of the only view that FD1 has rights on:

FD1 > select * from fd2.redproduct_view;

PRODUCT                                    QUANTITY

---------------------------------------- ----------

Neckerchief                                       2

Ding-A-Ling                                       4

 

So this is basically what CREATE SCHEMA does: it speeds things up when you have several objects to create and rights on them to grant since you can do all that in one command.  It's useful to create a data-less schema.  If only one of the CREATEs or GRANTs generates an error, nothing is carried out: the whole CREATE SCHEMA statement is rollbacked.  Note that indexes cannot be mentioned within a CREATE SCHEMA command, so you'll have to create your indexes with separate CREATE INDEXEs after running the CREATE SCHEMA command if you want performance indexes on the tables that CREATE SCHEMA has created.

 

But I discovered another, less documented, downside to that CREATE SCHEMA command.  I wanted to create a schema with my own toolkit to monitor Oracle databases, so I tried to create this schema (here for the F1 user):

FD1 > select object_type,object_name,created from user_objects;

no rows selected

 

FD1 > CREATE SCHEMA  AUTHORIZATION FD1

    

CREATE TABLE dmk_data_files as SELECT tablespace_name , file_id

, CASE

WHEN f.tablespace_name LIKE 'SYS%' THEN 'SYSTEM'

WHEN f.tablespace_name LIKE 'UNDO%' THEN 'UNDO'

WHEN f.tablespace_name LIKE '%IDX  2  01-DEC-19 UPGR FD1 >   2    3    4    5    6  %' THEN 'INDEX'

WHEN f.tablespace_name LIKE '%INDEX%' THEN 'INDEX'

ELSE 'TABLE'

END AS tablespace_type

FROM dba_data_files f

ORDER BY tablespace_name

 

CREATE TABLE DMK_my_ash AS

SELECT /*+LEADING(x) USE_NL(h)*/ h.*

FROM dba_hist_snapshot x  ,dba_hist_active_sess_history h

WHERE x.end_interval_time >=TO_DATE('201401261100','yyyymmddhh24mi') AND x.begin_interval_time<=TO_DATE('201401261300','yyyymmddhh24mi')

AND h.sample_time BETWEEN TO_DATE('201401261100','yyyymmddhh24mi') AND TO_DATE('201401261300','yyyymmddhh24mi')

AND h.snap_id= X.snap_id AND h.dbid= x.dbid AND h.instance_number= x.instance_number

 

CREATE TABLE dmk_objects

(object_id NUMBER NOT NULL ,owner VARCHAR2(30) NOT NULL,object_name VARCHAR2(128) NOT NULL,subobject_name VARCHAR2(30)

,PRIMARY KEY (OBJECT_ID));

 

GRANT SELECT on DMK_MY_ASH to fd2 ;

Table created.

 

FD1 > FD1 >

GRANT SELECT on DMK_MY_ASH to fd2

                *

ERROR at line 1:

ORA-00942: table or view does not exist

 

 

FD1 > select object_type,object_name,created from user_objects;

OBJECT_TYPE             OBJECT_NAME            CREATED

----------------------- ---------------------- ------------------

TABLE                   DMK_OBJECTS            01-DEC-19

INDEX                   SYS_C007595            01-DEC-19

 

As you can see above, this CREATE SCHEMA command is made up of 3 CREATE TABLEs and 1 GRANT.  It did not produce the "Schema created" message but I got one "Table created." and one ORA-00942. My SELECT FROM USER_OBJECTS shows that only one of the three tables was created, along with an unamed index.  The ORA-00942 occured because the DMK_MY_ASH table was not created.  So this is very tricky because as stated before, the documentation says that if one of the statements fails, all statements are rolbacked.  But here one of our 3 tables was created.  This is because elswhere the documentation also says "The CREATE SCHEMA statement supports the syntax of these statements only as defined by standard SQL, rather than the complete syntax supported by Oracle Database." Just one sentence to explain that in my example above, the DMK_DATA_FILES table could not be created probably because of its CASE function, and the DMK_MY_ASH table could not be created probably because of its hint.  The DMK_OBJECTS table, however, was created because it uses no fancy Oracle feature. 

Little challenge to my dear readers: where did that SYS_C007595 index come from (as I did not issue any CREATE INDEX command, and the CREATE SCHEMA command does not support it anyway)?

Please comment below.