Creating Tablepace 1

[twincalicos]

Thanks. Also, what Oracle user should I be using to create a tablespace?

I tried to make one using the "system" user to give it the one it wanted, but it seemed like the tablespace didn't get created.

I used the GUI screens to do it. Also, how do you know what size to make it?

 

[SantaMufasa]

Calico,

You should be a user that is a member of the DBA role. By default, both the "SYS" and "SYSTEM" users are DBAs. Either of them can issue a command to create a user and bestow the DBA privileges with the following commands:

CREATE USER CALICO IDENTIFIED BY <some password>;
GRANT DBA TO CALICO;

Then logged in (to SQL*Plus, for example) as any of these DBA users, you can issue the following SQL command (which I use) to create a tablespace:

CREATE TABLESPACE yada DATAFILE '<some fully qualified file name>' SIZE 10M
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

There are many variations of the CREATE TABLESPACE command, but this favourite variant that I use creates the tablespace with a 10MB-initial size, expanding on an as-needed basis in 10MB increments, to a maximum size of 2GB. Local extent management is the preferable, efficient method to use for object growth within the tablespace.

You may add virtually as many additional files as you need, that behave in similar fashion, to the same tablespace with this SQL command:

ALTER TABLESPACE yada
ADD DATAFILE '<another unique, fully qualified file name>' SIZE 10M
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

Use of the tablespace can be either implicit or explicit. Implicitly, the user, "CALICO" uses the "YADA" tablespace with this command:

ALTER USER CALICO DEFAULT TABLESPACE YADA;

The user, "CALICO", by virtue of membership in the DBA role, has UNLIMITED TABLESPACE privileges, but other (non-DBA) users receive rights to use a certain amount of space with the following command:

ALTER USER CALICO QUOTA 100M ON yada;
or
ALTER USER CALICO QUOTA UNLIMITED ON yada;

The above command examples give 100MB or unlimited space allocation to CALICO in the tablespace, YADA.

Let us know if this answers your questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]