CREATE:
CREATE SMALLFILE TABLESPACE "TEST_NAHAR"
DATAFILE '/oracle/oradata/TEST_NAHAR_01.dbf'
SIZE 10M
REUSE LOGGING
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1024K
SEGMENT SPACE MANAGEMENT AUTO;
Syntax-2:
DROP TABLESPACE "DATA02" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
CREATE TABLESPACE "TS_DATA02"
DATAFILE '/oradata/datafiles/THRP/DF_DATA02.dbf'
SIZE 1G
AUTOEXTEND ON
NEXT 50M
MAXSIZE 30G
BLOCKSIZE 8K;
Syntax-3:
CREATE TABLESPACE "DATA02"
DATAFILE '/oracle/oradata/JUPITER/DATA02_01.dbf'
SIZE 100M
BLOCKSIZE 8K;
DROP:
DROP TABLESPACE TEST_NAHAR
INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
ALTER:
SQL> ALTER TABLESPACE users OFFLINE;
SQL> ALTER TABLESPACE users ONLINE;
DBA_TABLESPACES Details:
If I set REUSE LOGGING, then LOGGING=LOGGING will set.
If I set BLOCKSIZE 8K, then BLOCKSIZE=8192 will set.
If I set BLOCKSIZE 16K, then BLOCKSIZE=16384 will set.
Default Settings:
LOGGING=LOGGING
EXTENT MANAGEMENT = LOCAL
SEGMENT_SPACE_MANAGEMENT=AUTO
DBA_DATA_FILES Details:
BYTES=10485760 =>SIZE 10M (10*1024*1024)
Q: Which Tablespace contains which datafile?
select t.tablespace_name, d.file_name, t.segment_space_management, t.block_size, t.bigfile
from DBA_TABLESPACES t,DBA_DATA_FILES d
where t.tablespace_name=d.tablespace_name
order by t.tablespace_name;
Or simply,
select d.tablespace_name,d.file_name
from DBA_DATA_FILES d order by d.tablespace_name;
Q: How to Check OBJECT on Tablespace?
select owner, segment_name, segment_type, tablespace_name
from dba_segments
where lower(segment_name) like lower('%SYS_LOB0000151343C00022%')
order by owner, segment_name;
Q: How to Check all OBJECTs of a Tablespace?
select owner, segment_name, segment_type, tablespace_name
from dba_segments
where lower(tablespace_name) like lower('%DATA02%')
order by owner, segment_name;
