Sql script to create oracle schema
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.
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 &"a_size on ‘ || objTablespace(i);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
DBMS_OUTPUT.put_line (‘ ‘);
END;
/
QUIT;
TablespaceNames.txt contains tablespace names like ‘abc’,’def’,’ghi’ ….