This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Feb 5, 2013 8:22 PM by Arun RSS

how to find out a specific record in which datafile?

Arun Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    Hi,

    check DBA_EXTENTS -> FILE_ID .

    Regards,
  • 2. Re: how to find out a specific record in which datafile?
    Arun Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Hi,

    You can use DBMS_ROWID package..

    Regards,
  • 7. Re: how to find out a specific record in which datafile?
    yoonas Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Yoonus,

    i will read it..
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points