Shulou(Shulou.com)06/01 Report--

Shrink space method:

one。 Do not delete data, shrink the data file size of the tablespace, and free up disk space

Query the tablespace where the corresponding table is located

Select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES/1024/1024 from dba_segments where segment_name=upper

('tasking optosyncratic jobless processing log')

View the data file size corresponding to the tablespace

Select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024 mb,BLOCKS from dba_data_files where

TABLESPACE_NAME='TFR_DATA'

View the data in the data file in the largest location

Select max (block_id) from dba_extents where file_id=9

Calculate how much space a tablespace actually needs

Select 1354880mm 8amp 1024 from dual

Set the tablespace size to this value

ALTER DATABASE DATAFILE'/ u01 RESIZE oradataUniverse FOSSDB qqdatafile.o1mfqnxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

two。 Delete data shrinkage on the premise that the shrinkage capacity of the above methods is limited.

There are two ways to delete data:

(1) Delete all table data and release space immediately

Truncate table tfr.t_opt_job_process_log

(2) delete part of the data and shrink the tablespace

Delete from tfr.t_opt_job_process_log where...

1. Moving the table to a table space with enough space will immediately release the deleted data space.

Alter table tfr.t_opt_job_process_log move tablespace PKP_DATA

Just move the watch back (or not).

Alter table tfr.t_opt_job_process_log move tablespace TFR_DATA

two。 Or use the following command to manually free up space

Alter table tfr.t_opt_job_process_log enable row movement

Alter table tfr.t_opt_job_process_log shrink space

View the size of the table in the tablespace

Select segment_name

Tablespace_name

Bytes B

Bytes / 1024 KB

Bytes / 1024 / 1024 MB from dba_segments where segment_type = 'TABLE'

And tablespace_name = 'USERS' order by bytes desc

See which tables are in the tablespace

Select Table_Name, Tablespace_Name

From Dba_Tables

Where Tablespace_Name = 'TFR_DATA'

Select *

From Dba_Tables

Where Tablespace_Name = 'USERS'; so you can see which user the table belongs to

View tablespaces-you can view tablespace files

Select b.file_name physical file name

B.tablespace_name tablespace

B.bytes/1024/1024 size M

(b.bytes-sum (nvl (a.bytesre0) / 1024Accord1024 has used M

Substr ((b.bytes-sum (nvl (a. Bytes0) / (b.bytes) * 100pr 1) utilization

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.bytes

Order by b.tablespace_name

Http://blog.csdn.net/starnight_cbj/article/details/6792364- tablespaces more

View tablespaces-view the total size and usage of each tablespace

SELECT a.tablespace_name, total / (1024024 * 1024) sizeG

Free / (1024024 * 1024) freeG, (total-free) / (1024024 * 1024) UsedG

Round ((total-free) / total, 4) * 100 Perc

FROM (SELECT tablespace_name, SUM (bytes) free FROM dba_free_space GROUP BY tablespace_name) a

(SELECT tablespace_name, SUM (bytes) total FROM dba_data_files GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

Order by a.tablespace_name

Delete empty tablespaces but do not contain physical files

Drop tablespace tablespace_name

Delete non-empty tablespaces but do not contain physical files

Drop tablespace tablespace_name including contents

-Delete empty tablespaces, including physical files

Drop tablespace tablespace_name including datafiles

Delete non-empty tablespaces, including physical files

Drop tablespace tablespace_name including contents and datafiles

If the tables in other tablespaces have foreign keys and other constraints associated with the fields of the tables in this tablespace, add CASCADE CONSTRAINTS

Drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS

SELECT a.tablespace_name Tablespace name

Total Tablespace size

Free "tablespace remaining size"

(total-free) "Tablespace usage size"

Total / (1024 * 1024 * 1024) "Table Space size (G)"

Free / (1024 * 1024 * 1024) "Tablespace remaining size (G)"

(total-free) / (1024 * 1024 * 1024) "Tablespace usage size (G)"

Round ((total-free) / total, 4) * 100 "usage%"

FROM (SELECT tablespace_name, SUM (bytes) free

FROM dba_free_space

GROUP BY tablespace_name) a

(SELECT tablespace_name, SUM (bytes) total

FROM dba_data_files

GROUP BY tablespace_name) b

Check to see if the table space SYSTEM grows automatically.

SELECT file_id, file_name, tablespace_name, autoextensible, increment_by

FROM dba_data_files

WHERE tablespace_name = 'SYSTEM'

Order by file_id desc

Extended tablespace

Newly added file

Alter tablespace SYSTEM

Add datafile'/ usr/kingdee/oradata/smsdb/users03.dbf'

Size 500M

Autoextend on

Extend existing files

Alter database datafile'/ usr/kingdee/oradata/smsdb/users03.dbf' resize 100000m

Set tablespace to grow automatically

Alter database datafile'/ u01qoradata autoextend on FOSSDBUniqdatafileUnixo2blh7ov4mblh7ov4

View the size of a table

Select G.owner.g.tablewriter name sum (G.sizeG) totalG

From (

Select d.owner,d.segment_name,d.segment_type,d.partition_name,f.table_name,d.tablespace_name

Trunc (d.bytes/1024 / 1024 / 1024 charge 2) sizeG

From dba_segments d

(

Select a.owner,a.table_name segment_name,a.table_name from dba_tables a

Where a. Tablekeeper nameplate Testi SRVWAYBILLTST'

Union

Select b.owner,b.index_name segment_name,b.table_name from dba_indexes b

Where b.tablebread nameplate Testament SRVWAYBILLATE test

Union

Select c.owner, c.segment_name,c.table_name from dba_lobs c

Where c. Tablekeeper nameplate Thousand SRVs WAYBILLTST

) f

Where d.owner=f.owner and d.segment_name = f.segment_name

ORDER BY d.bytes DESC

) G

Group by G.owner,G.table_name

Change the black area to the name of the table you want to query.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.