Скачать или смотреть Oracle Table Space and User Creation with grant Privileges

-------------Table space Creation-------------------
Syntax

CREATE TABLESPACE 'DATA FILE NAME' DATAFILE
'Data File Path to store the data' SIZE 'SIZE in MB' AUTOEXTEND ON NEXT 'SIZE in KB/MB' MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 'SIZE in KB'
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE TABLESPACE ORA12CR2_DATA DATAFILE
'E:\OracleDB\app\ORA12CR2_DATA.dbf' SIZE 2048M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE TABLESPACE ORA12CR2_INDEX DATAFILE
'E:\OracleDB\app\ORA12CR2_INDEX.dbf' SIZE 1024M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Increasing the Tablespace space:

ALTER DATABASE DATAFILE 'E:\OracleDB\app\ORA12CR2_DATA.dbf' RESIZE 5120M;
ALTER DATABASE DATAFILE 'E:\OracleDB\app\ORA12CR2_DATA.dbf' AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED;
ALTER DATABASE DATAFILE 'E:\OracleDB\app\ORA12CR2_INDEX.dbf' RESIZE 2048M;
ALTER DATABASE DATAFILE 'E:\OracleDB\app\ORA12CR2_INDEX.dbf' AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED;

------------User Creation and Grant Privileges
Syntax:-

CREATE USER 'USER NAME'
IDENTIFIED BY 'Password'
DEFAULT TABLESPACE 'Table Space Name'
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

Example:-

CREATE USER DEMOUSER
IDENTIFIED BY DEMOUSER
DEFAULT TABLESPACE ORA12CR2_DATA
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

alter profile DEFAULT limit PASSWORD_REUSE_TIME unlimited;
alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited;

GRANT DBA TO DEMOUSER;
ALTER USER DEMOUSER DEFAULT ROLE ALL;
-- 16 System Privileges for DEMOUSER
GRANT CREATE ROLE TO DEMOUSER;
GRANT CREATE TYPE TO DEMOUSER;
GRANT CREATE VIEW TO DEMOUSER;
GRANT CREATE TABLE TO DEMOUSER;
GRANT ALTER SESSION TO DEMOUSER;
GRANT CREATE SESSION TO DEMOUSER;
GRANT CREATE SYNONYM TO DEMOUSER;
GRANT CREATE TRIGGER TO DEMOUSER;
GRANT CREATE ANY TYPE TO DEMOUSER;
GRANT CREATE SEQUENCE TO DEMOUSER;
GRANT CREATE ANY INDEX TO DEMOUSER;
GRANT CREATE PROCEDURE TO DEMOUSER;
GRANT CREATE ANY TRIGGER TO DEMOUSER;
GRANT CREATE DATABASE LINK TO DEMOUSER;
GRANT UNLIMITED TABLESPACE TO DEMOUSER;
GRANT SELECT ANY DICTIONARY TO DEMOUSER;
GRANT DEBUG CONNECT SESSION TO DEMOUSER;
GRANT DEBUG ANY PROCEDURE TO DEMOUSER;
GRANT CREATE ANY MATERIALIZED VIEW TO DEMOUSER;
GRANT GLOBAL QUERY REWRITE TO DEMOUSER;
-- 2 Tablespace Quotas for DEMOUSER
ALTER USER DEMOUSER QUOTA UNLIMITED ON ORA12CR2_DATA;
ALTER USER DEMOUSER QUOTA UNLIMITED ON ORA12CR2_INDEX;
-- 1 Object Privilege for DEMOUSER

CREATE OR REPLACE DIRECTORY DB_EXP_IMP AS 'E:\OracleDB\app';
GRANT READ, WRITE ON DIRECTORY DB_EXP_IMP TO DEMOUSER;