Oracle remove tablespace data file ora-00604 ora-01426, ora-00604ora-01426

Oracle remove tablespace data file ora-00604 ora-01426, ora-00604ora-01426
Zookeeper

Project Background: Database Management in the previous development environment is messy. When the tablespace is insufficient, only data files are added. After the test, the data has been deleted, but the space occupied by the tablespace in the database cannot be recycled, as a result, the actual usage of the database's storage directory reaches 97% or more, and only 10% is used. Therefore, there is an urgent need to recycle unnecessary space.

Technical Background: Oracle does not provide methods to delete data files like tables and views. data files are part of tablespaces, so they cannot be removed. Oracle starts from 10G R2. You can use Alter tablespace tablespace_name drop datafile file_name; to delete an empty data file, and the corresponding data dictionary information is cleared:

A. Confirm the oracle version (I view the database version on the local machine (windows) and the test environment is the AIX operating system, but the database version is the same). if the version is met, You can directly Delete the data file.

Select * from v $ version;

B. Confirm the removed data file.

A. Determine the data file to be removed. You can use the maximum size and object size of the data file to evaluate whether to remove the file.

Expansion 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 the existing objects in the data file to other tablespaces.

A. Use the following statements for non-partition tables whose segment_type is TABLE:

Alter table bob_rwa3.db move tablespace ETLDATA;

Batch statement:

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 (non-composite partition) for partition tables whose segment_type is TABLE)

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 the segment_type TABLE composite partition:

Alter table BOB_RAY.LOG_TABLE move subpartition P199001010001_NULL tablespace IRM_DATA;

Batch scripts can be written by themselves in the dba_tab_subpartitions table.

D. You can use the following statement for segment_type LOBSEGMENT or LOBINDEX, because the LOB type fields automatically generate two parts: storage data and index (lobsegment, lobindex)

1. Check the segment_name for which the segment_type is lob:

Select * from dba_extents t
Where t. file_id = 10
And t. segment_type like '% LOB % ';

2. Check the column for which segment_type is LOB.

Select * from dba_lobs t
Where t. segment_name like '% SYS_LOB0000145648C00003 %'

;

3. move segment_type to another tablespace. Because moving table only moves non-LOB fields, run the following statement:

Alter table BOB_RAY.BIRT_RESOURCE move tablespace ETLDADA lob (CONTENT) store as (tablespace ETLDATA );

Batch script association can be written

Because the data table space in this project is separated from the index tablespace, the index-related operations are not involved here. If there is an index, it is estimated that it is similar to the table.

D. Check whether the data file is empty.

A. Check whether dba_extents has records (no records are required)

Select * from dba_extents t
Where t. file_id = 10;

B. Check whether dba_segments has records (no records are required)

Select * from dba_segments t
Where t. header_file = 10

NOTE: If dba_segments has data from the recycle bin, an error is reported when the tablespace data file is deleted.

C. Delete the recycle bin data

Purge recyclebin;

E. Delete the data file corresponding to the tablespace.

1. view the data file name

Select * from dba_data_files t
Where t. file_id = 10

;

2. Delete the tablespace Data File

Alter tablespace FERMATDATA drop datafile 'd: \ APP \ ORADATA \ RWADB \ FERMATDATA02.DBF ';

Note: When the statement is executed, the database server does not change when using df-g to view the space. After the database is restarted, the space is obviously recycled.

F. specify the method for storing the table in a fixed data file to (expand)

Alter table bob_ray.bis_risk_bucket allocate extent (datafie '/oracle/oradata/orcl/appdata. dbf ');

This statement does not change the storage of existing data in the table. It takes effect only when new data is inserted,

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.