oracle查看表空间创建,Oracle表空间查看及其创建16k表空间

SELECT F.TABLESPACE_NAME,

(T.TOTAL_SPACE - F.FREE_SPACE) "USED (MB)",

F.FREE_SPACE "FREE (MB)",

T.TOTAL_SPACE "TOTAL (MB)",

(ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) ||  '% ' PER_FREE

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BLOCKS *

(SELECT VALUE / 1024

FROM V$PARAMETER

WHERE NAME = 'db_block_size') / 1024)) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) T

WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

select

a.a1 表空间名称,

c.c2 类型,

c.c3 区管理,

b.b2/1024/1024 表空间大小M,

(b.b2-a.a2)/1024/1024 已使用M,

substr((b.b2-a.a2)/b.b2*100,1,5) 利用率

from

(select  tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,

(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,

(select tablespace_name c1,contents c2,extent_management c3  from dba_tablespaces) c

where a.a1=b.b1 and c.c1=b.b1;

select

b.file_name 物理文件名,

b.tablespace_name 表空间,

b.bytes/1024/1024 大小M,

(b.bytes-sum(nvl(a.bytes,0)))/1024/1024  已使用M,

substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5)  利用率

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id

group by b.tablespace_name,b.file_name,b.bytes

order by b.tablespace_name

select tablespace_name, file_id,file_name,

round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

alter tablespace users add datafile '/data/house/house/users06.dbf' autoextend on next 100m maxsize unlimited

alter tablespace users add datafile '/data/house/house/users06.dbf' size 32761m

1.建立表空间

create tablespace  tab_house datafile 'D:/sosdb.dbf' size 500m autoextend on next 10m maxsize unlimited;

2.建立用户

DROP USER houseproperty cascade;

CREATE USER houseproperty IDENTIFIED BY houseproperty DEFAULT TABLESPACE  TAB_HOUSE TEMPORARY TABLESPACE TEMP;

GRANT connect,resource,dba to houseproperty;

3.还原数据

imp userid=system/oracle@house fromuser=houseproperty touser=houseproperty file=E:/data_backup/2011-04-20/houseproperty.dmp tablespaces=tab_house rows=y

4.建立16block表空间

alter system set db_16k_cache_size=16k;

shutdown immediate;

startup;

create tablespace tb_16k datafile 'D:/ORACLE/PRODUCT/10.2.0/ORADATA/HOUSE/tb_16k.DBF' size 2G autoextend on next  500m maxsize unlimited blocksize 16k;

5.某个表移动到新的表空间中：alter table htimage move tablespace tb_16k;