1.创建表，分区表，大对象字段，分别查询出它们是否为段对象，给出SQL演示的整个过程。
SQL
>
create
table tt1
as
select
*
from dba_objects
where object_id
<
1000;
Table created.
SQL
>
create
table tt2(id
number(
10), object_type
varchar2(
40)) partition
by list(object_type) (partition p_table
values(
'TABLE'), partition p_others
values (
default));
Table created.
SQL
>
insert
into tt2
value
select object_id,object_type
from dba_objects
where object_id
<
1000;
942 rows created.
SQL
>
create
table tt3(id
number(
10), object_type
varchar2(
40), description
clob);
Table created.
SQL
>
insert
into tt3
value
select object_id,object_type,object_name
from dba_objects
where object_id
<
1000;
942 rows created.
SQL
>
commit;
Commit complete.
SQL
>
set linesize
200
SQL > select segment_name,partition_name,tablespace_name,extents,blocks from user_segments where SEGMENT_NAME IN ( 'TT1', 'TT2');
SQL > select segment_name,partition_name,tablespace_name,extents,blocks from user_segments where SEGMENT_NAME IN ( 'TT1', 'TT2');
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME EXTENTS BLOCKS
--------------------------------------------------------------------------------- ------------------------------ ------------------------------ ---------- ----------
TT1 SYSTEM 2 16
TT2 P_OTHERS SYSTEM 1 8
TT2 P_TABLE SYSTEM 1 8
SQL
>
select s.segment_name,s.partition_name,s.tablespace_name,s.extents,s.blocks
from user_segments s, user_lobs l
where s.segment_name
=l.SEGMENT_NAME
and l.
TABLE_NAME
=
'TT3';
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME EXTENTS BLOCKS
--------------------------------------------------------------------------------- ------------------------------ ------------------------------ ---------- ----------
SYS_LOB0000075214C00003$$ SYSTEM 1 8
2.分别创建一个ASSM，MSSM管理的表空间。
SQL
>
show
parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL > alter system set db_create_file_dest = '/u01/oradata/orcl';
System altered.
SQL
>
show
parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/oradata/orcl
SQL
>
create
tablespace mssm segment
space management manual;
Tablespace created.
SQL
>
create
tablespace assm segment
space management auto;
Tablespace created.
3.验证段存储属性和所在表空间存储属性的关系，给出SQL演示，并得出结论。
SQL
>
SELECT TABLESPACE_NAME ,SEGMENT_SPACE_MANAGEMENT ,EXTENT_MANAGEMENT ALLOCATION_TYPE
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME
IN (
'USERS02',
'USER03');
TABLESPACE_NAME SEGMEN ALLOCATION
------------------------------ ------ ----------
USER03 MANUAL LOCAL
USERS02 AUTO LOCAL
SQL
>
CREATE
TABLE t1
TABLESPACE USERS02
AS
SELECT
*
FROM DBA_OBJECTS;
Table created.
SQL
>
CREATE
TABLE t2
TABLESPACE USER03
AS
SELECT
*
FROM DBA_OBJECTS;
Table created.
SQL
>
SELECT
TABLE_NAME ,TABLESPACE_NAME,PCT_FREE, PCT_USED,FREELISTS ,STATUS
FROM USER_TABLES
WHERE
TABLE_NAME
IN (
'USERS02',
'USER03');
T! USERS02 10 VALIDMU_TABLE
T2 USER03 10 40 1 VALID
4.SQL演示临时表的机制，并说明它的适用场合。
SQL
>
create
global
temporary
table tt1
on
commit
preserve
rows
as
select
*
from dba_objects;
Table created.
SQL
>
select
count(
*)
from tt1;
COUNT(*)
----------
72595
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle
@oracle ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 14 07:41:27 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba;
Connected.
SQL
>
select
count(
*)
from tt1;
COUNT(*)
----------
0
SQL
>
create
global
temporary
table tt2
on
commit
delete
rows
as
select
*
from dba_objects;
Table created.
SQL
>
select
count(
*)
from tt2;
COUNT(*)
----------
0
5.示例演示数据压缩的效果。
SQL
>
create
table tt1
as
select
*
from t1;
Table created.
Elapsed: 00:00:12.61
SQL
>
create
table tt2 compress
as
select
*
from t1;
Table created.
Elapsed: 00:00:07.32
SQL
>
create
table tt3
as
select
*
from t1
where
1
=
2;
Table created.
Elapsed: 00:00:00.05
SQL
>
create
table tt4
as
select
*
from t1
where
1
=
2;
Table created.
Elapsed: 00:00:00.04
SQL
>
insert
into
/
*
+ append
*
/ tt3
select
*
from tt1;
1000000 rows created.
Elapsed: 00:00:18.48
SQL
>
insert
into
/
*
+ append
*
/ tt4
select
*
from tt2;
1000000 rows created.
Elapsed: 00:00:37.04
SQL
>
exec show_space(
'TT3');
Free Blocks............................. 5
Total Blocks............................ 13, 312
Total Bytes............................. 109, 051, 904
Total MBytes............................ 104
Unused Blocks........................... 259
Unused Bytes............................ 2, 121, 728
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 138, 496
Last Used Block......................... 765
Free Blocks............................. 5
Total Blocks............................ 13, 312
Total Bytes............................. 109, 051, 904
Total MBytes............................ 104
Unused Blocks........................... 259
Unused Bytes............................ 2, 121, 728
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 138, 496
Last Used Block......................... 765
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
SQL
>
exec show_space(
'TT4');
Free Blocks............................. 5
Total Blocks............................ 13, 312
Total Bytes............................. 109, 051, 904
Total MBytes............................ 104
Unused Blocks........................... 259
Unused Bytes............................ 2, 121, 728
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 151, 808
Last Used Block......................... 765
Free Blocks............................. 5
Total Blocks............................ 13, 312
Total Bytes............................. 109, 051, 904
Total MBytes............................ 104
Unused Blocks........................... 259
Unused Bytes............................ 2, 121, 728
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 151, 808
Last Used Block......................... 765
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL
>
create
table tt5 compress
as
select
*
from t1
where
1
=
2;
Table created.
Elapsed: 00:00:00.06
SQL
>
create
table tt6 compress
as
select
*
from t1
where
1
=
2;
Table created.
Elapsed: 00:00:00.04
SQL
>
insert
into
/
*
+ append
*
/ tt5
select
*
from tt1;
1000000 rows created.
Elapsed: 00:00:23.30
SQL
>
insert
into
/
*
+ append
*
/ tt6
select
*
from tt2;
1000000 rows created.
Elapsed: 00:00:27.71
SQL
>
exec show_space(
'TT5');
Free Blocks............................. 5
Total Blocks............................ 12, 288
Total Bytes............................. 100, 663, 296
Total MBytes............................ 96
Unused Blocks........................... 544
Unused Bytes............................ 4, 456, 448
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 164, 096
Last Used Block......................... 480
Free Blocks............................. 5
Total Blocks............................ 12, 288
Total Bytes............................. 100, 663, 296
Total MBytes............................ 96
Unused Blocks........................... 544
Unused Bytes............................ 4, 456, 448
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 164, 096
Last Used Block......................... 480
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL
>
exec show_space(
'TT6');
Free Blocks............................. 5
Total Blocks............................ 12, 288
Total Bytes............................. 100, 663, 296
Total MBytes............................ 96
Unused Blocks........................... 544
Unused Bytes............................ 4, 456, 448
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 176, 384
Last Used Block......................... 480
Free Blocks............................. 5
Total Blocks............................ 12, 288
Total Bytes............................. 100, 663, 296
Total MBytes............................ 96
Unused Blocks........................... 544
Unused Bytes............................ 4, 456, 448
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 176, 384
Last Used Block......................... 480
1083
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
