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



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

Technical background: Oracle does not provide a way to delete a data file, such as deleting a table or view, and the data file is part of a tablespace, so you cannot "move" the tablespace. Oracle 10G R2 Start, can be used: 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 (I am viewing the database version as native (Windows), the test environment is the AIX operating system, but the database version is the same), the version satisfies the data file can be deleted directly

SELECT * from V$version;

B. Confirm the data file that was removed

A. Identify the data files that need to be removed, and you can evaluate the size of the data file with the maximum scale and the number of objects, 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 a non-partitioned table that segment_type as 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 associated dba_tab_subpartitions table.

D. The following statements can be used for Segment_type to lobsegment or Lobindex, because fields of LOB type automatically generate 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 because move table only moves non-LOB fields, so you need to execute the following statement

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

Batch Script Association to write

Because the data table space in this project is separate from the index table space, there are no related operations on the index, and if there is an index, the condition is similar to the table.

D. Verify that the data file is empty

A. See if the dba_extents has a record (requires no records)

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

B. See if Dba_segments has a record (requires no records)

SELECT * FROM Dba_segments t
where T.header_file = 10

Note: If dba_segments has 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. Deleting a data file for a 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 finish executing the statement, the database server does not change when you view the space with Df-g, and after you restart the database, you can see that the space is significantly 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

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

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