Hanne Iren Midttun wrote:

Look at the ALTER DATABASE command. You can offline/drop the file, or shrink
it.

In my case it is never post here until I have at least 2 or 3 cups.. or else
I go thread hunting with a lead pipe... ;-)

--
Billy

"Billy Verreynne" <vslabs (AT) onwe (DOT) co.za> wrote




Why was the default ever K instead of M
still baffles me....

--
Cheers
Nuno Souto
wizofoz2k (AT) yahoo (DOT) com.au.nospam

Hanne Iren Midttun <hannem (AT) tihlde (DOT) org> wrote

An offline drop of the datafile should do it.
alter datafile '/u01/oradata/myfile' offline drop
It will be nice to take the necessary precautions though.
Export all tables in the tablespace,backup, full export ....
HTH
Michael Tubuo Ngong

Anthony Hogan wrote:

Yep. That is what I said "offline/drop" as in maybe drop will not be
possible. Life is a bitch isn't it. And in case you wondering why I
reveling at your problem is 'cause it just friggen great to see others also
having problem besides me... <evil & twisted grin> ;-)

Shrink it. So you will have a useless 1KB datafile in the database. Cover
your ass by renaming it to something like
'../oracle_system_buffer_zone_19.dbf' or something equally impressively
meaningless to obfuscate the clueless. :-)

Hey, at least this problem is better than the junior DBA some years ago who
was suppose to add more space for me and forgot to add the M at the back of
size.. giving me 2K of a raw device instead of 2GB.. Not that was a horror
to fix.


--
Billy

traineensp (AT) softcell (DOT) co.in (Divya) wrote in message news:<c343a42.0306222019.6cd519c9 (AT) posting (DOT) google.com>...
This helped me a while ago
This should give you the names of the tables and the dependent tables

select a.owner OWNER, a.table_name TABLE, c.column_name COL, b.owner
POWNER, b.table_name PTABLE, d.column_name PCOL from dba_constraints
a, dba_constraints b, dba_cons_columns c, dba_cons_columns d where
a.r_constraint_name = b.constraint_name and a.constraint_type =
'R' and b.constraint_type = 'P' and a.r_owner=b.owner and
a.constraint_name = c.constraint_name and
b.constraint_name=d.constraint_name and a.owner = c.owner and
a.table_name=c.table_name and b.owner = d.owner and
b.table_name=d.table_name

In the dependent tables alter table disable constraints;
You should no longer have the errors.You may need to enable the
constraints after you replace your table else Oracle may not have any
record of the constraints you deleted

Vince

You can *never* drop a datafile from a tablespace once it's been added.
Ever. And offline drop doesn't do it either.

The only options are to shrink the file to a stupidly small size that is of
no consequence, or to drop the tablespace and re-create it.

;-(


"michael ngong" <mngong (AT) yahoo (DOT) com> wrote

Must be living in the past.......But looking toward the future, I'd want
the default to be G instead of M.

Cheers,
Brian

Noons wrote:
--
================================================== =================

Brian Peasland
oracle_dba (AT) remove_spam (DOT) peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"

Is that quite true?

I thought you could do the offline drop thing and then look at rebuilding
the controlfile from a script based on the 'alter database backup
controlfile to trace' output. Making sure of course that you leave out the
offending file.

Not possible unless you can handle the implications of doing this though.

Personally I would think about renaming the file (and possibiliy moving it)
to make it fit in with the conventions of the Tablespace is has accedentally
been added to. Then it can be sized up for use as appropriate to the
requirements in that Tablespace. Then add a new (and correctly named) file
for the originally intended Tabelspace. Or am I just being dense!

Regards Kevin


"Arcangelo" <arcangelo-c (AT) yahoo (DOT) com> wrote