Oracle/Create tablespace

Basic

Create a new tablespace:

CREATE TABLESPACE TEST LOGGING 
  DATAFILE '/usr/lib/oracle/xe/oradata/XE/test.dbf' 
  SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED 
  EXTENT MANAGEMENT LOCAL;

Create a new temporary tablespace:

CREATE TEMPORARY TABLESPACE TEST_TEMP
  TEMPFILE '/usr/lib/oracle/xe/oradata/XE/test_temp.dbf'
  SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED 
  EXTENT MANAGEMENT LOCAL;

Extended

CREATE TABLESPACE TEST
  DATAFILE '/oracle/app/oradata/devdb/TEST_0.dbf' SIZE 31G AUTOEXTEND ON NEXT 100M MAXSIZE 32767m,
           '/oracle/app/oradata/devdb/TEST_1.dbf' SIZE 31G AUTOEXTEND ON NEXT 100M MAXSIZE 32767m,
           '/oracle/app/oradata/devdb/TEST_2.dbf' SIZE 31G AUTOEXTEND ON NEXT 100M MAXSIZE 32767m,
           '/oracle/app/oradata/devdb/TEST_3.dbf' SIZE  1G AUTOEXTEND ON NEXT 100M MAXSIZE 32767m,
           '/oracle/app/oradata/devdb/TEST_4.dbf' SIZE  1G AUTOEXTEND ON NEXT 100M MAXSIZE 32767m,
           '/oracle/app/oradata/devdb/TEST_5.dbf' SIZE  1G AUTOEXTEND ON NEXT 100M MAXSIZE 32767m
  NOLOGGING;
-- import, then change to LOGGING
ALTER TABLESPACE TEST LOGGING;

CREATE USER "TEST_A" PROFILE "DEFAULT" IDENTIFIED BY "seeburger" DEFAULT TABLESPACE "TEST_A" ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE, CREATE VIEW, CONNECT, RESOURCE TO "TEST_A";
CREATE USER "TEST_B" PROFILE "DEFAULT" IDENTIFIED BY "seeburger" DEFAULT TABLESPACE "TEST_B" ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE, CREATE VIEW, CONNECT, RESOURCE TO "TEST_B";

User

Create a new user for this table space with appropriate rights (important: case must be the same for both, user name and table space!):

CREATE USER "TEST" PROFILE "DEFAULT" 
  IDENTIFIED BY "PASSWORD"
  DEFAULT TABLESPACE "TEST"
  TEMPORARY TABLESPACE "TEST_TEMP"
  ACCOUNT UNLOCK;

-- ROLE RESOURCE: CREATE CLUSTER, INDEXTYPE, OPERATOR, PROCEDURE, SEQUENCE, TABLE, TRIGGER, TYPE
-- ROLE CONNECT: CREATE SESSION
GRANT CONNECT, RESOURCE TO "TEST"; 
-- CREATE VIEW
GRANT CREATE VIEW TO "TEST";
GRANT UNLIMITED TABLESPACE TO "TEST";

Revoke rights again:

REVOKE UNLIMITED TABLESPACE FROM "TEST";

Or set default tablespace + temporary tablespace of an existing user:

ALTER USER "TEST"
  DEFAULT TABLESPACE "TEST"
  TEMPORARY TABLESPACE "TEST_TEMP";

Change user password:

ALTER USER "TEST" IDENTIFIED BY "NEW_PASSWORD";

Application Specific

JBoss 7

GRANT SELECT ON sys.dba_pending_transactions TO TEST; GRANT SELECT ON sys.pending_trans$ TO TEST; GRANT SELECT ON sys.dba_2pc_pending TO TEST; GRANT EXECUTE ON sys.dbms_xa TO TEST;

Drop

SELECT * FROM USER_TABLESPACES;
SELECT * FROM DBA_DIRECTORIES;
DROP TABLESPACE "TEST" INCLUDING CONTENTS AND DATAFILES;