第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;
==================================================
阅读(1413) | 评论(0) | 转发(0) |