Re: In which tablespaces are objects located?

Hello all,

Surprise! It seems that there were some Oracle objects in this tablespace. At least associated somehow.

Even though the segments only listed Rollback segments:

  1 select segment_type,
  2 tablespace_name,
  3 count(1)
  4 from dba_segments
  5 group by segment_type,
  6* tablespace_name

...

ROLLBACK          RBS                                     3

The tablespace was ONLINE, but the rollback segments were OFFLINE.

It seems that there was also Oracle packages in there.

When I tried to read the code for DBMS_OUTPUT:

accept ls_package_name prompt "Enter the proc to list code for: " ; Select text
from all_source
where name = rtrim(ltrim(upper( '&ls_package_name' ))) and type = 'PACKAGE BODY'
order by line;

I got an error:
ERROR:
ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/rollback/ntst1/trbs1.dbf'

But, the package still worked:

SQL> begin
  2 dbms_output.put_line('testing');
  3 end;
  4 /
testing

PL/SQL procedure successfully completed. Elapsed: 00:00:00.12

So, I put the tablespace back online. It required some recovery. I could then find the source code for the package.

So, I conclude that this package had some dependency onto this non-SYSTEM tablespace. Is this correct, or is my logic wrong?

Ciao,

Argosy

In article <8gh9sb$psr$1_at_nnrp1.deja.com>,   argosy22_at_my-deja.com wrote:
> Hi all,
>
> I have inherited a database with a tablespace: RBS
> that has 3 rollback segments: R01, R02, R03
> and is owned by SYS.
>
> The rollback segments are offline, but the tablespace
> is online. But when I look at this file on the Unix side,
> it is always current to within 30 minutes.
>
> I was thinking of taking the tablespace offline,
> but first I wondered what objects are contained in
> it. We know about the rollback segments. But what else?
> I'm thinking of stored functions or procedures.
>
> This query does not return the tablespace.
>
> Select DEFAULT_TABLESPACE,
> TEMPORARY_TABLESPACE
> from dba_users
> group by DEFAULT_TABLESPACE,
> TEMPORARY_TABLESPACE
>
> These the views that have a column about tablespaces.
>
> SYS ALL_CLUSTERS TABLESPACE_NAME
> SYS ALL_INDEXES TABLESPACE_NAME
> SYS ALL_TABLES TABLESPACE_NAME
> SYS DBA_CLUSTERS TABLESPACE_NAME
> SYS DBA_DATA_FILES TABLESPACE_NAME
> SYS DBA_EXTENTS TABLESPACE_NAME
> SYS DBA_FREE_SPACE TABLESPACE_NAME
> SYS DBA_FREE_SPACE_COALESCED TABLESPACE_NAME
> SYS DBA_INDEXES TABLESPACE_NAME
> SYS DBA_ROLLBACK_SEGS TABLESPACE_NAME
> SYS DBA_SEGMENTS TABLESPACE_NAME
> SYS DBA_TABLES TABLESPACE_NAME
> SYS DBA_TABLESPACES TABLESPACE_NAME
> SYS DBA_TS_QUOTAS TABLESPACE_NAME
> SYS SM$TS_AVAIL TABLESPACE_NAME
> SYS SM$TS_FREE TABLESPACE_NAME
> SYS SM$TS_USED TABLESPACE_NAME
> SYS USER_CLUSTERS TABLESPACE_NAME
> SYS USER_EXTENTS TABLESPACE_NAME
> SYS USER_FREE_SPACE TABLESPACE_NAME
> SYS USER_INDEXES TABLESPACE_NAME
> SYS USER_SEGMENTS TABLESPACE_NAME
> SYS USER_TABLES TABLESPACE_NAME
> SYS USER_TABLESPACES TABLESPACE_NAME
> SYS USER_TS_QUOTAS TABLESPACE_NAME
> SYS V_$SORT_SEGMENT TABLESPACE_NAME
>
> Other than the views that are specific to an object type,
> (ie. dba_tables, dba_indexes, dba_rollback_segs)
> I can't see any view that links objects and tablespaces
> together. Is there one?
>
> How can I tell which tablespace an object is stored in?
> Or, what objects are in which tablespace?
>
> Ciao,
>
> Argosy
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 30 2000 - 00:00:00 CDT