This discussion is archived
8 Replies Latest reply: Dec 3, 2012 1:55 PM by Mark Malakanov (user11181920) RSS

Data file on Linux - timestamp changing

ski123 Newbie
Currently Being Moderated
Oracle 11.2.0.3 database created on Linux. The data files are on Linux. This database has one customized tablespace which has two data files. Since application doesn't use this tablespace anymore, we need to remove this tablespace and its two data files. BUT it's found that these two data files timestamp is still changing which tells us that something is still access and updating these two files. Application developers told us they are no longer use this tablespace, why its data files is still updating? How can I find which process is using this two files?

709107712 Nov 27 02:39 data67.dbf
642260992 Nov 27 02:39 data68.dbf


Appreciate any ideas.

S.
  • 1. Re: Data file on Linux - timestamp changing
    sb92075 Guru
    Currently Being Moderated
    ski123 wrote:
    Oracle 11.2.0.3 database created on Linux. The data files are on Linux. This database has one customized tablespace which has two data files. Since application doesn't use this tablespace anymore, we need to remove this tablespace and its two data files. BUT it's found that these two data files timestamp is still changing which tells us that something is still access and updating these two files. Application developers told us they are no longer use this tablespace, why its data files is still updating? How can I find which process is using this two files?

    709107712 Nov 27 02:39 data67.dbf
    642260992 Nov 27 02:39 data68.dbf


    Appreciate any ideas.

    S.
    SCN is written to file headers as SCN changes
  • 2. Re: Data file on Linux - timestamp changing
    vlethakula Expert
    Currently Being Moderated
    when ever checkpoint happens, SCN is updated in all datafile headers.
  • 3. Re: Data file on Linux - timestamp changing
    ski123 Newbie
    Currently Being Moderated
    My point is that if no activity on these files, there should not have checkpoint. Is there anyway to find out which process update these files?

    Thanks

    S.
  • 4. Re: Data file on Linux - timestamp changing
    sb92075 Guru
    Currently Being Moderated
    ski123 wrote:
    My point is that if no activity on these files, there should not have checkpoint.
    WRONG!
    Is there anyway to find out which process update these files?
    CHECKPOINT
  • 5. Re: Data file on Linux - timestamp changing
    vlethakula Expert
    Currently Being Moderated
    CHECKPOINT is at database level,
    At any given point the SCN in all datafile headers must be same (unless they are offline).

    select file#,tablespace_name,checkpoint_change# from v$datafile_header;

    FILE# TABLESPACE_NAME CHECKPOINT_CHANGE#
    ------------------------------ ------------------------------ ------------------------------
    1 SYSTEM 238969054403
    2 UNDOTBS1 238969054403
    3 SYSAUX 238969054403
    4 USERS 238969054403
    5 DEM01 238969054403
    6 GGSDEMO 238969054403

    alter tablespace users offline;

    alter system checkpoint;


    select file#,tablespace_name,checkpoint_change# from v$datafile_header;

    FILE# TABLESPACE_NAME CHECKPOINT_CHANGE#
    ------------------------------ ------------------------------ ------------------------------
    1 SYSTEM 238969068565
    2 UNDOTBS1 238969068565
    3 SYSAUX 238969068565
    4 0
    5 DEM01 238969068565
    6 GGD 238969068565

    alter tablespace users online;

    FILE# TABLESPACE_NAME CHECKPOINT_CHANGE#
    ------------------------------ ------------------------------ ------------------------------
    1 SYSTEM 238969068622
    2 UNDOTBS1 238969068622
    3 SYSAUX 238969068622
    4 USERS 238969068622
    5 DEM01 238969068622
    6 GGD 238969068622
  • 6. Re: Data file on Linux - timestamp changing
    ski123 Newbie
    Currently Being Moderated
    Thank you all very much.

    S.
  • 7. Re: Data file on Linux - timestamp changing
    sb92075 Guru
    Currently Being Moderated
    ski123 wrote:
    Thank you all very much.

    S.
    Handle:     ski123
    Status Level:     Newbie
    Registered:     Apr 23, 2002
    Total Posts:     358
    Total Questions:     106 (49 unresolved)


    how SAD!
  • 8. Re: Data file on Linux - timestamp changing
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    Application developers told us they are no longer use this tablespace, why its data files is still updating? How can I find which process is using this two files?
    CKPT updates datafiles. Unless they are read-only.
    If you do not want these files updated
    ALTER TABLESPACE MyTS READ ONLY;

    wait couple of days to make sure nobody screams...
    then
    DROP TABLESPACE MyTS INCLUDING CONTENTS AND DATAFILES;

    And keep the email where Application developers told us they are no longer use this tablespace.

    Edited by: user11181920 on Dec 3, 2012 4:55 PM

Legend

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