Create Table Space

CREATE TABLESPACE Create a tablespace. Syntax: CREATE [UNDO] TABLESPACE tablespace_name DATAFILE Datafile_Options Storage_Options ; Datafile_Options: 'filespec' [AUTOEXTEND OFF] 'filespec' [AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]] The Autoextend Maxsize clause will default to UNLIMITED if no value is specified. Storage_Options: DEFAULT [COMPRESS|NOCOMPRESS] STORAGE storage_clause MINIMUM EXTENT int {K|M} BLOCKSIZE int K LOGGING | NOLOGGING FORCE LOGGING ONLINE | OFFLINE PERMANENT | TEMPORARY EXTENT MANAGEMENT {DICTIONARY | LOCAL {AUTOALLOCATE | UNIFORM [SIZE int K | M]} } SEGMENT SPACE MANAGEMENT {MANUAL | AUTO} Related Commands: ALTER TABLESPACE DROP TABLESPACE Related Views: DBA_DATA_FILES DBA_TABLESPACES USER_TABLESPACES DBA_TEMP_FILES DBA_TS_QUOTAS USER_TS_QUOTAS Create tablespace in Oracle The create tablespace statement is used to create a tablespace. Permanent tablespace CREATE TEMPORARY TABLESPACE Create a temporary tablespace. Syntax: CREATE TEMPORARY TABLESPACE tablespace_name TEMPFILE Tempfile_Options [EXTENT MANAGEMENT LOCAL] [UNIFORM [SIZE int K | M] ]; Tempfile_Options: 'filespec' [AUTOEXTEND OFF] 'filespec' [AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]] The Autoextend Maxsize clause will default to UNLIMITED if no value is specified. To create a locally managed tablespace specify 'EXTENT MANAGEMENT LOCAL'. All extents of temporary tablespaces are the same size - if UNIFORM is not defined it will default to 1 MB. DROP TABLESPACE Create a tablespace. Syntax: DROP TABLESPACE tablespace_name [INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]]; create tablespace ts_something logging datafile '/dbf1/ts_sth.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; create tablespace data datafile '/home/oracle/databases/ora10/data.dbf' size 10M autoextend on maxsize 200M extent management local uniform size 64K; Temporary tablespace create temporary tablespace temp_mtr

tempfile '/dbf1/mtr_temp01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; Note, a temporary tablespace has tempfiles, not datafiles. Undo tablespace create undo tablespace ts_undo datafile '/dbf/undo.dbf' size 100M; Misc More than one datafile can be created with a single create tablespace command: create tablespace ts_sth datafile 'c:\xx\sth_01.dbf' size 4M autoextend off, 'c:\xx\sth_02.dbf' size 4M autoextend off, 'c:\xx\sth_03.dbf' size 4M autoextend off logging extent management local; Adding a tablespace to a database is a structural change that requires a backup. ALTER TABLESPACE Change the properties of a tablespace. Syntax: ALTER TABLESPACE tablespace_name option options: The option used with this command can be any one of the following ADD {TEMPFILE|DATAFILE} 'filespec' [AUTOEXTEND OFF] SIZE int {K|M} ADD {TEMPFILE|DATAFILE} 'filespec' SIZE int {K|M} [ AUTOEXTEND ON [NEXT int K | M] [MAXSIZE {UNLIMITED|int K|int M}] ] RENAME DATAFILE 'filename' TO 'filename' {TEMPFILE|DATAFILE} ONLINE {TEMPFILE|DATAFILE} OFFLINE MINIMUM EXTENT int {K|M} COALESCE DEFAULT STORAGE storage_clause ONLINE OFFLINE {NORMAL | TEMPORARY | IMMEDIATE} {BEGIN | END} BACKUP READ {ONLY | WRITE} PERMANENT | TEMPORARY LOGGING | NOLOGGING [NO] FORCE LOGGING Crear un usuario create user prueba0001 identified by prueba0001; Asignar a un usuario los permisos connect y resource grant connect, resource to scott; Cambio de password alter user prueba0001 identified by prueba0001; Crear un tablespace create tablespace TSprueba0007 datafile 'DFprueba0007_1.dat' size 60K; Asignar un tablespace a un usuario alter user prueba0001 default tablespace TSprueba0001 temporary tablespace TSprueba0001; Borrar un tablespace drop tablespace TSprueba0001

including contents cascade constraints; Crear un tablespace especificando las propiedades de almacenamiento create tablespace TSprueba0001 datafile 'c:\orant\database\DFprueba0001.dat' size 50K default storage ( initial 10K next 10K minextents 8 maxextents 200 pctincrease 0); Crear un tablespace de varios datafiles create tablespace TSprueba0001 datafile 'c:\orant\database\DFprueba0001.dat' size 50 M autoextend off, 'c:\orant\database\DFprueba0001.dat' size 50 M autoextend off, 'c:\orant\database\DFprueba0001.dat' size 100 M autoextend on maxsize 200 M default storage ( initial 100 K next 100K minextents 1 maxextents 200 pctincrease 0); Modificar las propiedades de almacenamiento de un tablespace alter tablespace TSprueba0001 default storage ( initial 10K next 10K minextents 1 maxextents 500 pctincrease 0); Añadir otro datafile al tablespace alter tablespace TSprueba0001 add datafile 'c:\orant\database\DFprueba0002.dat' size 10 k autoextend on next 10 k maxsize 100 k; Modificar el tamaño de un datafile alter database datafile 'prueba0001.dat' resize 1 M; Eliminar del diccionario un tablespace con datafiles borrados manualmente estando la BD parada shutdown abort startup shutdown abort startup mount alter database datafile 'c:\mi_carpeta\mi_datafile.dat' offline drop; shutdown startup Unir huecos contiguos en los datafiles de un tablespace alter tablespace TSprueba1 coalesce; Crear extensiones de un segmento Se crean automáticamente cuando se necesitan alter table allocate extent... Eliminar extensiones de un segmento alter table deallocate unused...

create rollback segment ... optimal