4 Replies Latest reply: Apr 23, 2013 4:32 AM by 1002717 RSS

    OMF_IN_ASM

    User235837 -Oracle
      Hi All,

      For 600GB Tablespace iam creating below tablespace . i believe it will auto extend with unlimited max size.
      so when i import 600GB data it will automatically extend rite ? please clarify !!!!


      The datafile is 100 MB and is autoextensible with an unlimited maximum size.(ASM_OMF)

      SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '+DATA';
      SQL> CREATE TABLESPACE TBS01;



      Thanks,
        • 1. Re: OMF_IN_ASM
          Fran
          from documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses004.htm#i998826
          If you do not specify SIZE, then the database creates a 100M file with AUTOEXTEND enabled. When autoextension is required, the database extends the file by its original size or 100MB, whichever is smaller. You can override this default behavior by specifying the NEXT clause.
          http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7003.htm:
          You must specify the DATAFILE or TEMPFILE clause unless you have enabled Oracle-managed files by setting a value for the DB_CREATE_FILE_DEST initialization parameter. For Automatic Storage Management disk group files, the parameter must be set to a multiple file creation form of Automatic Storage Management filenames. If this parameter is set, then the database creates a system-named 100 MB file in the default file destination specified in the parameter. The file has AUTOEXTEND enabled and an unlimited maximum size.
          • 2. Re: OMF_IN_ASM
            User235837 -Oracle
            Thanks for your valuable reply ....:)
            • 3. Re: OMF_IN_ASM
              Hemant K Chitale
              Your "CREATE TABLESPACE" statement would create a single datafile. Do you want a single file of 600GB ? Then you should create a BIGFILE TABLESPACE.


              Hemant K Chitale
              • 4. Re: OMF_IN_ASM
                1002717
                By default it will create the tablespace with MAXSIZE UNLIMITED, however the meaning of 'unlimited' is that it will be set to the maximum addressable size. This is operating system dependent, but is generally 32G for an 8K blocksize or 128G for a 16k blocksize. As Hemant says, if you want to go beyond these limits in a single file, you must use bigfile tablespaces.

                For your 600G therefore, you will probably need to create your tablespace with 20 datafiles assuming an 8k blocksize.

                Edited by: 999714 on Apr 23, 2013 2:32 AM