0

Consider empty (!) database creation:

CREATE TABLESPACE reload DATAFILE 'D:\myDb.dbf'  SIZE 3000M  ONLINE;

This is leads to writring 3000 megabytes to the disk for empty database. Is it normal for oracle? I am new to oracle database, did I miss something important in CREATE TABLESPACE which leads to such behavior?

2

If you are able to create this tablespace(logical entity of Oracle database to hold user objects) then you already have created the database.

Yes, this is expected. The statement defines a datafile of 300MB size in underlying storage.

You can also auto increment size. For example, initially you have defined its size as 300M. When you create new objects and perform DML, they will use this space. Once this 300M is consumed by objects you need to either add new datafile or increase its size or let the Oracle increment its size as needed.

Example:

CREATE TABLESPACE tbs_02 
   DATAFILE '/diskb/tbs_f5.dbf' SIZE 500K REUSE
   AUTOEXTEND ON NEXT 500K MAXSIZE 100M;

It creates a tablespace called tbs_02with a datafile of 500K initial size. Once the 500K is occupied by objects it will be incremented by 500K at once and grows upto 100MB.

If you are new to Oracle database I strongly suggest you to follow Oracle Concepts Manual

Reference:

CREATE TABLESPACE

storage_clause

5
  • "You may assign few megabytes at the beginning and define auto increment as needed" - but created db is empty. I understand if it allocates empty file with 3000M, but it writes to this file. – Cherry Apr 5 '17 at 4:06
  • You have defined 300M which which creates data file of 300MB on the underlying file system. What I mean is you could also allocate 100M at first and define auto increment as create tablespace tbs1 datafile 'path/name.dbf size 100M autoextend on next 1M maxsize 2G` – atokpas Apr 5 '17 at 4:28
  • Could you please provide a full statement for creating table space with incrementing file? Because when I try CREATE TABLESPACE reload DATAFILE 'D:\myFile.dbf' SIZE 1M DEFAULT STORAGE (INITIAL 100M MAXSIZE 3G) ONLINE; It fails later, because myFile.dbf restricted to 1M and not increases to 3G. – Cherry Apr 5 '17 at 6:28
  • The problem is you defined initial extent size in storage clause. It means, when you create a object on the tablespace Oracle will assign 100M as a initial extent of the segment(objects such as table). Refer to my lasted update for details about creating tablespace and storage clause. – atokpas Apr 5 '17 at 11:54
  • 2
    @Cherry "…it writes to this file…" sure, the file (and each block within it) needs to be initialized: community.oracle.com/thread/722274?start=15&tstart=0 – Jack Douglas Apr 5 '17 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.