Oracle

Oracle DBA's Experts, Pro's & Beginner's are requested to post here and share ideas about DBA roles and responsibilities..
www.oracle.com


Oracle Administration workshop I on Managing schema objects
oracle schema ojects

Create schema object

SQL> SHOW USER 
USER is "MAG"
SQL> CREATE SCHEMA AUTHORIZATION CRSCH
  2     CREATE TABLE new_product
  3        (color VARCHAR2(10)  PRIMARY KEY, quantity NUMBER)
  4     CREATE VIEW new_product_view
  5        AS SELECT color, quantity FROM new_product WHERE color = 'RED';
CREATE SCHEMA AUTHORIZATION CRSCH
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier

SQL> CONNECT CRSCH/CRSCH
Connected.
SQL> SHOW USER
USER is "CRSCH"
SQL> CREATE SCHEMA AUTHORIZATION CRSCH
  2     CREATE TABLE new_product
  3        (color VARCHAR2(10)  PRIMARY KEY, quantity NUMBER)
  4     CREATE VIEW new_product_view
  5        AS SELECT color, quantity FROM new_product WHERE color = 'RED';

Schema created.
SQL> SELECT * FROM CAT;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
NEW_PRODUCT                    TABLE
NEW_PRODUCT_VIEW               VIEW

Oracle Error while creating schema

ORA-02421: missing or invalid schema authorization identifier

I tried creating a schema with authorization and got the following error. Sequence of steps and fix have been discussed here

SQL> create schema authorization play;

create schema authorization play

*

ERROR at line 1:

ORA-02421: missing or invalid schema authorization identifier

SQL> create user play identified by play;

User created.

SQL> grant create session to play;

Grant succeeded.

SQL> connect play/play

Connected.

SQL> create schema authorization play;

Schema created.

SQL commands

DESC[RIBE] (SQL*Plus command)

Describe an Oracle Table, View, Synonym, package or Function.

Note that because this is a SQL*Plus command you don't need to terminate it with a semicolon.

Syntax:

   DESC table

   DESC view

   DESC synonym

   DESC function

   DESC package

Renaming Columns And Constraints

In addition to renaming tables and indexes Oracle9i Release 2 allows the renaming of columns and constraints on tables. In this example once the the TEST1 table is created it is renamed along with it's columns, primary key constraint and the index that supports the primary key.

SQL> CREATE TABLE test1 (
  2    col1  NUMBER(10) NOT NULL, 
  3    col2  VARCHAR2(50) NOT NULL);

Table created.

SQL> ALTER TABLE test1 ADD (
  2    CONSTRAINT test1_pk PRIMARY KEY (col1));

Table altered.

SQL> DESC test1
 Name                 Null?    Type
 -------------------- -------- --------------------
 COL1                 NOT NULL NUMBER(10)
 COL2                 NOT NULL VARCHAR2(50)

SQL> SELECT constraint_name
  2  FROM   user_constraints
  3  WHERE  table_name      = 'TEST1'
  4  AND    constraint_type = 'P';

CONSTRAINT_NAME
------------------------------
TEST1_PK

1 row selected.

SQL> SELECT index_name, column_name
  2  FROM   user_ind_columns
  3  WHERE  table_name = 'TEST1';

INDEX_NAME            COLUMN_NAME
--------------------  --------------------
TEST1_PK              COL1

1 row selected.

SQL> -- Rename the table, columns, primary key
SQL> -- and supporting index.
SQL> ALTER TABLE test1 RENAME TO test;

Table altered.

SQL> ALTER TABLE test RENAME COLUMN col1 TO id;

Table altered.

SQL> ALTER TABLE test RENAME COLUMN col2 TO description;

Table altered.

SQL> ALTER TABLE test RENAME CONSTRAINT test1_pk TO test_pk;

Table altered.

SQL> ALTER INDEX test1_pk RENAME TO test_pk;

Index altered.

SQL> DESC test
 Name                 Null?    Type
 -------------------- -------- --------------------
 ID                   NOT NULL NUMBER(10)
 DESCRIPTION          NOT NULL VARCHAR2(50)

SQL> SELECT constraint_name
  2  FROM   user_constraints
  3  WHERE  table_name      = 'TEST'
  4  AND    constraint_type = 'P';

CONSTRAINT_NAME
--------------------
TEST_PK

1 row selected.

SQL> SELECT index_name, column_name
  2  FROM   user_ind_columns
  3  WHERE  table_name = 'TEST';

INDEX_NAME            COLUMN_NAME
--------------------  --------------------
TEST_PK               ID

1 row selected.