Sql script to create oracle schema

Oracle Database

Below script can be used to create oracle schema and assign tablespace to the schema owner.

--   TODO     :- Change the following, if required:
--            a.    username              '<newschema_username>'
--            b.    data_tablespace       '<newschema_username>_DATA'
--            c.    index_tablespace      '<newschema_username>_IDX'
--            d.    data file name          <newschema_username>_DATA.dbf
--            e.    Index file name          <newschema_username>_IDX.dbf

-- set feedback off;
-- set echo off;
whenever sqlerror continue;

spool create_schema.log
define datafile_data_path    = '&datafile_data_path';
define datafile_index_path   = '&datafile_index_path';
define temp_tablespace       = '&temp_tablespace';
define username              = '<newschema_username>';
define data_tablespace       = '<newschema_username>_DATA';
define index_tablespace      = '<newschema_username>_IDX';


---- ***************************************************
-- Creation of tablespaces
---- ***************************************************

create tablespace &&data_tablespace Datafile '&&datafile_data_path/<newschema_username>_DATA.dbf' size 2048M reuse autoextend on NEXT 100M MAXSIZE UNLIMITED;
create tablespace &&index_tablespace Datafile '&&datafile_index_path/<newschema_username>_IDX.dbf' size 1024M reuse autoextend on NEXT 50M MAXSIZE UNLIMITED;

alter session set "_COMPLEX_VIEW_MERGING"=FALSE;
alter system set "_COMPLEX_VIEW_MERGING"=FALSE scope=both;
alter system set "_allow_level_without_connect_by"=true scope=both;
alter session set "_optimizer_cost_based_transformation" =off;
alter session set "_gby_hash_aggregation_enabled" = FALSE;
alter system set "_optimizer_cost_based_transformation"=off scope=both;
alter system set "_gby_hash_aggregation_enabled"=FALSE scope=both;

---- ***************************************************
-- Creation of users and giving privileges
---- ***************************************************

create user &&username identified by &&username default tablespace &&data_tablespace temporary tablespace &&temp_tablespace;

grant create procedure to &&username;
grant create sequence to &&username;
grant create session to &&username;
grant alter session to &&username;
grant create view to &&username;
grant create table to &&username;
grant create trigger to &&username;
grant create type to &&username;
grant CREATE SYNONYM to &&username;
grant create database link to &&username;

grant select any dictionary to &&username;

grant CREATE ROLE to &&username;

-- Uncomment the following if required

-- grant create any index to &&username;
-- grant create any type to &&username;
-- grant create any trigger to &&username;

alter user &&username quota unlimited on &&data_tablespace;
alter user &&username quota unlimited on &&index_tablespace;

prompt Tablespace / User creation Completed.

spool off;
quit;

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.

2 Responses

  1. santhosha says:

    Hi sir, I am Santhosha working as a junior dba. I have created a sql script by using above sript, In that i need to add tablespace and quota size dynamically. for example i have three tablespace ‘abc,’def’,ghi’ for each tablespace i need to specify different quotas means for tablespace ‘abc’ i need to specify 1m quota, and def unlimited so on.Like this users can specify different quota size as per requirements. below is my script, i have used utl_file which contains tablespace names. but i am not able specify quota size dynamically any help would be appreciated. Below is my script.

    WHENEVER SQLERROR EXIT;

    set show off;
    set verify off;
    set feedback off;
    set serveroutput on;

    DECLARE
    u_file UTL_FILE.FILE_TYPE;
    u_line VARCHAR(32767);
    u_count INTEGER := 0;
    TYPE my_arr1 IS TABLE OF VARCHAR2(100);
    objTablespace my_arr1 := my_arr1();

    BEGIN
    SELECT COUNT (1) INTO u_count FROM dba_users WHERE username = UPPER (‘&&username’);
    IF u_count != 0
    THEN
    dbms_output.put_line(‘User already exisits,Try another..!’);
    RETURN;
    ELSE
    EXECUTE IMMEDIATE ‘create user &&username identified by &&password default tablespace &&default_tablespace temporary tablespace &&temp_tablespace’;
    EXECUTE IMMEDIATE ‘grant dba to &&username’;
    EXECUTE IMMEDIATE ‘revoke unlimited tablespace from &&username’;

    u_file := UTL_FILE.FOPEN(‘UTLFILEDIR’,’tablespacenames.txt’,’r’,32767);
    BEGIN
    LOOP
    UTL_FILE.GET_LINE(u_file, u_line, 32767);
    objTablespace.EXTEND;
    objTablespace(objTablespace.COUNT) := u_line;
    END LOOP;
    EXCEPTION
    WHEN OTHERS THEN — it actually should be no_data_found
    UTL_FILE.FCLOSE(u_file);
    END;

    FOR i IN objTablespace.FIRST..objTablespace.LAST LOOP
    EXECUTE IMMEDIATE ‘alter user &&username quota &&quota_size on ‘ || objTablespace(i);
    END LOOP;
    END IF;

    EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.put_line (SQLERRM);
    DBMS_OUTPUT.put_line (‘ ‘);
    END;
    /
    QUIT;

  2. santhosha says:

    TablespaceNames.txt contains tablespace names like ‘abc’,’def’,’ghi’ ….

Leave a Reply