[oracle] create new data file

To create a new data file in oracle 11g is pretty easy.

Steps are:

  1. login as sysdba (you can use sqldeveloper or sqlplus, user sys/<password>@<SID> as SYSDBA)
  2. execute below statements, replacing values as per requirement on your side (this is creating auto extendable db files):
    -- create a user tablespace to be assigned as the default tablespace for users
    CREATE TABLESPACE rddbfile LOGGING
    DATAFILE '/u01/app/oracle/oradata/rddbfile_data.dbf'
    SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL;
    -- create a tablespace for indexes, separate from user tablespace
    CREATE TABLESPACE indx LOGGING
    DATAFILE '/u01/app/oracle/oradata/rddbfile_idx.dbf'
    SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL;
  3. once executed, look at filesystem, files have actually been created as per below:
    sudo ls -lh /u01/app/oracle/oradata/
    total 51M
    -rw-r----- 1 oracle dba       26M May 28 20:07 rddbfile_data.dbf
    -rw-r----- 1 oracle dba       26M May 28 20:07 rddbfile_idx.dbf
  4. retrieve current default tablespace being used by running below query:
    -- returns default tablespace
    SELECT PROPERTY_VALUE
    FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';
  5. odds are you want to use newly created files, simply set “default tablespace” instruction when creating user, as per below:
    drop user myuser;
    create user myuser identified by mypwd default tablespace rddbfile;
    grant all PRIVILEGES  to myuser;

Done!

That’s inserting data and file size should grow automatically.

sources

Post a Comment

Your email is never published nor shared. You're allow to say what you want...