oracle 实验4

1、创建一个本地管理表空间tbs1，区自动分配，段空间自动管理，数据文件初  tablespace_name;

始大小10M，自动扩展，每次2M，最大100M                     
create tablespace tbs1                                                

datafile 'tbs101.dbf' size 10M autoextend on next 2M maxsize 100M      

extent management local

autoallocate                                                            

segment space management auto;                                    

 【解释】                                                                   

autoextend on  表示自动扩展；                                                 

next 2M 表示每次扩2M                                                         

maxsize 100M 表示数据文件最大可达100M                                            

extent management local 表示本地管理表空间；默认                               

autoallocate 表示区大小自动分配；默认                                            

segment space management auto 表示段空间自动管理。默认 

 

2、创建一个本地管理表空间tbs2，区统一大小为64K，段空间手动管理，数据 

文件初始大小10M，自动扩展，每次2M，最大无限制                                               

create tablespace tbs2                                              

datafile 'tbs201.dbf' size 10M autoextend on next 2M maxsize unlimited

uniform size 64K                                                   

segment space management manual;

 

3、为表空间tbs1 增加数据文件tbs102.dbf,    初始大小10M，自动扩展，每次2M，

最大100M                                                                     

alter tablespace tbs1                                                  

add datafile 'tbs102.dbf' size 10M autoextend on next 2M maxsize 100M;

 

4、查询表空间剩余空间

select   tablespace_name, sum(bytes)/1024/1024||'M'    from   dba_free_space   group   by  tablespace_name;

TABLESPACE_NAME                SUM(BYTES)/1024/1024||'M'
------------------------------ -----------------------------------------
UNDOTBS1                       .125M
SYSAUX                         2.75M
TBS1                           19.875M
USERS                          1.75M
SYSTEM                         5.8125M
EXAMPLE                        22.3125M
LEARNING                       99.9375M
TBS02                          9.9375M

 

 5、查询表空间大小（所有数据文件大小和）

        select  tablespace_name,sum(bytes)/1024/1024||'M'  from  dba_data_files  group  by    tablespace_name

 

TABLESPACE_NAME                SUM(BYTES)/1024/1024||'M'
------------------------------ -----------------------------------------
SYSAUX                         240M
UNDOTBS1                       35M
TBS1                           20M
USERS                          5M
SYSTEM                         480M
EXAMPLE                        100M
LEARNING                       100M
TBS02                          10M

 

 

1、在表空间tbs1 上创建一个表table1(name varchar2(4000))，然后插入总量大 约100K 数据。

insert into table1 values (lpad('a',2048,'s'))

2、查询表table1  占用区的数量和块的数量

 select  sum(blocks)*8/1024 from dba_extents where segment_name = 'TABLE2';

SUM(BLOCKS)*8/1024(占用大小)
------------------
                 6

select count(extent_id) ,sum(blocks) from dba_extents where segment_name='TABLE2'

3、查询表空间tbs1 剩余空间大小
select * from dba_free_space where tablespace_name = 'TBS1';

 select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='TBS1

SUM(BYTES)/1024/1024 (剩余大小)
--------------------
             13.8125

4、查询表空间tbs1 的空间利用率

【相关视图】

dba_segments

dba_extents

dba_tablespaces

dba_data_files

 dba_free_space

 【要求】写作业本上。