Extend or add datafile to tablespace for no oracle admins like me

Connected server i couldn’t find enterprise manager and PL/SQL Developer already open and after some search ;

First need to get tablespace name🙂

select * from dba_data_files

If you know the name of your tablespace type like below

select * from dba_data_files t where t.TABLESPACE_NAME = ‘xxxxxx’

Need to be resize then continue like below , this process will increase the size 50G not additional 50G🙂

ALTER DATABASE DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\XXYPURFOLDER\YOUR_DATA_FILE.ORA’ RESIZE 50G;

Here you should be careful because you can touch the block limit of related datafile and need to be create additional one like below

To add additional datafile to tablespace then use such command below

ALTER TABLESPACE USERS_EK ADD DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\XXYPURFOLDER\YOUR_DATA_FILE2.ORA’ SIZE 20G;

And you have to wait , i guess its writing 0 to all file and go !

VM

 

Posted on 14/12/2012, in Database, Oracle10g and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: