Oracle创建表空间以及创建用户

1、创建表空间

 

LINUX
CREATE SMALLFILE TABLESPACE "DFTSINDEX" DATAFILE '/opt/oracle/oradata/XXX/DFTSINDEX.dbf' SIZE 800M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "DFTSINTERFACE" DATAFILE '/opt/oracle/oradata/XXX/DFTSINTERFACE.dbf' SIZE 100M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "DFTSOPERATION" DATAFILE '/opt/oracle/oradata/XXX/DFTSOPERATION.dbf' SIZE 1G AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "DFTSSYSTEM" DATAFILE '/opt/oracle/oradata/XXX/DFTSSYSTEM.dbf' SIZE 800M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "DFTSTEM" DATAFILE '/opt/oracle/oradata/XXX/DFTSTEM.dbf' SIZE 500M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

WINDOWS
CREATE SMALLFILE TABLESPACE "DFTSINDEX" DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\XXX\DFTSINDEX.dbf' SIZE 1G AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "DFTSINTERFACE" DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\XXX\DFTSINTERFACE.dbf' SIZE 500M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "DFTSOPERATION" DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\XXX\DFTSOPERATION.dbf' SIZE 3G AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "DFTSSYSTEM" DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\XXX\DFTSSYSTEM.dbf' SIZE 1G AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "DFTSTEM" DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\XXX\DFTSTEM.dbf' SIZE 800M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

 

2、创建用户

 

---创建用户
CREATE USER "DFO" PROFILE "DEFAULT" IDENTIFIED BY "WELLCOM" DEFAULT TABLESPACE "DFTSSYSTEM" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "DFO";
CREATE USER "WELDFO" PROFILE "DEFAULT" IDENTIFIED BY "WELLCOM" DEFAULT TABLESPACE "DFTSOPERATION" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "WELDFO";
CREATE USER "DFP" PROFILE "DEFAULT" IDENTIFIED BY "WELLCOM" DEFAULT TABLESPACE "DFTSTEM" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "DFP";
---为用户分配表空间容量的使用限额
ALTER USER "DFO" QUOTA UNLIMITED ON "DFTSINDEX";
ALTER USER "DFO" QUOTA UNLIMITED ON "DFTSINTERFACE";
ALTER USER "DFO" QUOTA UNLIMITED ON "DFTSOPERATION";
ALTER USER "DFO" QUOTA UNLIMITED ON "DFTSSYSTEM";
ALTER USER "DFO" QUOTA UNLIMITED ON "DFTSTEM";
ALTER USER "WELDFO" QUOTA UNLIMITED ON "DFTSINDEX";
ALTER USER "WELDFO" QUOTA UNLIMITED ON "DFTSINTERFACE";
ALTER USER "WELDFO" QUOTA UNLIMITED ON "DFTSOPERATION";
ALTER USER "WELDFO" QUOTA UNLIMITED ON "DFTSSYSTEM";
---回收权限
--REVOKE UNLIMITED TABLESPACE FROM "DFO"
--REVOKE "RESOURCE" FROM "DFO"
---赋基本权限
GRANT CREATE JOB TO "DFO";
GRANT CREATE VIEW TO "DFO";
GRANT CREATE TABLE TO "DFO";
GRANT CREATE PROCEDURE TO "DFO";
GRANT CREATE TRIGGER TO "DFO";
GRANT CREATE SEQUENCE TO "DFO";
GRANT DEBUG CONNECT SESSION TO "DFO";
GRANT EXPORT FULL DATABASE TO "DFO";
GRANT IMPORT FULL DATABASE TO "DFO";
--ALTER USER "DFO" DEFAULT ROLE ALL

3、回收权限

 

REVOKE execute on utl_file from PUBLIC;
REVOKE execute on DBMS_RANDOM FROM PUBLIC;
REVOKE execute on UTL_HTTP FROM PUBLIC;
REVOKE execute on UTL_SMTP FROM PUBLIC;
REVOKE execute on UTL_TCP FROM PUBLIC;