Collect common Oracle commands-tablespace Management

Common Oracle commands-tablespace Management

1. Create tablespaces
SQL> Create tablespace tablespace_name datafile 'C: \ oracle \ oradata \ file1.dbf 'size 100 m,
SQL> 'C: \ oracle \ oradata \ file2.dbf' size 100 m minimum extent 550 K [logging/nologging]
SQL> default storage (initial 500 K next 500 k maxextents 500 pctinccease 0)
SQL> [online/offline] [Permanent/Temporary] [extent_management_clause]

2. Locally managed tablespace
SQL> Create tablespace user_data datafile 'C: \ oracle \ oradata \ user_data01.dbf'
SQL> size 500 m extent management local uniform size 10 m;

3. Temporary tablespace
SQL> create temporary tablespace temp tempfile 'C: \ oracle \ oradata \ temp01.dbf'
SQL> size 500 m extent management local uniform size 10 m;

4. Change the storage setting
SQL> alter tablespace app_data minimum extent 2 m;
SQL> alter tablespace app_data default storage (initial 2 m next 2 m maxextents 999 );

5. Taking tablespace offline or online
SQL> alter tablespace app_data offline;
SQL> alter tablespace app_data online;

6. read_only tablespace
SQL> alter tablespace app_data read only | write;

7. droping tablespace
SQL> drop tablespace app_data including contents;

8. enableing automatic extension of data files
SQL> alter tablespace app_data add datafile 'C: \ oracle \ oradata \ app_data01.dbf' size 200 m
SQL> autoextend on next 10 m maxsize 500 m;

9. Change the size fo data files manually
SQL> alter database datafile 'C: \ oracle \ oradata \ app_data.dbf' resize 200 m;

10. Moving data files: Alter tablespace
SQL> alter tablespace app_data rename datafile 'C: \ oracle \ oradata \ app_data.dbf'
SQL> to 'C: \ oracle \ app_data.dbf ';

11. Moving data files: Alter Database
SQL> alter database rename file 'C: \ oracle \ oradata \ app_data.dbf'
SQL> to 'C: \ oracle \ app_data.dbf ';