Re: Expanding Database Size

Joe Schwartz schrieb am 03-Dez-97 21:26:37 zum Thema "Expanding Database Size":
>Hi everyone,
> Please excuse my ignorance in advance. I'm an absolute
>beginner at Oracle DBA functions.
> I created a database on an NT server (3.51) running Oracle
>7.3. I made the database too small, so that users running a software
>package using the Oracle engine are unable to add records. Can someone
>please help me out with expanding the database (without, of course,
>losing the existing data). I could either create additional extents,
>create and format a new database and move data to it, etc., whatever
>you think is best. Since I don't know how to do any of the above, any
>help would be appreciated. I've read the manuals with no luck, and am
>scheduled for training, but this problem is immediate. Please E-Mail
>me at jschwrtz_at_aes.tracor.com. Thank you.
 

> Joe

First, You can enlarge the tablespaces by giving the following command:

alter datafile 'datafile_name' resize 1000M;

This enlarges the datafile to 1GB. The datafile should belong to the tablespace that is too small. Have a look at v$datafiles.

Alternatively You can do:

alter tablespace you_tablespace add datafile 'file_name' size 1000M;

This adds another datafile to the tablespace. I would prefer the first solution because I think there a limit on the maximum number of datafiles a database can have.

You eventually should consider using the Enterprise Manager to do the above things because there You can do the changes in a more intuitive way without having to worry about the correct SQL syntax...

If there is enough room left in the tablespace but the table has reached max_extents, You should export that table using 'COMPACT=Y' in the export control file. After dropping and importing the table You should check the next extent size and adust it.
The ex- and import should put the whole table into one initial extent.

As I'm writing this at home, I don't have any Oracle to check the exact syntax of the given commands. They might *slightly* differ! ;-)

Hope this helps,
Lothar

--
Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |