top of page
CerebroSQL

Oracle:

CREATE TABLESPACE

CREATE
[ BIGFILE | SMALLFILE ]
{ permanent_tablespace_clause
| temporary_tablespace_clause
| undo_tablespace_clause
} ;

permanent_tablespace_clause::=
TABLESPACE tablespace
[ DATAFILE file_specification [, file_specification ]... ]
{ MINIMUM EXTENT size_clause
| BLOCKSIZE integer [ K ]
| logging_clause
| FORCE LOGGING
| ENCRYPTION tablespace_encryption_spec
| DEFAULT [ table_compression ] [ storage_clause ]
| { ONLINE | OFFLINE }
| extent_management_clause
| segment_management_clause
| flashback_mode_clause
}...
logging_clause ::=
{ LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING }
tablespace_encryption_spec::=
[ USING 'encrypt_algorithm' ]
extent_management_clause ::=
EXTENT MANAGEMENT LOCAL
[ AUTOALLOCATE
| UNIFORM [ SIZE size_clause ]
]
segment_management_clause ::=
SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }
flashback_mode_clause ::=
FLASHBACK { ON | OFF }
temporary_tablespace_clause::=
TEMPORARY TABLESPACE tablespace
[ TEMPFILE file_specification [, file_specification ]... ]
[ tablespace_group_clause ]
[ extent_management_clause ]
tablespace_group_clause::=
TABLESPACE GROUP { tablespace_group_name | '' }
undo_tablespace_clause::=
UNDO TABLESPACE tablespace
[ DATAFILE file_specification [, file_specification ]... ]
[ extent_management_clause ]
[ tablespace_retention_clause ]
tablespace_retention_clause ::=
RETENTION { GUARANTEE | NOGUARANTEE }

example

CREATE BIGFILE TABLESPACE bigtbs_01
DATAFILE 'bigtbs_f1.dbf'
SIZE 20M AUTOEXTEND ON;

CREATE UNDO TABLESPACE undots1
DATAFILE 'undotbs_1a.dbf'
SIZE 10M AUTOEXTEND ON
RETENTION GUARANTEE;

CREATE TEMPORARY TABLESPACE temp_demo
TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON;

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '$ORACLE_HOME/rdbms/dbs';

CREATE TEMPORARY TABLESPACE tbs_05;

CREATE TEMPORARY TABLESPACE tbs_temp_02
TEMPFILE 'temp02.dbf' SIZE 5M AUTOEXTEND ON
TABLESPACE GROUP tbs_grp_01;

CREATE TABLESPACE tbs_01
DATAFILE 'tbs_f2.dbf' SIZE 40M
ONLINE;

CREATE TABLESPACE tbs_03
DATAFILE 'tbs_f03.dbf' SIZE 20M
LOGGING;

CREATE TABLESPACE tbs_02
DATAFILE 'diskb:tbs_f5.dbf' SIZE 500K REUSE
AUTOEXTEND ON NEXT 500K MAXSIZE 100M;

CREATE TABLESPACE tbs_04 DATAFILE 'file_1.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

CREATE TABLESPACE lmt1 DATAFILE 'lmt_file2.dbf' SIZE 100m REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

CREATE TABLE lmt_table1 (col1 NUMBER, col2 VARCHAR2(20))
TABLESPACE lmt1 STORAGE (INITIAL 2m);

CREATE TABLESPACE lmt2 DATAFILE 'lmt_file3.dbf' SIZE 100m REUSE
EXTENT MANAGEMENT LOCAL;

CREATE TABLE lmt_table2 (col1 NUMBER, col2 VARCHAR2(20))
TABLESPACE lmt2 STORAGE (INITIAL 2m MAXSIZE 100m);

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet_password";

CREATE TABLESPACE encrypt_ts
DATAFILE '$ORACLE_HOME/dbs/encrypt_df.dbf' SIZE 1M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT);

CREATE TABLESPACE auto_seg_ts DATAFILE 'file_2.dbf' SIZE 1M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '$ORACLE_HOME/rdbms/dbs';

CREATE TABLESPACE omf_ts1;

CREATE TABLESPACE omf_ts2 DATAFILE AUTOEXTEND OFF;

bottom of page