ORACLE初始化表空间、用户模板

/*************用户初始化模板****************/

--数据文件信息查询
SELECT F.* FROM V$DBFILE F ORDER BY F.FILE# ASC

--创建表空间
CREATE TABLESPACE YTGZHDB DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\YTGZHTEST\YTGZHDB.DBF' SIZE 200M AUTOEXTEND ON NEXT 100M;
CREATE TABLESPACE YTGZSBDB DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\YTGZHTEST\YTGZSBDB.DBF' SIZE 200M AUTOEXTEND ON NEXT 100M;
CREATE TABLESPACE ANHUI_DIST_SEC DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\YTGZHTEST\ANHUI_DIST_SEC.DBF' SIZE 200M AUTOEXTEND ON NEXT 100M;

--创建 YTGZHDB 用户
CREATE USER YTGZHDB IDENTIFIED BY YTGZHDB#13 DEFAULT TABLESPACE YTGZHDB TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;
GRANT CONNECT, DBA, RESOURCE, UNLIMITED TABLESPACE TO YTGZHDB;
GRANT JAVAUSERPRIV TO YTGZHDB;
GRANT CREATE ANY INDEX TO YTGZHDB;
GRANT CREATE ANY SEQUENCE TO YTGZHDB;
GRANT CREATE ANY TABLE TO YTGZHDB;
GRANT CREATE ANY VIEW TO YTGZHDB;
GRANT SELECT ANY TABLE TO YTGZHDB;

GRANT SELECT ON SYS.DBA_PENDING_TRANSACTIONS TO YTGZHDB;
GRANT SELECT ON SYS.PENDING_TRANS$ TO YTGZHDB;
GRANT SELECT ON SYS.DBA_2PC_PENDING TO YTGZHDB;
GRANT EXECUTE ON SYS.DBMS_SYSTEM TO YTGZHDB;

GRANT ALL ON DBA_DB_LINKS TO YTGZHDB;
GRANT ALTER PUBLIC DATABASE LINK TO YTGZHDB;
GRANT CREATE PUBLIC DATABASE LINK TO YTGZHDB;
GRANT DROP PUBLIC DATABASE LINK TO YTGZHDB;

--创建 YTGZSBDB 用户
CREATE USER YTGZSBDB IDENTIFIED BY YTGZSBDB#13 DEFAULT TABLESPACE YTGZSBDB TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;
GRANT CONNECT, DBA, RESOURCE, UNLIMITED TABLESPACE TO YTGZSBDB;
GRANT JAVAUSERPRIV TO YTGZSBDB;
GRANT CREATE ANY INDEX TO YTGZSBDB;
GRANT CREATE ANY SEQUENCE TO YTGZSBDB;
GRANT CREATE ANY TABLE TO YTGZSBDB;
GRANT CREATE ANY VIEW TO YTGZSBDB;
GRANT SELECT ANY TABLE TO YTGZSBDB;

GRANT SELECT ON SYS.DBA_PENDING_TRANSACTIONS TO YTGZSBDB;
GRANT SELECT ON SYS.PENDING_TRANS$ TO YTGZSBDB;
GRANT SELECT ON SYS.DBA_2PC_PENDING TO YTGZSBDB;
GRANT EXECUTE ON SYS.DBMS_SYSTEM TO YTGZSBDB;

--创建 ANHUI_DIST_SEC 用户
CREATE USER ANHUI_DIST_SEC IDENTIFIED BY ANHUI_DIST_SEC#13 DEFAULT TABLESPACE ANHUI_DIST_SEC TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;
GRANT CONNECT, DBA, RESOURCE, UNLIMITED TABLESPACE TO ANHUI_DIST_SEC;
GRANT JAVAUSERPRIV TO ANHUI_DIST_SEC;
GRANT CREATE ANY INDEX TO ANHUI_DIST_SEC;
GRANT CREATE ANY SEQUENCE TO ANHUI_DIST_SEC;
GRANT CREATE ANY TABLE TO ANHUI_DIST_SEC;
GRANT CREATE ANY VIEW TO ANHUI_DIST_SEC;
GRANT SELECT ANY TABLE TO ANHUI_DIST_SEC;

--支持分布式事务
GRANT SELECT ON SYS.DBA_PENDING_TRANSACTIONS TO ANHUI_DIST_SEC;
GRANT SELECT ON SYS.PENDING_TRANS$ TO ANHUI_DIST_SEC;
GRANT SELECT ON SYS.DBA_2PC_PENDING TO ANHUI_DIST_SEC;
GRANT EXECUTE ON SYS.DBMS_SYSTEM TO ANHUI_DIST_SEC;