Locate the specific datafile on which row data is stored in the table and which datafile
Subtitle: uses a PL/SQL statement to locate the specific datafile in which row data is stored in the table.
A dba may often ask you how to know which data file is in the database when you insert a row of data. The answer is yes. Here, we will write this method for more friends to use.
1. Normal query Step 1.1: first check the ROWID of the table's upstream data
Select rowid from tivoli. test_db_avg1 where rownum = 1 |
Output value:
AAAQ0mAAEAAAAhLAAA
1. 2. Locate the file id based on ROWID.
Select dbms_rowid.rowid_relative_fno (row_id => 'aaaq0maaeaaaahlaaa ') from dual |
Output value:
4
1.3 locate the specific file path and name based on the file id
Select file_name from dba_data_fileswhere file_id = 4 |
Output value:
/Dba/oracle/oradata/single/users01.dbf
2. the preceding three steps are simplified into a PL/SQL block.
Set serveroutput on DECLARE File_number INTEGER; Rowid_val ROWID; File_name_val varchar2 (100 ); BEGIN Select rowid into rowid_val FROM tivoli. test_db_avg1 where rownum = 1; File_number: = Dbms_rowid.rowid_relative_fno (rowid_val ); Select file_name into file_name_val from dba_data_files where file_id = file_number; Dbms_output.put_line (file_name_val ); End; / |
Output result:
/Dba/oracle/oradata/single/users01.dbf
3. Verify that the result is correct. 3.1 take datafile 4 (/dba/oracle/oradata/single/users01.dbf) offline.
Alter databasedatafile 4 offline; |
3.2 check whether the table tivoli. test_db_avg1 can be queried normally.
SQL> SELECT ROWID FROM tivoli. test_db_avg1 where rownum = 1; Select rowid from tivoli. test_db_avg1 where rownum = 1 * ERROR at line 1: ORA-00376: file 4 cannot be read at this time ORA-01110: data file 4: '/dba/oracle/oradata/single/users01.dbf' |
The query is no longer normal, indicating that the file is located accurately.
3.3 restore datafile 4 to the online status
SQL> alter database recover datafile 4; Database altered. SQL> alter database datafile 4 online; Database altered. SQL> SELECT ROWID FROM tivoli. test_db_avg1 where rownum = 1; ROWID ------------------ AAAQ0mAAEAAAAhLAAA |
You can also query the data in the tivoli. test_db_avg1 table. The method described in this article shows which file the Specific Row data is stored in.
Author: LI Junjie (Network Name: Step-by-Step), engaged in "system architecture, operating system, storage device, database, middleware, application" six levels of systematic performance optimization work
Join the system performance optimization professional group to discuss performance optimization technologies. GROUP: 258187244