Re: Command to free unused space in a data file

Edwinah63 wrote:
> in MS SQL Server we have a command DBCC SHRINKFILE ( myDB,
> truncateonly) which causes any unused space in the data files to be
> released to the operating system and shrinks the file to the last
> allocated extent, reducing the file size without moving any data.
>
> does Oracle have anything similar?

Yes

>
> if so, what is/are the command/s ?

alter database datafile 'filepathname '
resize <whatever-you-want>M;

"M" is for MB.

>
> also what commands can I use to defrag my database so I can get all my
> data into contiguous blocks?

You don't need to do that in Oracle.
But you can use export and import to do this if you absolutely want to waste time.
Or if it's just one table, 'alter table move' to a different tablespace, then drop the original ts (provided it's empty!).

> Or in this case do I just do the windows
> defrag?

It's always a good idea to do that in all database partitions after you have allocated initial sizes but *before* you load the data. Only once, unless you want to spend time shrinking/expanding files.

> Or does Oracle manage this itself?
>

Pretty much does inside its files, provided you are using anything newer than release 8ir3 and locally managed tablespaces (LMT).

> * Oracle 9i sp5
> * win 2k

Sounds good to me. Make sure it's patched to latest 9i patch level. Received on Thu Feb 02 2006 - 06:16:30 CST