1 2 Previous Next 27 Replies Latest reply: Feb 5, 2013 10:22 PM by Arun RSS

    how to find out a specific record in which datafile?

    Arun
      Hi All,

      Database version :11gR2

      I am inserting a table a record called 'xx'. and the table is in a tablespace which has lets say 10 datafiles. How to find out the datafile which stores this 'xx' record.

      Thank you,
      Arun
        • 1. Re: how to find out a specific record in which datafile?
          asahide
          Hi,

          check DBA_EXTENTS -> FILE_ID .

          Regards,
          • 2. Re: how to find out a specific record in which datafile?
            Arun
            How do i get the extent ID or block id?

            Edited by: Arun on Feb 4, 2013 10:28 PM
            • 3. Re: how to find out a specific record in which datafile?
              LaserSoft
              Arun wrote:
              How do i get the extent ID or block id?

              Edited by: Arun on Feb 4, 2013 10:28 PM
              SQL> select segment_name,segment_type,file_id from dba_extents where segment_name='&table_name'
              • 4. Re: how to find out a specific record in which datafile?
                Arun
                yes.. i get file_id for three datafiles.. How to find the exact one?



                CISADM@MX210POC>select SEGMENT_NAME,EXTENT_ID,file_id,BLOCK_ID from dba_extents where segment_name='TEST';

                SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID
                --------------------------------------------------------------------------------- ---------- ---------- ----------
                TEST 0 5 117440
                TEST 3 5 117472
                TEST 6 5 117504
                TEST 9 5 117536
                TEST 12 5 117568
                TEST 15 5 117600
                TEST 18 5 117632
                TEST 21 5 117664
                TEST 24 5 117696
                TEST 27 5 117728
                TEST 30 5 117760
                TEST 33 5 117792
                TEST 36 5 117824
                TEST 39 5 117856
                TEST 42 5 117888
                TEST 45 5 117920
                TEST 48 5 117952
                TEST 51 5 117984
                TEST 54 5 118016
                TEST 57 5 118048
                TEST 60 5 118080
                TEST 63 5 118112
                TEST 66 5 118144
                TEST 69 5 118176
                TEST 72 5 118208
                TEST 75 5 118240
                TEST 78 5 118272
                TEST 81 5 118304
                TEST 84 5 118336
                TEST 87 5 118368
                TEST 90 5 118400
                TEST 93 5 118432
                TEST 96 5 118464
                TEST 99 5 118496
                TEST 1 7 128
                TEST 4 7 160
                TEST 7 7 192
                TEST 10 7 224
                TEST 13 7 256
                TEST 16 7 288
                TEST 19 7 320
                TEST 22 7 352
                TEST 25 7 384
                TEST 28 7 416
                TEST 31 7 448
                TEST 34 7 480
                TEST 37 7 512
                TEST 40 7 544
                TEST 43 7 576
                TEST 46 7 608
                TEST 49 7 640
                TEST 52 7 672
                TEST 55 7 704
                TEST 58 7 736
                TEST 61 7 768
                TEST 64 7 800
                TEST 67 7 832
                TEST 70 7 864
                TEST 73 7 896
                TEST 76 7 928
                TEST 79 7 960
                TEST 82 7 992
                TEST 85 7 1024
                TEST 88 7 1056
                TEST 91 7 1088
                TEST 94 7 1120
                TEST 97 7 1152
                TEST 100 7 1184
                TEST 2 8 128
                TEST 5 8 160
                TEST 8 8 192
                TEST 11 8 224
                TEST 14 8 256
                TEST 17 8 288
                TEST 20 8 320
                TEST 23 8 352
                TEST 26 8 384
                TEST 29 8 416
                TEST 32 8 448
                TEST 35 8 480
                TEST 38 8 512
                TEST 41 8 544
                TEST 44 8 576
                TEST 47 8 608
                TEST 50 8 640
                TEST 53 8 672
                TEST 56 8 704
                TEST 59 8 736
                TEST 62 8 768
                TEST 65 8 800
                TEST 68 8 832
                TEST 71 8 864
                TEST 74 8 896
                TEST 77 8 928
                TEST 80 8 960
                TEST 83 8 992
                TEST 86 8 1024
                TEST 89 8 1056
                TEST 92 8 1088
                TEST 95 8 1120
                TEST 98 8 1152
                TEST 101 8 1184
                • 5. Re: how to find out a specific record in which datafile?
                  LaserSoft
                  Arun wrote:
                  yes.. i get file_id for three datafiles.. How to find the exact one?
                  So the tablespace contains multiple data files and table data can be across the data files.

                  And to find out particular ROW ID belongs to datafile, you have to play with the package
                  SQL> select dbms_rowid.rowid_to_absolute_fno(rowid,'SCHEMANAME','TABLENAME') from tablename;
                  • 6. Re: how to find out a specific record in which datafile?
                    asahide
                    Hi,

                    You can use DBMS_ROWID package..

                    Regards,
                    • 7. Re: how to find out a specific record in which datafile?
                      yoonas
                      select 
                          DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
                          DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
                          DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK"
                          DBMS_ROWID.ROWID_ROW_NUMBER(rowid) "ROW"
                         from TEST
                      • 8. Re: how to find out a specific record in which datafile?
                        Andrejs Karpovs
                        Hi LaserSoft,

                        Can you please share your email ID at noflow@inbox.lv
                        I would like to contact you with regards to 11g Grid Control installation media. I saw you had it in some other thread.
                        I would appreciate if we could get in touch. Thanks.

                        Regards,
                        Andrejs.
                        • 9. Re: how to find out a specific record in which datafile?
                          Arun
                          Thanks a lot for all the correct answers... It all solved my question.. Before marking it answered i have few more quiestions.. Appreciate if you could answer..

                          I am experimenting few things. I have found the datafile and opened the file with vi editor. I found the record 'xx' and updated with yy on vi editor and saved it.

                          As expected it shows the block corruption while i query for that data. But now i am trying to recover using rman.


                          CISADM@MX210POC>elect * from test where owner_flg='XX';
                          select * from test where owner_flg='XX'
                          *
                          ERROR at line 1:
                          ORA-01578: ORACLE data block corrupted (file # 8, block # 869)
                          ORA-01110: data file 8: '/mwm_oradata_01/MX210POC/cists03.dbf'

                          I do not have any backups but the database is in archivelog mode. Is there anyway i can recover it? rman it shows the below error.

                          RMAN> blockrecover datafile 8 block 869;

                          Starting recover at 05-FEB-13
                          using target database control file instead of recovery catalog
                          allocated channel: ORA_DISK_1
                          channel ORA_DISK_1: SID=333 device type=DISK
                          allocated channel: ORA_DISK_2
                          channel ORA_DISK_2: SID=412 device type=DISK

                          RMAN-00571: ===========================================================
                          RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                          RMAN-00571: ===========================================================
                          RMAN-03002: failure of recover command at 02/05/2013 02:29:44
                          RMAN-06026: some targets not found - aborting restore
                          RMAN-06023: no backup or copy of datafile 8 found to restore


                          Thanks,
                          Arun
                          • 10. Re: how to find out a specific record in which datafile?
                            LaserSoft
                            Arun wrote:
                            Thanks a lot for all the correct answers... It all solved my question.. Before marking it answered i have few more quiestions.. Appreciate if you could answer..

                            I am experimenting few things. I have found the datafile and opened the file with vi editor. I found the record 'xx' and updated with yy on vi editor and saved it.

                            As expected it shows the block corruption while i query for that data. But now i am trying to recover using rman.


                            CISADM@MX210POC>elect * from test where owner_flg='XX';
                            select * from test where owner_flg='XX'
                            *
                            ERROR at line 1:
                            ORA-01578: ORACLE data block corrupted (file # 8, block # 869)
                            ORA-01110: data file 8: '/mwm_oradata_01/MX210POC/cists03.dbf'

                            I do not have any backups but the database is in archivelog mode. Is there anyway i can recover it? rman it shows the below error.

                            RMAN> blockrecover datafile 8 block 869;

                            Starting recover at 05-FEB-13
                            using target database control file instead of recovery catalog
                            allocated channel: ORA_DISK_1
                            channel ORA_DISK_1: SID=333 device type=DISK
                            allocated channel: ORA_DISK_2
                            channel ORA_DISK_2: SID=412 device type=DISK

                            RMAN-00571: ===========================================================
                            RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                            RMAN-00571: ===========================================================
                            RMAN-03002: failure of recover command at 02/05/2013 02:29:44
                            RMAN-06026: some targets not found - aborting restore
                            RMAN-06023: no backup or copy of datafile 8 found to restore


                            Thanks,
                            Arun
                            It clears that do not have backup. Can you check what object it is?
                            do you have any backup of that object? such as "export backup" or metadata? so that you can drop and recreate.
                            • 11. Re: how to find out a specific record in which datafile?
                              Arun
                              No backup.. But i have a DR setup for this database. But anyway its a test table. I need to know if i can recover this table without using DR or any backup.

                              I have all the archivelogs i need to recover this record. is there anyway i can use this logfiles?

                              Thanks,
                              Arun
                              • 12. Re: how to find out a specific record in which datafile?
                                yoonas
                                Did you go through this one
                                http://docs.oracle.com/cd/E29505_01/backup.1111/e10642/rcmblock.htm
                                • 13. Re: how to find out a specific record in which datafile?
                                  JohnWatson
                                  Arun wrote:
                                  No backup.. But i have a DR setup for this database. But anyway its a test table. I need to know if i can recover this table without using DR or any backup.

                                  I have all the archivelogs i need to recover this record. is there anyway i can use this logfiles?

                                  Thanks,
                                  Arun
                                  Easy, if you have all the archive logfiles. In the Backup And Recovery Users Guide, look for "Re-Creating Data Files When Backups Are Unavailable".
                                  --
                                  John Watson
                                  http://skillbuilders.com
                                  • 14. Re: how to find out a specific record in which datafile?
                                    Arun
                                    Thanks Yoonus,

                                    i will read it..
                                    1 2 Previous Next