Re: Tables in Datafile

On Fri, 18 Jun 2004 12:13:26 +0100, "Mill" <mill.h_at_ntlworld.com> wrote:

>Hi
>
>A previous DBA on our site had extended a tablespace by creating an
>additional datafile, unfortunately
>he didn't create it with the logging switch.
>
>How can i find out which tables are actually in that part of the datafile ?
>

Don't you mean "in that part of the tablespace"?

In truth, all *or parts* of any or all of the tables in that tablespace could be in either or both of the files that constitute the tablespace. You get to specify which tablespace a table goes in, but Oracle decides where to put the various extents that are allocated for that table.

>I want to recreate the datafile with the logging switch then move all the
>data into this new file
>and drop the old datafile.
>

The 'logging' switch does not apply to data files, it applies to tablespaces. And even there, it is only a default to be applied to segments within the tablespace if it is not explicity specified when those objects are created.

I'd just alter the tablespace to set logging on, then identify all objects within the tablespace that have been set to nologging.

ALTER TABLESPCE my_ts LOGGING;

SELECT owner,

                table_name,
FROM dba_tables
WHERE tablespace_name = 'MY_TBLSPC'
AND logging = 'NO';

ALTER TABLE ...... LOGGING;         
>Using Oracle 8i on Windows 2000 Adv Server
>
>Regards
>
>Mill
>
>
Received on Fri Jun 18 2004 - 08:10:42 CDT