Oracle view table space size and modify table space size

Oracle view table space size and modify table space size

Problem description:

1. Modify tablespace size

two。 View the tablespace size and the associated sql.

one。 Modify tablespace size

The solution to the above problem: it can be solved by increasing the table space, as follows:

Log in using dba users

Sqlplus / as sysdba

Execute the following command:

SQL > ALTER TABLESPACE SYSTEM ADD DATAFILE'/ u01 SIZE AUTOEXTEND OFF

Note: where'/ u01ax'is the path to your database. Here I set the size to: 20g.

two。 View tablespace size and related sql

While solving the above problems, through sorting out, the following sql, as a manager, should also be necessary.

1. View the name and size of the tablespace

SQl > SELECT t.tablespace_name, round (SUM (bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name

2. View the name and size of the tablespace physical file

SQl > SELECT tablespace_name, file_id, file_name, round (bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name

3. View the name and size of the rollback segment

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

4. View the control file

SQl > SELECT NAME FROM v$controlfile

5. View log files

SELECT MEMBER FROM v$logfile

6. Check the use of tablespaces

SQl > SELECT SUM (bytes) / (1024 * 1024) AS free_space, tablespace_name FROM dba_free_space GROUP BY tablespace_name

SQl > SELECT a.tablespace_name, a.bytes total, b.bytes used, c.bytes free, (b.bytes * 100) / a.bytes "% USED", (c.bytes * 100) / 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

7. View database objects

SQl > SELECT owner, object_type, status, COUNT (*) count# FROM all_objects GROUP BY owner, object_type, status

8. Check the database version

SQl > SELECT version FROM product_component_version WHERE substr (product, 1,6) = 'Oracle'

9. View the creation date and archiving method of the database

SQl > SELECT created, log_mode, log_mode FROM v$database

What to do when Oracle tablespaces are insufficient

1. Check which tablespace the table is in

Select tablespace_name,table_name from user_talbes where table_name='test'

-- 2. Get the user's default tablespace

Select username,DEFAULT_TABLESPACE from dba_users where username='MXH'

-- 3. View all files in the tablespace

Select * from dba_data_files where tablespace_name='USERS'

-- 4. Check the use of tablespaces:

SELECT tbs tablespace name

Sum (totalM) Total size M

Sum (usedM) has used space M

Sum (remainedM) residual space M

Sum (usedM) / sum (totalM) * 100% used

Sum (remainedM) / sum (totalM) * 100 remaining percentage

FROM (

SELECT b.file_id ID

B.tablespace_name tbs

B.file_name name

B.bytes/1024/1024 totalM

(b.bytes-sum (nvl (a.bytesre0) / 1024 take 1024 usedM

Sum (nvl (a.bytesjin0) / 1024Universe 1024) remainedM

Sum (nvl (a.bytesre0) / (b.bytes) * 100)

(100-(sum (nvl (a.bytesd0)) / (b.bytes) * 100))

FROM dba_free_space a,dba_data_files b

WHERE a.file_id = b.file_id

GROUP BY b.tablespace_name,b.file_name,b.file_id,b.bytes

ORDER BY b.tablespace_name

)

GROUP BY tbs

5. Expand the tablespace

Alterdatabase datafile'D:\ ORACLE\ PRODUCT\ ORADATA\ TEST\ USERS01.DBF' resize 50m

-- automatic growth

Alterdatabase datafile'D:\ ORACLE\ PRODUCT\ ORADATA\ TEST\ USERS01.DBF' autoextend onnext 50m maxsize 500m

-- add data files

Alter tablespace USERS add datafile'd:\ users02.dbf' size 5m