166 lines
3.3 KiB
SQL
166 lines
3.3 KiB
SQL
PROMPT create - alter Tablespaces
|
|
|
|
DEFINE OWNER = DWH
|
|
|
|
CREATE TABLESPACE dwhdata
|
|
DATAFILE '&data_path/dwhdata.dbf' size 10G
|
|
--DEFAULT STORAGE (INITIAL 2M NEXT 1M MINEXTENTS 1 MAXEXTENTS 999)
|
|
AUTOEXTEND ON
|
|
ONLINE
|
|
/
|
|
|
|
CREATE TABLESPACE dwhdata_gl
|
|
DATAFILE '&data_path/dwhdata_gl.dbf' size 10G
|
|
--DEFAULT STORAGE (INITIAL 2M NEXT 1M MINEXTENTS 1 MAXEXTENTS 999)
|
|
AUTOEXTEND ON
|
|
ONLINE
|
|
/
|
|
|
|
|
|
CREATE TABLESPACE dwhdata_kredit
|
|
DATAFILE '&data_path/dwhdata_kredit.dbf' size 10G
|
|
--DEFAULT STORAGE (INITIAL 2M NEXT 1M MINEXTENTS 1 MAXEXTENTS 999)
|
|
AUTOEXTEND ON
|
|
ONLINE
|
|
/
|
|
|
|
|
|
CREATE TABLESPACE dwhindex
|
|
DATAFILE '&data_path/dwhindex.dbf' size 5G
|
|
--DEFAULT STORAGE (INITIAL 2M NEXT 1M MINEXTENTS 1 MAXEXTENTS 999)
|
|
AUTOEXTEND ON
|
|
ONLINE
|
|
/
|
|
|
|
CREATE TABLESPACE dwhdimension
|
|
DATAFILE '&data_path/dwhdimension.dbf' size 1G
|
|
--DEFAULT STORAGE (INITIAL 2M NEXT 1M MINEXTENTS 1 MAXEXTENTS 999)
|
|
AUTOEXTEND ON
|
|
ONLINE
|
|
/
|
|
|
|
|
|
PROMPT create Application Owners Users
|
|
|
|
CREATE USER &OWNER
|
|
IDENTIFIED BY bagbag
|
|
DEFAULT TABLESPACE dwhdata
|
|
TEMPORARY TABLESPACE temp
|
|
QUOTA UNLIMITED ON dwhdata
|
|
QUOTA UNLIMITED ON dwhdata_gl
|
|
QUOTA UNLIMITED ON dwhdata_kredit
|
|
QUOTA UNLIMITED ON dwhindex
|
|
QUOTA UNLIMITED ON dwhdimension
|
|
/
|
|
|
|
PROMPT Grant/Revoke role privileges
|
|
|
|
GRANT CONNECT TO &OWNER
|
|
/
|
|
GRANT RESOURCE TO &OWNER
|
|
/
|
|
|
|
PROMPT Oracle install privileges
|
|
|
|
GRANT CREATE TABLE TO &OWNER
|
|
/
|
|
GRANT CREATE VIEW TO &OWNER
|
|
/
|
|
GRANT CREATE SEQUENCE TO &OWNER
|
|
/
|
|
GRANT CREATE PROCEDURE TO &OWNER
|
|
/
|
|
GRANT CREATE TRIGGER TO &OWNER
|
|
/
|
|
GRANT CREATE ROLE TO &OWNER
|
|
/
|
|
GRANT GRANT ANY ROLE TO &OWNER
|
|
/
|
|
GRANT DROP ANY ROLE TO &OWNER
|
|
/
|
|
GRANT ALTER SYSTEM TO &OWNER
|
|
/
|
|
GRANT CREATE ANY VIEW TO &OWNER
|
|
/
|
|
GRANT CREATE DATABASE LINK TO &OWNER
|
|
/
|
|
GRANT ALTER PROFILE TO &OWNER
|
|
/
|
|
GRANT CREATE PROFILE TO &OWNER
|
|
/
|
|
GRANT DROP PROFILE TO &OWNER
|
|
/
|
|
GRANT CREATE CLUSTER TO &OWNER
|
|
/
|
|
GRANT CREATE PUBLIC SYNONYM TO &OWNER
|
|
/
|
|
GRANT DROP PUBLIC SYNONYM TO &OWNER
|
|
/
|
|
GRANT CREATE JOB TO &OWNER
|
|
/
|
|
GRANT UNLIMITED TABLESPACE TO &OWNER
|
|
/
|
|
|
|
PROMPT Oracle session privileges
|
|
|
|
GRANT CREATE SESSION TO &OWNER WITH ADMIN OPTION
|
|
/
|
|
GRANT ALTER SESSION TO &OWNER WITH ADMIN OPTION
|
|
/
|
|
|
|
PROMPT Oracle object privileges
|
|
|
|
GRANT SELECT ON DBA_ROLES TO &OWNER WITH GRANT OPTION
|
|
/
|
|
GRANT SELECT ON DBA_ROLE_PRIVS TO &OWNER
|
|
/
|
|
GRANT SELECT,INSERT,UPDATE,DELETE ON SOURCE$ TO &OWNER
|
|
/
|
|
GRANT EXECUTE ON DBMS_PIPE TO &OWNER
|
|
/
|
|
GRANT EXECUTE ON DBMS_LOCK TO &OWNER
|
|
/
|
|
GRANT SELECT ON DBA_JOBS TO &OWNER WITH GRANT OPTION
|
|
/
|
|
GRANT EXECUTE ON DBMS_IJOB TO &OWNER
|
|
/
|
|
GRANT SELECT ON V_$SESSION TO &OWNER WITH GRANT OPTION
|
|
/
|
|
GRANT SELECT ON V_$ACCESS TO &OWNER
|
|
/
|
|
GRANT SELECT ON V_$DATABASE TO &OWNER
|
|
/
|
|
GRANT SELECT ON V_$OPTION TO &OWNER
|
|
/
|
|
GRANT SELECT ON V_$PROCESS TO &OWNER WITH GRANT OPTION
|
|
/
|
|
GRANT SELECT ON V_$PARAMETER TO &OWNER WITH GRANT OPTION
|
|
/
|
|
GRANT SELECT ON ARGUMENT$ TO &OWNER WITH GRANT OPTION
|
|
/
|
|
GRANT SELECT ON USER$ TO &OWNER
|
|
/
|
|
GRANT EXECUTE ON DBMS_ALERT TO &OWNER
|
|
/
|
|
GRANT SELECT ON DBA_TAB_PRIVS TO &OWNER WITH GRANT OPTION
|
|
/
|
|
GRANT SELECT ON COM$ TO &OWNER WITH GRANT OPTION
|
|
/
|
|
GRANT SELECT ON OBJ$ TO &OWNER WITH GRANT OPTION
|
|
/
|
|
GRANT SELECT ON DBA_PROFILES TO &OWNER WITH GRANT OPTION
|
|
/
|
|
GRANT SELECT ON DBA_USERS TO &OWNER
|
|
/
|
|
GRANT SELECT ON DBA_TABLESPACES TO &OWNER
|
|
/
|
|
|
|
PROMPT Grant/Revoke directories access
|
|
|
|
GRANT READ ON DIRECTORY data_pump_dir TO &OWNER
|
|
/
|
|
GRANT WRITE ON DIRECTORY data_pump_dir TO &OWNER
|
|
/
|
|
|
|
|