第10章 管理表
10.2 ROWID
select empno,ename,rowid from emp;
10.3 Oracle 数据行的结构
10.4创建普通表
--登录
connect system/password
--在本地表空间users中为scott用户创建名为product的表
create table scott.product
(pcode number(8),
pname varchar2(30),
pdesc varchar2(100),
pprice number(6,2)
)
tablespace users;
--在数据字典管理的表空间了创建表
create table scott.supplier
(
scode number(8),
sname varchar2(25),
contact varchar2(15),
phone varchar2(15),
fax varchar2(15)
)
storage(initial 100k next 100k pctincrease 0
minextents 1
maxextents 8)
tablespace jinlian;
);
--查询结果
select table_name,tablespace_name,initial_extent
from dba_tables
where owner = 'SCOTT';
10.5创建临时表
create global temporary table
scott.emp_temp
on commit preserve rows /*--会话级--事务级on commit delete rows*/
as
select *
from scott.emp
where job not like 'SAL%';
--临时表不适用默认表空间而是使用临时段
col table_name for a15
col tablespace_name for a15
select table_name,tablespace_name,temporary
from dba_tables
where owner = 'SCOTT';
10.6 表的维护和管理
alter table scott.product
PCTFREE 20
PCTUSED 50;
--分配Extent
--1.
COL segment_name for a15
select segment_name,tablespace_name,extents
from dba_segments
where owner = 'SCOTT'
AND SEGMENT_NAME LIKE 'PRO%';
--2.增加extent
alter table scott.product
allocate extent;
--3.检查
COL segment_name for a15
select segment_name,tablespace_name,extents
from dba_segments
where owner = 'SCOTT'
AND SEGMENT_NAME LIKE 'PRO%';
10.7非分区表的重组
===============宝儿========================================
--SCOTT用户的数据存放在了那些表空间
select segment_name,tablespace_name,extents,blocks
from dba_segments
where owner = 'SCOTT';
--哪些数据段是表,哪些数据段是索引
col object_name for a10
SELECT OBJECT_ID,OBJECT_NAME,OBJECT_TYPE,STATUS,CREATED
FROM DBA_OBJECTS
WHERE OWNER = 'SCOTT';
--索引基于哪些表
COL INDEX_NAME FOR A10
COL TABLE_NAME FOR A12
select index_name,table_name,tablespace_name,status
from dba_indexes
where owner = 'SCOTT';
--将scott用户下所有段从系统表空间移出
alter table scott.emp
move tablespace users;
--验证索引是否这的有效
--1.
col object_name for a10
SELECT OBJECT_ID,OBJECT_NAME,OBJECT_TYPE,STATUS,CREATED
FROM DBA_OBJECTS
WHERE OWNER = 'SCOTT';
--2.
COL INDEX_NAME FOR A10
COL TABLE_NAME FOR A12
select index_name,table_name,tablespace_name,status
from dba_indexes
where owner = 'SCOTT';
--重建索引
alter index scott.pk_emp rebuild
tablespace jinlian_index;
==================================================
10.8列的维护
10.9
第11章 索引的管理和维护
--查询索引信息
col index_name for a10
col table_name for a10
col tablespace_name for a10
col index_type for a8
select index_name,table_name,tablespace_name,
index_type,uniqueness,status
from dba_indexes
where owner = 'SCOTT';
--查询索引列信息
col column_name for a10
col index_owner for a11
select index_name,table_name,column_name,
index_owner,table_owner
from dba_ind_columns
where table_owner = 'SCOTT';
--创建索引
create index scott.emp_ename_idx
on scott.emp(ename)
PCTFREE 20
STORAGE(INITIAL 100K NEXT 100k PCTINCREASE 0 MAXEXTENTS 100)
TABLESPACE USERS;
--查询索引的相关存储参数设置
select index_name,pct_free,pct_increase,initial_extent,next_extent
from dba_indexes
where owner = 'SCOTT';
--重建索引
alter index scott.emp_ename_idx rebuild
pctfree 40
storage (next 300k);
--查看索引的磁盘分配情况
COL SEGMENT_NAME FOR A13
select segment_name,segment_type,tablespace_name,extents
from dba_segments
where owner = 'SCOTT'
AND segment_type = 'INDEX';
--给索引怎加一个extent的磁盘空间
alter index scott.emp_ename_idx
allocate extent
--收回extent
alter index scott.emp_ename_idx deallocate unused;
--合并碎片
alter index scott.emp_ename_idx coalesce;
--online 选项
例如:alter index xx rebuild online;
11.8 标识索引的使用情况
--开启监督
alter index scott.emp_ename_idx monitoring usage;
--查看使用情况
select * from v$object_usage;
--关闭监督
alter index scott.emp_ename_idx nomonitoring usage;
--
drop index xxx
阅读(1598) | 评论(0) | 转发(0) |