Oracle Add Temp Tablespace and Temp User

Oracle Add TableSpace

PROMPT '--------------------------------------';

PROMPT 'Start CREATE TEST_TABLESPACE';

CREATE SMALLFILE TABLESPACE "TEST_TABLESPACE" DATAFILE 'testdatafile01.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
 ,'testdatafile02.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
PROMPT 'Start CREATE TESTUSER';

DROP USER TESTUSER CASCADE;

CREATE USER TESTUSER PROFILE "DEFAULT" IDENTIFIED BY "Tin.netS1" DEFAULT TABLESPACE "TEST_TABLESPACE" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;

GRANT "CONNECT"
 TO TESTUSER;

GRANT "RESOURCE"
 TO TESTUSER;

GRANT CREATE ANY DIRECTORY
 TO TESTUSER;

PROMPT '--------------------------------------';

quit;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

PROMPT '--------------------------------------';

PROMPT 'Start CREATE DITTO_TABLESPACE';

DROP TABLESPACE "DITTO_TABLESPACE" INCLUDING CONTENTS CASCADE CONSTRAINTS;

CREATE SMALLFILE  TABLESPACE "DITTO_TABLESPACE" DATAFILE
'dittodatafile01.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile02.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile03.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile04.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile05.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile06.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile07.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile08.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile09.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile10.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile11.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile12.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile13.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile14.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile15.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
, 'dittodatafile16.dbf' SIZE 5000 M REUSE AUTOEXTEND ON NEXT 100 M MAXSIZE UNLIMITED
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

DROP USER DITTOUSER CASCADE;

CREATE USER DITTOUSER PROFILE "DEFAULT" IDENTIFIED BY "Tin.netS1" DEFAULT TABLESPACE "DITTO_TABLESPACE" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;

GRANT "CONNECT"
 TO DITTOUSER;

GRANT "RESOURCE"
 TO DITTOUSER;

GRANT CREATE ANY DIRECTORY
 TO DITTOUSER;

PROMPT '--------------------------------------';

quit;