Oracle removes table space data files ora-00604 ora-01426



Project background: In the previous development environment database management is more chaotic, in the table space is not enough to add data files, after testing the data has been deleted, but the database table space occupied by the space can not be recycled, resulting in the database storage folder usage of more than 97% of the actual use of more than 10%, there is an urgent need to reclaim the space .

Technical background: Oracle does not provide methods for deleting data files, such as deleting tables and views. The data file is part of the tablespace, so you cannot "move" the tablespace. Oracle 10G R2 starts with the ability to use: Alter tablespace tablespace_name drop datafile file_name, to delete an empty data file, and the corresponding data dictionary information is also cleared:

A. Confirm the Oracle version number (I am viewing the database version number with my Computer (Windows), the test environment is the AIX operating system, but the database version number is the same), the version number satisfies the ability to delete the data file directly

SELECT * from V$version;

B. Confirm the data file that was removed

A. Determine the data files that need to be removed, and be able to evaluate the size and object of the largest expansion of the data file, whether to remove the file

Extended Size:

Select File_id,tablespace_name,max (t.block_id) *8192/1024/1024 from Dba_extents t
where tablespace_name = ' fermatdata '
GROUP BY File_id,tablespace_name

Number of objects:

SELECT * FROM Dba_extents t
where t.file_id =10

C. Move an existing object in the data file to another table space

A. Use the following statement for non-partitioned tables that are Segment_type table

ALTER TABLE bob_rwa3.db move tablespace etldata;

Bulk statements:

Select ' ALTER TABLE ' | | t.owner| | '. ' | | t.segment_name| | ' Move tablespace etldata; ' from Dba_extents t
where t.file_id =10
and T.partition_name is null

B. Use the following statement for a partitioned table that Segment_type is a table (not a composite partition)

ALTER TABLE Bob_ray. T_transition_matrix Move partition P199001010001 tablespace etldata update Global indexes;

Batch script:

Select ' ALTER TABLE ' | | t.owner| | '. ' | | t.segment_name| | ' Move Partition ' | | t.partition_name| | ' tablespace etldata update global indexes; '
From Dba_extents t
where t.file_id =10
And t.partition_name is not null;

C. Use the following statement for Segment_type for table composite partitions

ALTER TABLE Bob_ray. Log_table move subpartition p199001010001_null tablespace irm_data;

A bulk script can write its own dba_tab_subpartitions table.

D. For segment_type to Lobsegment or Lobindex can adopt the following statement, because the LOB type field will voluntarily generate the stored data and index two parts (lobsegment,lobindex)

1. Look for the segment_name Segment_type for LOB:

SELECT * FROM Dba_extents t
where t.file_id =10
and t.segment_type like '%lob% ';

2. View Segment_type as the LOB corresponding column

SELECT * FROM Dba_lobs t
where t.segment_name like '%sys_lob0000145648c00003% '

;

3. Transfer Segment_type to a different tablespace type, since move table simply transfers non-LOB fields, you need to run the following statement

ALTER TABLE Bob_ray. Birt_resource move tablespace Etldada LOB (CONTENT) store as (Tablespace etldata);

A batch script association can be written

Because the data table space in this project is separate from the index table space, there are no related operations involved in the index, assuming that the index is expected to be similar to the table's case.

D. Verify that the data file is empty

A. See if the dba_extents has a record (need not be recorded)

SELECT * FROM Dba_extents t
where t.file_id = 10;

B. Check if the dba_segments has a record (need not be recorded)

SELECT * FROM Dba_segments t
where T.header_file = 10

Note: Assuming that dba_segments has the data for the Recycle Bin, an error ora-00604 when deleting the tablespace data file ora-01426

C. Deleting Recycle Bin data

Purge RecycleBin;

E. Delete the corresponding data file for the Tablespace

1. View the data file name

SELECT * FROM Dba_data_files t
where t.file_id = 10

;

2. Delete a tablespace data file

Alter tablespace fermatdata drop DataFile ' D:\APP\ORADATA\RWADB\FERMATDATA02. DBF ';

Note: When you run out of statements, database server does not change when you view space with Df-g, and after you restart the database, you can see that the space is clearly recycled.

F. Specifying tables stored in a fixed data file method (extended)

ALTER TABLE Bob_ray.bis_risk_bucket allocate extent (Datafie '/oracle/oradata/orcl/appdata.dbf ');

This statement does not alter the storage of existing data in the table and takes effect when the new insert data

Oracle removes table space data files ora-00604 ora-01426