Oracle tablespaces and datafiles

• create tablespaces

sql> create tablespace tablespace_name datafile '\oracle\oradata\file1.dbf' size 100m,
sql> '/oracle/oradata/file2.dbf' size 100m minimum extent 550k [logging/nologging]
sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0)
sql> [online/offline] [permanent/temporary] [extent_management_clause]


• locally managed tablespace

sql> create tablespace user_data datafile '/oracle/oradata/user_data01.dbf'
sql> size 500m extent management local uniform size 10m;


• temporary tablespace

sql> create temporary tablespace temp tempfile '/oracle/oradata/temp01.dbf'
sql> size 500m extent management local uniform size 10m;


• change the storage setting

sql> alter tablespace app_data minimum extent 2m;
sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999);


• taking tablespace offline or online

sql> alter tablespace app_data offline;
sql> alter tablespace app_data online;


• read_only tablespace

sql> alter tablespace app_data read onlywrite;


• droping tablespace

sql> drop tablespace app_data including contents;


• enableing automatic extension of data files
  sql> alter tablespace app_data add datafile '/oracle/oradata/app_data01.dbf'size 200m autoextend on next 10m maxsize 500m;
  
  
• change the size of data files manually

sql> alter database datafile '/oracle/oradata/app_data.dbf'resize 200m;


• Moving data files: alter tablespace

sql> alter tablespace app_data rename datafile '/oracle/oradata/app_data.dbf'
sql> to '/oracle/app_data.dbf';


• moving data files:alter database

sql> alter database rename file '/oracle/oradata/app_data.dbf'
sql> to '/oracle/app_data.dbf';