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

First of all, let's introduce the background of the project, which is a java back-end platform that runs on the win2008server platform, mainly stores pictures and videos, takes up a lot of space, and the Oracle database (you know, the rhythm of the crash).

A picture table is mainly used to store blob type picture data, and the hard disk has a total of 800G. It is found that a single tablespace data file has grown to about 30G, and the hard disk has only about 30G of space left. The table is about to explode. The leader is very anxious and urgent, but I am confused. I do not have a detailed record document. I do not know what to do.

Finally, by consulting the boss who wrote the project in that year, get the plan, back up the data for a year, truncate table, directly delete the tablespace, and then re-establish the tablespace, the following records some of the sql statements used at that time.

View each tablespace size select a.tablespace_name, round (a.total_size) "total_size (MB)", round (a.total_size)-round (b.freefreesizepag3) "used_size (MB)", round (b.freefreesizememe3) "free_size (MB)", round (b.freefreesizehand to taltalsized sizecards 100) |'% 'free_rate from (select tablespace_name) Sum (bytes) / 1024 total_size from dba_data_files group by tablespace_name) a, (select tablespace_name Sum (bytes) / 1024 free_size from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name (+) Check the tablespace select tablespace_name,file_id,file_name, round (bytes/ (1024 / 1024), 0) total_space from dba_data_files order by tablespace_name select file_name,autoextensible,increment_by from dba_data_files where tablespace_name=' tablespace name 'empty data it is possible that the tablespace has not been freed You can use the following statement: truncate table pecprocesspic alter table pecprocesspic deallocate UNUSED KEEP 0 Delete tablespaces, delete tablespaces without any data objects drop tablespace wz_img delete tablespaces with any data objects (be careful, you must back up the data! ) drop tablespace wz_img including contents and datafiles; create tablespace create tablespace tablespace name datafile'D:\ app\ Administrator\ oradata\ orcl\ tablespace file name .ora 'size 512m autoextend on next 256m maxsize unlimited; add tablespace file alter tablespace tablespace file add datafile'D:\ app\ Administrator\ oradata\ orcl\ tablespace file name .ora' size 512m autoextend on next 256m maxsize unlimited These are some important sql statements, other backup restore is not written, are operated through pl/sql, not difficult, a large amount of data, backup for nearly seven days, restore about three days.

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.