oracle--viewing tablespace size and modifying table space size

I. Modifying table space size

Solution to the above problem: by increasing the table space can be resolved as follows:

SQL code

2. log in with a DBA user
4. Sqlplus/ as SYSDBA;
6.   
8. execute the following command:
10. SQL > ALTER tablespace SYSTEM ADD datafile '/u01/app/oracle/oradata/test/system02.dbf '   SIZE 20480M autoextend OFF;
12.   
14. Note: where '/u01/app/oracle/oradata/test/system02.dbf ' is the path to your database.  Here I set the size to: 20G.

two. View the table space size and related SQL

In solving the above problems encountered at the same time, through the collation, the following SQL, as a management staff, should also be necessary.

SQL code

2. 1. View the name and size of the table space
4. SQl > SELECT t.tablespace_name, round (SUM(Bytes/(1024x768)), 0) ts_size from Dba_ta Blespaces T, dba_data_files D WHERE t.tablespace_name = d.tablespace_name GROUP by T.tablespa   Ce_name;
6.   
8. 2. View the table space physical file name and size
10. SQl > SELECT tablespace_name, file_id, file_name, round (Bytes/(1024x768), 0) total_space from Dba_data_files ORDER by Tablespace_name;
12.   
14. 3. Check the rollback segment name and size
16. SQl > SELECT segment_name, Tablespace_name, R.status, (initial_extent/1024) initialextent, (next_ extent/1024) nextextent, max_extents, V.curext curextent from Dba_rollback_segs R, V$rollstat v WHERE   r.segment_id = V.usn (+) ORDER by Segment_name;
18.   
20. 4. View Control Files
22. SQl > SELECT NAME from V$controlfile;
24.   
26. 5. Viewing log files
28. SELECT MEMBER from V$logfile;
30.   
32. 6. View the usage of table space
34. SQl > SELECT SUM(bytes)/(1024x768 * 1024x768) as Free_space, Tablespace_name from DB   A_free_space GROUP by Tablespace_name;
36.   
38. SQl > SELECT a.tablespace_name, a.bytes total, b.bytes used, c.bytes free, (b.bytes * +)/A.byt Es "% used", (c.bytes * +)/a.bytes "% free" from Sys.sm$ts_avail A, sys.sm$ts_used B, sys   . sm$ts_free C WHERE a.tablespace_name = b.tablespace_name and a.tablespace_name = C.tablespace_name;
40.   
42. 7. View Database Library Objects
44. SQl > SELECT owner, object_type, status, Count(*) Count# from All_objects GROUP    by Owner, object_type, status;
46.   
48. 8. View the version of the database
50. SQl > SELECT version from product_component_version WHERE substr (product, 1, 6) = '   Oracle ';
52.   
54. 9. View the date the database was created and how it was archived
56. SQl > SELECT created, Log_mode, Log_mode from v$database;

oracle--viewing tablespace size and modifying table space size