7 Replies Latest reply: May 9, 2012 12:00 AM by mBk77 RSS

    Temp file

    Shaik
      Hi

      we have prod database 10.2.0.4

      I have added temp data file to existing temp tablespace of 5g;
      but os level it is showing 144k size.


      TABLESPACE_NAME GROUP_NAME
      ===========================
      TEMP1          TEMP
      TEMP2          TEMP

      please check my command:

      alter tablespace temp1 add tempfile '/prd/ora/db/apps_st/data/temp06.dbf' size 5G;


      Please help me out in that.


      Regards
      Shaik
        • 1. Re: Temp file
          asifkabirdba
          When the temp tablespace is in use and occupy space then it shows usage space in the OS level. When the temp tablespace usage is 100% then you can see 5 GB occupied from the OS level.


          Regards
          Asif Kabir
          • 2. Re: Temp file
            713555
            after you run the command in sqlplus

            alter tablespace temp1 add tempfile '/prd/ora/db/apps_st/data/temp06.dbf' size 5G;

            do
            ! ls -l /prd/ora/db/apps_st/data/temp06.dbf

            and post the results

            also do
            select name, bytes/1024/1024 from v$tempfile
            • 3. Re: Temp file
              415289
              Its normal,your command is right.
              its showing 144KB and This tempfile is not yet used by any user process so oracle will actual consume the space from OS mount point when some session is using this dbf file for any Temp operation.

              So make sure you've enough space available(5GB) when actually required by session for this tempfile.
              • 4. Re: Temp file
                Shaik
                Hi

                Please find the output

                [oraprd@dbprd data]$ ls -l /prd/ora/db/apps_st/data/temp06.dbf
                -rw-r----- 1 oraprd dba 5368717312 May 8 15:59 /prddata03/oraprd/db/apps_st/data/temp06.dbf



                SQL> select name, bytes/1024/1024 from v$tempfile;

                NAME BYTES/1024/1024
                ------------------------------------------
                /prd/ora/db/apps_st/data/temp01.dbf
                12000

                /prd/ora/db/apps_st/data/temp02.dbf
                12000

                /prd/ora/db/apps_st/datatemp03.dbf
                12288


                NAME
                --------------------------------------------------------------------------------
                BYTES/1024/1024
                ---------------
                /prd/ora/db/apps_st/data/temp04.dbf
                0

                /prd/ora/db/apps_st/data/temp05.dbf
                6072

                /prd/ora/db/apps_st/data/temp06.dbf
                5120


                6 rows selected.
                • 5. Re: Temp file
                  713555
                  >
                  [oraprd@dbprd data]$ ls -l /prd/ora/db/apps_st/data/temp06.dbf
                  -rw-r----- 1 oraprd dba 5368717312 May 8 15:59 /prddata03/oraprd/db/apps_st/data/temp06.dbf
                  ..
                  ..
                  /prd/ora/db/apps_st/data/temp06.dbf
                  5120

                  >

                  well this is showing 5gb in size on database and filesystem. where are you getting 144k from?
                  • 6. Re: Temp file
                    Shaik
                    Hi

                    Once added a datafile i checked with du command it shown 144k, where as now i have checked it is showing 4G.
                    The first reply to this thread is showing that whenever this datafile started using this size is increasing.

                    Regards
                    shaik
                    • 7. Re: Temp file
                      mBk77
                      Large sort operations performed by the database may result in a temporary tablespace growing and occupying a considerable amount of disk space.

                      http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces007.htm#CFHJFBBJ