Locate the specific datafile on which row data is stored in the table and which datafile

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.