7 Replies Latest reply: Dec 11, 2012 2:54 AM by KODS RSS

    Script to add or resize datafile

    596937
      Hi,

      Can someone help me to provide the script to add or resize the datafile if tablespace free space is less than 10%?

      Thanks in Advance....

      Regards,
      Puneet Pradhan
        • 1. Re: Script to add or resize datafile
          Chanchal Wankhade
          Hi,
          you can add datafile to the tablespace which is having size less...
          For normal tablespace 
          
          ALTER TABLESPACE tbs_name 
              ADD DATAFILE 'tbs_file1.dbf'
              SIZE 1024K
              AUTOEXTEND ON;
          
          for Temp tablespace
          
          ALTER TABLESPACE temp ADD TEMPFILE 'temp01.dbf' SIZE 1024 AUTOEXTEND ON;
          SIze is depends on your requirement.
          • 2. Re: Script to add or resize datafile
            Stew Ashton
            Here is a link to documentation I suggest for you:

            http://docs.oracle.com/cd/E11882_01/server.112/e25494/dfiles003.htm#ADMIN11423

            It tells you how to resize manually, but above all it tells you about AUTOEXTEND

            AUTOEXTEND does what you ask without having to write a script.
            • 3. Re: Script to add or resize datafile
              596937
              Hi,

              Thanks for valuable answers, I know we can do it using autoextend but my requirement is different. If we calculate the percentage of free space for tablespace and if tablespace size is less 10% then only script/trigger must add or resize the datafile for that particular tablespace.

              Regards,
              Puneet Pradhan
              • 4. Re: Script to add or resize datafile
                596937
                Hi All,

                Please someone provide me the required script.

                Regards,
                Puneet
                • 5. Re: Script to add or resize datafile
                  Paul  Horth
                  Puneet_19 wrote:
                  Hi All,

                  Please someone provide me the required script.

                  Regards,
                  Puneet
                  Do you know PL/SQL? What have you tried so far?
                  • 6. Re: Script to add or resize datafile
                    AlbertoFaenza
                    Hi Puneet,

                    please explain the reason why you want a script to do what AUTOEXTEND can do.
                    Either you control tablespaces manually and you add/resize datafile when they have a low free space or you use AUTOEXEND.
                    But why you want to do it with a script? What's the reason behind that?

                    In any case, if you want to go in that direction you can google and find how to check free space in tablespace, etc.

                    Regards.
                    Al
                    • 7. Re: Script to add or resize datafile
                      KODS
                      Hi

                      ALTER TABLESPACE test ADD DATAFILE 'test.dbf' SIZE 100K AUTOEXTEND ON NEXT 10K MAXSIZE 100M;

                      The above statement will add datafile TEST.DBF to tablespace TEST with Maximum size of 100M. but when it creates initially with 100K size. Once it reaches to 100K then it allocates 10K like that the AUTOEXTEND the file size till it reaches 100M.

                      ALTER DATABASE DATAFILE 15 RESIZE 150M;

                      The above statement will resize the datafile test(file id is 15) to 150M from 100M;

                      If you like to execute the same from UNIX prompt:

                      $ vi TestSQL.sh
                      #!/bin/ksh
                      ORA_DB=ORCL
                      ORA_USER=system
                      ORA_PWD=manager

                      SQLFILE=TestSQL.sql
                      LOGFILE=TestSQL.log

                      sqlplus -s /nolog <<-EOF >> ${LOGFILE}
                      connect $ORA_USER/$ORA_PWD@$ORA_DB
                      @$SQLFILE
                      exit
                      EOF
                      exit

                      $ vi TestSQL.sh
                      ALTER TABLESPACE test ADD DATAFILE 'test.dbf' SIZE 100K AUTOEXTEND ON NEXT 10K MAXSIZE 100M;


                      Thanks,
                      Kods

                      Edited by: KODS on Dec 11, 2012 2:23 PM