=========
How to create a tablespace.
========
CREATE [TEMPORARY / UNDO] TABLESPACE
DATAFILE / TEMPFILE ” SIZE [,
'' SIZE [,
'' SIZE [,...]]]
BLOCKSIZE
AUTOEXTEND { [OFF/ON (NEXT MAXSIZE) / UNLIMITED] }
LOGGING/NOLOGGING (Logging default)
ONLINE/OFFLINE (Online default)
EXTENT MANAGEMENT { [DICTIONARY] /
[LOCAL Default (AUTOALLOCATE / UNIFORM )] }
PERMANENT / TEMPORARY (Permanent default)
MINIMUM EXTENT
DEFAULT STORAGE { [INITIAL ]
[NEXT ]
[PCTINCREASE ]
[MINEXTENTS ]
[MAXEXTENTS / UNLIMITED]
[FREELISTS ]
[FREELIST GROUPS ]
[OPTIMAL /NULL]
[BUFFER_POOL ] }
CHUNK
NOCACHE;

BLOCKSIZE – By Default blocksize define in the parameter DB_BLOCK_SIZE. In Oracle9i, multiple blocksize that is different block size for different tablespaces, can be defined; all datafiles of a same tablespace have the same block size.
DEFAULT STORAGE :
INITIAL – To specifies the size of the object’s first extent.3 k minmum for Locally and 2 k minimum Dictionary.
NEXT – To specifies the size of the object’s sucessive extent.
PCTINCREASE – To specifies the ratio of the third or the preceding extent of the object. The default value for PCTINCREASE is 50 % and
the minimum value is 0%.
MINEXTENTS – Total number of extent allocated to the segment at the time of creation.
MAXEXTENTS – Maximum number of extent that can be allocated to the segment.
MININUM EXTENT – Extent are multiple of the size specified in this clause .NEXT and INITIAL extent size specified should be multiple of minmum extent.
PERMANENT / TEMPORARY – Permannent is default, use to store the table,index etc,Temporary is for temporay segments(sorts in Sql) can not store
table,index in temporary tablespace.
LOGGING / NOLOGGING – Logging is default. DDL operation & direct insert load are recorded in the redo log file.
ONLINE / OFFLINE – Online is default. Tablespace is available to use as soon as created.
===========
How to check the tablespace total size and usage.
===========
set pages 1000
set linesize 160
SELECT TabSpaceName,
SUM(TotBytes) “TOTAL MB”,
SUM(BytesUsed) “USED MB”,
SUM(BytesFree) “FREE MB”,
AVG(PCUSED) “% USED”,
AVG(PCFREE) “% FREE”
FROM (
SELECT SUBSTR (df.file_id, 1, 3) “ID#”,
df.tablespace_name TabSpaceName,
df.BYTES/1024/1024 TotBytes,
round (NVL(df.BYTES/1024/1024 – SUM (fs.BYTES/1024/1024),df.BYTES/1024/1024)) BytesUsed,
round (NVL(SUM (fs.BYTES/1024/1024),0)) BytesFree,
round (NVL((100 * ((SUM (fs.BYTES)) / df.BYTES)),0)) PCFREE,
round (NVL((100 * ((df.BYTES – SUM (fs.BYTES)) / df.BYTES)),100)) PCUSED
FROM SYS.dba_data_files df,
SYS.dba_free_space fs
WHERE df.file_id = fs.file_id (+)
GROUP BY df.tablespace_name, df.file_id,
df.tablespace_name,
df.BYTES,
df.blocks)
GROUP BY TabSpaceName
ORDER BY 6  ;

Tablespace Size (MB) Free (MB) % Free % Used
—————————— ———- ———- ———- ———-
Sanjay_DATA 5000 4989.75 100 0
Sanjay_INDEX 5000 4961.6875 99 1
USERS 100 97.4375 97 3
TEMP 20 15 75 25

==========
Oracle 11g.
==========
How to see the default permanent tablespace.
========
SELECT * FROM database_properties
WHERE property_name = ‘DEFAULT_PERMANENT_TABLESPACE’;

===========
How to check the tablespace extent and segment space management.
==========
Select tablespace_name ,extent_management ,segment_space_management
from dba_tablespaces;

============
How to change default permanent tablespace
============
SQL> alter database default tablespace sanjay;

==========
How to check default temporary tablespace.
=========
SELECT * FROM database_properties
WHERE property_name = ‘DEFAULT_TEMP_TABLESPACE’;
==========
How to change temporary tablespace.
==========
SQL> alter database default temporary tablespace temp;