Created
December 29, 2022 07:20
-
-
Save brovish/3229f2fe9f1e46414ccfb55e0e9a9eb9 to your computer and use it in GitHub Desktop.
Oracle script to recreate the schema (delete if there is a connection user, drop schema, recreate) DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp diagram as parameter, password p240580 you can expand it, for example, a password, so that it would be an optional parameter
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WHENEVER SQLERROR EXIT SQL.SQLCODE | |
| SET ECHO ON | |
| / | |
| set autocommit on | |
| DEF username = &1 | |
| --DEF pwd = &2 | |
| / | |
| DEF default_ts = users | |
| / | |
| DEF temp_ts = temp | |
| / | |
| SPOOL create_user.log | |
| / | |
| BEGIN | |
| FOR ln_cur IN (SELECT sid, serial# FROM v$session WHERE username = upper('&username')) | |
| LOOP | |
| dbms_output.put_line('SQL_CMD:ALTER SYSTEM KILL SESSION ''' || ln_cur.sid || ',' || ln_cur.serial# || ''' IMMEDIATE'); | |
| EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || ln_cur.sid || ',' || ln_cur.serial# || ''' IMMEDIATE'); | |
| END LOOP; | |
| END; | |
| / | |
| DECLARE | |
| v_count INTEGER := 0; | |
| v_statement VARCHAR2(500); | |
| BEGIN | |
| SELECT | |
| COUNT(1) | |
| INTO v_count | |
| FROM | |
| dba_users | |
| WHERE | |
| username = upper('&username'); | |
| IF v_count != 0 THEN | |
| dbms_output.put_line('SQL_CMD:DROP USER &username CASCADE'); | |
| EXECUTE IMMEDIATE ( 'DROP USER &username CASCADE' ); | |
| END IF; | |
| v_count := 0; | |
| EXCEPTION | |
| WHEN OTHERS THEN | |
| dbms_output.put_line(sqlerrm); | |
| dbms_output.put_line(' '); | |
| END; | |
| / | |
| WHENEVER SQLERROR CONTINUE; | |
| CREATE USER &username IDENTIFIED BY p240580 | |
| DEFAULT TABLESPACE &default_ts | |
| TEMPORARY TABLESPACE &temp_ts; | |
| / | |
| WHENEVER SQLERROR EXIT SQL.SQLCODE | |
| --CREATE USER ORADMMG0 IDENTIFIED BY CATEKprep | |
| --DEFAULT TABLESPACE USERS QUOTA unlimited ON USERS | |
| --TEMPORARY TABLESPACE TEMPTBS QUOTA unlimited ON USERS; --on 10th TEMP instead of TEMPTBS | |
| --GRANT CREATE SESSION TO ORADMMG0; | |
| --GRANT CREATE TYPE TO ORADMMG0; | |
| --GRANT CREATE PROCEDURE TO ORADMMG0; | |
| --GRANT CREATE TABLE TO ORADMMG0; | |
| --GRANT CREATE TRIGGER TO ORADMMG0; | |
| --GRANT CREATE VIEW TO ORADMMG0; | |
| --GRANT CREATE SEQUENCE TO ORADMMG0; | |
| ALTER USER &username | |
| QUOTA unlimited ON users; | |
| / | |
| GRANT | |
| CREATE SESSION | |
| TO &username; | |
| / | |
| GRANT | |
| CREATE SESSION | |
| TO &username; | |
| / | |
| GRANT | |
| CREATE TABLE | |
| TO &username; | |
| / | |
| GRANT | |
| CREATE PROCEDURE | |
| TO &username; | |
| / | |
| GRANT | |
| CREATE TRIGGER | |
| TO &username; | |
| / | |
| GRANT | |
| CREATE VIEW | |
| TO &username; | |
| / | |
| GRANT | |
| CREATE SEQUENCE | |
| TO &username; | |
| / | |
| GRANT | |
| DROP PROFILE | |
| TO &username; | |
| / | |
| GRANT | |
| CREATE TYPE | |
| TO &username; | |
| GRANT EXECUTE ON dbms_aq TO &username; | |
| GRANT EXECUTE ON dbms_aqadm TO &username; | |
| / | |
| SPOOL OFF | |
| EXIT 0 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment