Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Create Bigfile Tablespace – Oracle Managed Files (OMF)

Posted by Tyler Muth on February 10, 2012

Working with Exadata a lot (almost exclusively) I create almost all my tablespaces as bigfile using OMF. For some reason I can never find the right syntax for this when I need it. So, that’s the main reason for this post.

CREATE BIGFILE TABLESPACE data1  NOLOGGING datafile '+DATA_DM01' SIZE 50G AUTOEXTEND ON NEXT 5G;
CREATE BIGFILE TABLESPACE dbfs_ts  NOLOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE  SEGMENT SPACE MANAGEMENT AUTO datafile '+DBFS_DM01' SIZE 500G AUTOEXTEND ON NEXT 10G;

On a related note, wow I use “column mode” in Notepad++ (link) a lot for this stuff. Many other editors support column mode, including an old favorite on Windows, UltraEdit (link).

  

About these ads

3 Responses to “Create Bigfile Tablespace – Oracle Managed Files (OMF)”

  1. This i have seen in GUI … but now were i was able to locate in both version … thank you for sharing. Your blog seems to be interesting one. I will go through each and every post you have made.

  2. This site definitely has all of the information I needed concerning this subject and didn’t know who to ask.

  3. Hi Tyler,

    Small OMF syntax issue. Consider:

    SQL>
    SQL>
    SQL> CREATE BIGFILE TABLESPACE data1 NOLOGGING datafile ‘+DATA_DM01′ SIZE 50G AUTOEXTEND ON NEXT 5G;
    CREATE BIGFILE TABLESPACE data1 NOLOGGING datafile ‘+DATA_DM01′ SIZE 50G AUTOEXTEND ON NEXT 5G
    *
    ERROR at line 1:
    ORA-01119: error in creating database file ‘+DATA_DM01′
    ORA-17502: ksfdcre:4 Failed to create file +DATA_DM01
    ORA-15001: diskgroup “DATA_DM01″ does not exist or is not mounted
    ORA-15001: diskgroup “DATA_DM01″ does not exist or is not mounted

    SQL> CREATE BIGFILE TABLESPACE data1 NOLOGGING datafile ‘DATA_DM01′ SIZE 1G AUTOEXTEND ON NEXT 5G;

    Tablespace created.

    SQL> drop tablespace data1 including contents and datafiles ;

    Tablespace dropped.

    SQL>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: