| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How is this possible?
Ed Stevens wrote:
> In putting together my response to mill in the thread 'tables in
> datafile' I saw this in one of my dbs:
>
>
> 1 select owner,
> 2 table_name,
> 3 tablespace_name
> 4 from dba_tables
> 5 where logging = 'NO'
> 6* order by owner, table_name
> SQL> /
>
> OWNER TABLE_NAME
> TABLESPACE_NAME
> ------------------------------ ------------------------------
> ---------------
> ------------
> DB_XRAY BMC$PKK_ALARM_HISTORY LCOTOLTS
> DB_XRAY BMC$PKK_ALM_HIST_ADD_INFO LCOTOLTS
> DB_XRAY BMC$PKK_DAILY_ACTIVITY LCOTOLTS
> DB_XRAY BMC$PKK_INSTANCE_STATS LCOTOLTS
> DB_XRAY BMC$PKK_THRESHOLDS LCOTOLTS
> PUR003 TABLE_STATS_HISTORY LCOTOLTS
> SQL_EXPLORER BMC_ISE_O_TEXT
> SQL_EXPLORER BMC_ISE_PLANTAB
> SQL_EXPLORER BMC_ISE_STATEMENTS
> SYS ATEMPTAB$
> SYSTEM DEF$_TEMP$LOB SYSTEM
>
>
> I don't know if line wrap will make this nearly impossible to read in
> your newsreader, but what I'm seeing is three tables owned by
> SQL_EXPLORER and one table owned by SYS have no tablespace. !?!?!
>
>
It's possible that a third party tool used a statement with a bind variable to create its own personal tablspace but run the statement with
nothing but spaces as a value for the variable:
SQL> r
1 create tablespace " " datafile
2* 'C:\foo.dbs'size 100 m
Tablespace created.
SQL> r
1* select tablespace_name||'x' from user_tablespaces
TABLESPACE_NAME||'X'
7 rows selected.
This is *obviously* extremely bad practice but is possible.
Try the query above in your environment and see what happens.
You might also want to check out the definitoon of tablespace_name in the ALL_TABLES data dictionary view:
> TABLESPACE_NAME
>
>
> VARCHAR2(30)
>
>
> Name of the tablespace containing the table; NULL for partitioned, temporary and index-organized tables
David Rolfe
Orinda Software
Dublin, Ireland
 |
 |