Oracle Table Space

1. View all tablespaces: SELECT * from Dba_tablespaces;

2. View the default tablespace for a user: SELECT default_tablespace,username from Dba_users WHERE username= ' USERNAME ';

3. View the remaining capacity of the table space: SELECT tablespace_name,sum (BYTES) from Dba_free_space GROUP by Tablespace_name;

4. View information for tablespace data files: SELECT * from Dba_data_files;

5. Create TABLE space: Creation tablespace ODI datafile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ODI. DBF ' SIZE 50M autoextend on NEXT 10M PERMANENT EXTENT MANAGEMENT LOCAL;

Complete Build Table space statement: CREATE tablespace tablespace_name datafile ' filename ' size size [autoextend [on NEXT size | OFF]] [MAXSIZE size][permanent | Temporary][extent MANAGEMENT DICTIONARY | LOCAL];

6. Rename table space: ALTER tablespace oldname RENAME to NewName;

7. Set the read and write status of the tablespace: ALTER tablespace tablespace_name Read Only | WRITE;

8. Set the available status of the tablespace: ALTER tablespace tablespace_name ONLINE | OFFLINE [Noraml | Temporary | IMMEDIATE]; if it is online, then the tablespace can be manipulated by the user, and conversely set to offline, Tablespace is not unavailable, offline state also includes 3 ways.

9. Create large File Table space: Create Bigfile tablespace tablespace_name datafile ' filename ' size size;

10. Delete Table space: Drop tablespace tablespace_name [including Contents][cascade CONSTRAINTS]; including contents means that the data files in the tablespace are also deleted, and CASCADE constraints will delete the integrity of the table space.

11, temporary table space is generally refers to the data stored in the database, when the memory is not enough to write the space, this space is not like the general table space, when the operation of the database after the execution of the content of the space is automatically emptied.

12. Creating temporary tablespace: Create temporary tablespace tablespace_name tempfile ' filename ' size size;

13. Set temporary table space as default tablespace: ALTER DATABASE default temporary tablespace tablespace_name;

14. Query Temporary tablespace: SELECT * from Dba_tmp_files;

15. Creating a temporary tablespace group: Create temporary tablespace tablespace_name tmpfile ' filename ' size size tablespace group group_name;

16. Move the temporal table space to the table space group: ALTER tablespace tablespace_name tablespace Group group_name;

17, query temporary table space Group: SELECT * from Dba_tablespace_groups;

18. Delete temporary tablespace: Drop tablespace tablespace_name including CONTENTS and datafiles;

19. Delete data files in tablespace: ALTER tablespace tablespace_name DROP datafile ' filename ';

20. Add data file to table space: ALTER tablespace tablespace_name add datafile ' filename ' size size;

21. Add data files to the table space and allow data files to grow automatically: ALTER tablespace tablespace_name ADD datafile ' filename ' SIZE 50M autoextend on NEXT 5M MAXSIZE 100M;

22. Allow existing data files to grow automatically: ALTER DATABASE datafile ' filename ' autoextend on NEXT 5M MAXSIZE 100M;

23, manually change the size of the existing data file: Alter DATABASE datafile ' filename ' RESIZE 100M;

Oracle Table Space