8 Replies Latest reply: Dec 3, 2012 3:55 PM by Mark Malakanov (user11181920) RSS

    Data file on Linux - timestamp changing

    ski123
      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
          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
            when ever checkpoint happens, SCN is updated in all datafile headers.
            • 3. Re: Data file on Linux - timestamp changing
              ski123
              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
                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
                  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
                    Thank you all very much.

                    S.
                    • 7. Re: Data file on Linux - timestamp changing
                      sb92075
                      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)
                        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