Schema / User Creation in Oracle Database

Schema Creation in Oracle Database
---------------------------------------------

-- TO CREATE TABLESPACE

CREATE TABLESPACE TBS_XXAPLDALL DATAFILE 'datafile_name.dbf' SIZE 3G;

CREATE TEMPORARY TABLESPACE TEMP_XXAPLDALL TEMPFILE 'tempfile_name.dbf' SIZE 5M AUTOEXTEND ON;

-- TO CREATE SCHEMA

CREATE USER XXAPLDALL IDENTIFIED BY XXAPLDALL DEFAULT TABLESPACE TBS_XXAPLDALL TEMPORARY TABLESPACE TEMP_XXAPLDALL PROFILE DEFAULT ACCOUNT UNLOCK;

-- TO CREATE SESSION

GRANT CREATE SESSION TO systemappsil;

-- TO ALTER SESSION

GRANT ALTER SESSION TO systemappsil;

-- TO CREATE SYNONYM

GRANT CREATE SYNONYM TO systemappsil;

-- TO CREATE TABLE

GRANT CREATE TABLE TO systemappsil;

-- TO CREATE VIEW

GRANT CREATE VIEW TO systemappsil;

-- TO CREATE SEQUENCE

GRANT CREATE SEQUENCE TO systemappsil;

-- TO CREATE TRIGGER 

GRANT CREATE TRIGGER TO systemappsil;

-- TO CREATE PROCEDURE

GRANT CREATE PROCEDURE TO systemappsil;

-- TO UNLIMIT TABLESPACE

GRANT UNLIMITED TABLESPACE TO systemappsil;

-- TO GIVE THE EDITIONS:

ALTER USER XXAPLDALL ENABLE EDITIONS;

-- TO GIVE THE "ENQUEUE","DEQUEUE" PRIVILEGE

begin
 DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
        PRIVILEGE => 'ENQUEUE_ANY',
        GRANTEE => 'XXAPLDALL',
        ADMIN_OPTION => FALSE);

DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
        PRIVILEGE => 'DEQUEUE_ANY',
        GRANTEE => 'XXAPLDALL',
        ADMIN_OPTION => FALSE);
        end ;

-- TO CREATE EXECUTE PRIVILEGE

grant execute any procedure to XXAPLDALL;


*************************************************************************************************************
USER CREATION LIKE APPS/SYSTEM :

CREATE OR REPLACE TRIGGER readonly_logon_trg
AFTER logon ON systemappsil.SCHEMA
DECLARE
BEGIN
EXECUTE IMMEDIATE 
        'declare begin ' ||
        'dbms_application_info.set_client_info ( 101 ); end;';
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =SYSTEM';
END;
/

*************************************************************************************************************