Re: Question about "alter" command.

As a rule of thumb, I always think like this:

If I'm talking to the controlfile or the O/S via Oracle, it's usually an 'alter database'. If I'm talking to the Instance, it's an 'alter system'.

Hence, resizing a tablespace actually means getting the O/S to do some work, and update the Controlfile with the new file size... so it's an alter database.

But performing a log switch is an instruction to LGWR (a background process, and thus part of the Instance) to write to a new location. So that's an alter system.

Put another way, Alter Database commands tend to do permanent things, and alter system commands tend to do transient things.

There are always exceptions, but it usually gets me through.

As for the other 'alters', like tablespace and rollback segment and table, well... they're for things which are specific to the entity being addressed. Rollback segments can be onlined and offlined, and shrunk. Tables can be allocated more extents, or have extents deallocated from them. Tablespaces can be onlined, offlined or backed up.

Regards
HJR "Jordan" <mj_at_nba.com> wrote in message
news:9l6dcd$56s2_at_imsp212.netvigator.com...
> I always confuse when to use the alter xxx command. e.g. alter database,
> alter system, alter tablespace .....
> e.g. I want to resize the tablespace size, I will think 'alter tablespace
> .... resize 10M'. However, the actual result is 'alter database datafile
 ..
> 10M'. Can anyone there when I should use alter database, alter system,
> alter tablespace or others. THANKS.
>
>
Received on Sun Aug 12 2001 - 19:22:05 CDT