Re: How do I create Datafiles?

elwood_at_rumba.m.isar.de (Konstantinos Agouros) wrote:

>Hi,
>
>my question might be stupid, but how do I create new Datafiles.
>I need that in order to create a new tablespace. Hints welcome.

The CREATE TABLESPACE command will create new datafiles for you.

CREATE TABLESPACE my_tablespace
  DATAFILE 'my_datafile1.dbf' SIZE 100M
  DEFAULT STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 10); ALTER TABLESPACE will let you add more datafiles to an existing tablespace.

ALTER TABLESPACE my_tablespace
  ADD DATAFILE 'my_datafile2.dbf' SIZE 50M;

I know a lot of people will recommend using a default PCTINCREASE of 0 but I wouldn't do it. Oracle doesn't coalesce free space on tablespaces where it's set to 0. If you really don't want a PCTINCREASE, set it to 0 on each object you create in the tablespace but leave the default at something else.

Also, don't forget to grant quotas on the tablespace to users who need to use it. See ALTER USER for that.

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself!