见过没有tablespace的table么？
SYS@single> select owner,table_name,tablespace_name from dba_tables where tablespace_name is null and rownum < 10;OWNER TABLE_NAME TABLESPACE_NAME
--------------- ------------------------------ ------------------------------
SYS ATEMPTAB$ (NULL)
SYS MAP_OBJECT (NULL)
SYS CLUSTER_DATABASES (NULL)
SYS CLUSTER_NODES (NULL)
SYS CLUSTER_INSTANCES (NULL)
SYS PSTUBTBL (NULL)
SYS WRI$_ADV_ASA_RECO_DATA (NULL)
SYS ODCI_SECOBJ$ (NULL)
SYS ODCI_WARNINGS$ (NULL)
9 rows selected. 本帖最后由 doni 于 2013-01-22 10:36 编辑
系统表,在内存中的,启动时创建 (? 可能) 本帖最后由 xuhainanjing 于 2013-01-22 08:27 编辑
SYS@single_copy> select OWNER,table_name,tablespace_name from dba_tables where owner='HR';
OWNER TABLE_NAME TABLESPACE_NAME
--------------- ------------------------------ ------------------------------
HR REGIONS EXAMPLE
HR LOCATIONS EXAMPLE
HR DEPARTMENTS EXAMPLE
HR JOBS EXAMPLE
HR EMPLOYEES EXAMPLE
HR JOB_HISTORY EXAMPLE
HR COUNTRIES --- <---这可不是系统表！
7 rows selected.
SYS@single_copy> select * from hr.countries;
CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ----------
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
CA Canada 2
CH Switzerland 1
CN China 3
DE Germany 1
DK Denmark 1
EG Egypt 4
FR France 1
HK HongKong 3
IL Israel 4
IN India 3
IT Italy 1
JP Japan 3
KW Kuwait 4
MX Mexico 2
NG Nigeria 4
NL Netherlands 1
SG Singapore 3
UK United Kingdom 1
US United States of America 2
ZM Zambia 4
ZW Zimbabwe 4
25 rows selected.
我看了一下,如果是分区表的话,tablespace就为NULL
不过LS这个表看上去不像是分区表,可能是其他什么情况,我查查资料看 对比了一下dba_objects,dba_segments
我的理解是这样的:
table不一定是物理的object(segment),
就比如分区表,表的分区才是真正的object(segment)
所以,对于分区表,表是逻辑上的概念,所以没有tablespace_name
分区才有 tablespace_name
其他的object也可能有类似的情况 http://docs.oracle.com/cd/B10501_01/server.920/a96536/ch2152.htm#1303953
TABLESPACE_NAME VARCHAR2(30) Name of the tablespace containing the table; NULL for partitioned, temporary and index-organized tables
对于我在2楼提到的内存中的表,还未找到相关资料 hr.countries 应该不是分区表 本帖最后由 doni 于 2013-01-22 10:43 编辑
回复 7# xuhainanjing
NULL for partitioned, temporary and index-organized tables
查询一下dba_objects,看一下object_type SYS@single_copy> select segment_name,segment_type,partition_name from dba_segments where owner='HR' and segment_name='COUNTRIES';
no rows selected
SYS@single_copy> select object_name,object_type from dba_objects where object_name='COUNTRIES' and owner='HR';
OBJECT_NAME OBJECT_TYPE
---------------------------------------- -------------------
COUNTRIES TABLE
本帖最后由 doni 于 2013-01-22 11:06 编辑
select object_name,object_type,TEMPORARY from dba_objects where object_name='COUNTRIES' and owner='HR';
看一下TEMPORARY这个字段的值
页:
[1]
2