第54/55次：2015-09-04 星期五上下午

botang · 发表于 2015-9-4 21:21:09

本帖最后由 botang 于 2015-9-5 21:46 编辑

上完1Z0-053第13章 (52-48)
上完1Z0-053第18章 (52-49)

2015-09-04a.sql:

SQL> show user
USER is "SYS"
SQL> conn hr/oracle_4U
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> create table t05318_chain(a varchar2(2000), b varchar2(2000), c varchar2(2000), d varchar2(2000));
SQL> set echo on
SQL> set feedback on
SQL> insert into t05318_chain(a) values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
1 row created.
SQL> commit;
Commit complete.
SQL> update t05318_chain set b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05318_chain set c='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
1 row updated.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_block_number ( rowid ) from t05318_chain;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
651
1 row selected.
SQL> select * from CHAINED_ROWS;
select * from CHAINED_ROWS
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> @?/rdbms/admin/utlchain.sql
SQL> rem
SQL> rem $Header: utlchain.sql 07-may-96.19:40:01 sbasu Exp $
SQL> rem
SQL> Rem Copyright (c) 1990, 1995, 1996, 1998 by Oracle Corporation
SQL> Rem NAME
SQL> REM UTLCHAIN.SQL
SQL> Rem FUNCTION
SQL> Rem Creates the default table for storing the output of the
SQL> Rem analyze list chained rows command
SQL> Rem NOTES
SQL> Rem MODIFIED
SQL> Rem syeung 06/17/98 - add subpartition_name
SQL> Rem mmonajje 05/21/96 - Replace timestamp col name with analyze_timestam
SQL> Rem sbasu 05/07/96 - Remove echo setting
SQL> Rem ssamu 08/14/95 - merge PTI with Objects
SQL> Rem ssamu 07/24/95 - add field for partition name
SQL> Rem glumpkin 10/19/92 - Renamed from CHAINROW.SQL
SQL> Rem ggatlin 03/09/92 - add set echo on
SQL> Rem rlim 04/29/91 - change char to varchar2
SQL> Rem Klein 01/10/91 - add owner name for chained rows
SQL> Rem Klein 12/04/90 - Creation
SQL> Rem
SQL>
SQL> create table CHAINED_ROWS (
2 owner_name varchar2(30),
3 table_name varchar2(30),
4 cluster_name varchar2(30),
5 partition_name varchar2(30),
6 subpartition_name varchar2(30),
7 head_rowid rowid,
8 analyze_timestamp date
9 );
Table created.
SQL>
SQL> set linesize 1000
SQL> select * from CHAINED_ROWS;
no rows selected
SQL> analyze table t05318_chain list chained rows;
Table analyzed.
SQL> select * from CHAINED_ROWS;
no rows selected
SQL> update t05318_chain set d='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
1 row updated.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_block_number ( rowid ) from t05318_chain;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
651
1 row selected.
SQL> analyze table t05318_chain list chained rows;
Table analyzed.
SQL> select * from CHAINED_ROWS;
OWNER_NAME TABLE_NAME CLUSTER_NAME PARTITION_NAME SUBPARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------
HR T05318_CHAIN N/A AAASNnAAEAAAAKLAAA 04-SEP-15
1 row selected.
SQL> show parameter db_32k
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> show parameter db_32k
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_32k_cache_size big integer 0
SQL> alter system set db_32k_cache_size=1M ;
System altered.
SQL> show parameter db_32k_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_32k_cache_size big integer 64M
SQL> create tablespace tbs32k datafile size 20M blocksize 32K;
Tablespace created.
SQL> conn hr/oracle_4U
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> alter table t05318_chain move tablespace tbs32k;
Table altered.
SQL> select * from CHAINED_ROWS;
OWNER_NAME TABLE_NAME CLUSTER_NAME PARTITION_NAME SUBPARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------
HR T05318_CHAIN N/A AAASNnAAEAAAAKLAAA 04-SEP-15
1 row selected.
SQL> truncate table CHAINED_ROWS;
Table truncated.
SQL> analyze table t05318_chain list chained rows;
Table analyzed.
SQL> select * from CHAINED_ROWS;
no rows selected
SQL> create table t05318_migrate( a varchar2(2000)) pctfree 0;
Table created.
begin
for i in 1..733
loop
insert into t05318_migrate values('A');
end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select dbms_rowid.rowid_block_number(rowid) , count(*) from t05318_migrate
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
651 733
1 row selected.
SQL> begin
2 for i in 1..100
3 loop
4 insert into t05318_migrate values('B');
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select dbms_rowid.rowid_block_number(rowid) , count(*) from t05318_migrate
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
651 733
652 100
2 rows selected.
SQL> delete from t05318_migrate where A='B';
100 rows deleted.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_block_number(rowid) , count(*) from t05318_migrate
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
651 733
1 row selected.
SQL> analyze table t05318_migrate list chained rows;
Table analyzed.
SQL> select * from CHAINED_ROWS;
no rows selected
SQL> update t05318_migrate set a='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' where rownum=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> analyze table t05318_migrate list chained rows;
Table analyzed.
SQL> select * from CHAINED_ROWS;
OWNER_NAME TABLE_NAME CLUSTER_NAME PARTITION_NAME SUBPARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------
HR T05318_MIGRATE N/A AAASNrAAEAAAAKLAAA 04-SEP-15
1 row selected.
SQL> select dbms_rowid.rowid_block_number(rowid) , count(*) from t05318_migrate
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
651 733
1 row selected.
SQL> select tablespace_name from user_tables where table_name='T05318_MIGRATE';
TABLESPACE_NAME
------------------------------
USERS
1 row selected.
SQL> alter table t05318_migrate move tablespace users;
Table altered.
SQL> select dbms_rowid.rowid_block_number(rowid) , count(*) from t05318_migrate
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
667 730
668 3
2 rows selected.
SQL> truncate table chained_rows;
Table truncated.
SQL> select * from chained_rows;
no rows selected
SQL> analyze table t05318_migrate list chained rows;
Table analyzed.
SQL> select * from chained_rows;
no rows selected
SQL> select pct_free from user_tables where table_name='T05318_MIGRATE';
PCT_FREE
----------
0
1 row selected.
SQL> alter table t05318_migrate pctfree 20 ;
Table altered.
SQL> select pct_free from user_tables where table_name='T05318_MIGRATE';
PCT_FREE
----------
20
1 row selected.
SQL> alter table t05318_migrate move tablespace users;
Table altered.
SQL> select dbms_rowid.rowid_block_number(rowid) , count(*) from t05318_migrate
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
651 584
652 149
2 rows selected.
SQL>

复制代码

2015-09-04b.sql:

select * from dba_segments s where s.owner='HR' and s.segment_name='T05318_DEFER';
select * from dba_segments s where s.owner='HR' and s.segment_name='T05318_NODEFER';
select * from dba_segments s where s.owner='HR' and s.segment_name='T05318_CMD';
select * from dba_segments s where s.owner='HR' and s.segment_name='T05318_PART';
select * from dba_segments s where s.owner='HR' and s.segment_name='I05318_A';
select * from dba_segments s where s.owner='HR' and s.segment_name='IBIG';
select * from dba_indexes i where i.owner='HR' and i.index_name='IBIG';
select * from dba_segments s where s.owner='HR' and s.segment_name like '%DEMO2';
select i.index_name , decode( ip.status,null,i.status,ip.status),
i.partitioned,ip.partition_name
from dba_ind_partitions ip , dba_indexes i
where ip.index_name(+)=i.index_name
and i.table_name='DEMO2' and i.table_owner='HR';

复制代码

compress.sql:

Oracle11gR2的表压缩特性通过压缩表中的数据以减少对空间的占用。Oracle11gR2支持三种不同的压缩方式：
· 支持直接路径加载的Basic压缩方式（10x）
· 支持针对所有DML操作的OLTP压缩方式（2-4x）
· Exadata专属的Hybrid columnar compression压缩方式
Oracle公司建议我们采用以上三种不同的压缩方式，针对特定的应用场合来进行表压缩。如果该表包含大量冗长的和重复的值，通过被压缩可以减少该表对磁盘空间的占用和对SGA中数据库缓冲区缓存的占用。
先从图形界面入手，我们发现在新建表空间时Oracle11gR2的界面出现压缩选项：
在下面各个部分所有的实验中我们都创建两个表：一个表在TBS_NOCOMPRESSION表空间，另一个表在有对应默认压缩选项的表空间。最后我们把在 TBS_NOCOMPRESSION表空间上的那个表转换成对应压缩选项表。通过对比实验来弄清Oracle11gR2这方面的新特性。
--sys--
create tablespace tbs_nocompression datafile size 10M autoextend on;
create tablespace tbs_basic datafile size 10M autoextend on default compress basic;
create tablespace tbs_oltp datafile size 10M autoextend on default compress for oltp;
create tablespace tbs_query datafile size 10M autoextend on default compress for query;
create tablespace tbs_archive datafile size 10M autoextend on default compress for archive;
select t.tablespace_name, t.def_tab_compression, t.compress_for from dba_tablespaces t
where t.tablespace_name in ('TBS_NOCOMPRESSION',
'TBS_BASIC', 'TBS_OLTP',
'TBS_QUERY',
'TBS_ARCHIVE');
--hr--
create table t_nocompression (a varchar2(200)) tablespace tbs_nocompression;
create table t_basic (a varchar2(200)) tablespace tbs_basic;
create table t_oltp (a varchar2(200)) tablespace tbs_oltp;
--hr-error--
create table t_query (a varchar2(200)) tablespace tbs_query;
create table t_archive (a varchar2(200)) tablespace tbs_archive;
--hr--
begin
for i in 1..400
loop
insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
begin
for i in 1..400
loop
insert into t_basic values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
begin
for i in 1..400
loop
insert into t_oltp values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP');
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
select count(*) , substr(rowid, 10, 6 ) from hr.t_oltp group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid)
from hr.T_OLTP group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid);
--hr--
create table t_basic2 (a varchar2(200)) tablespace tbs_nocompression compress;
create table t_oltp2 (a varchar2(200)) tablespace tbs_nocompression compress for oltp;
--hr-error--
create table t_query2 (a varchar2(200)) tablespace tbs_nocompression compress for query;
create table t_archive2 (a varchar2(200)) tablespace tbs_nocompression compress for archive;
--hr--
select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP', 'T_BASIC2','T_OLTP2' );
--hr--
begin
for i in 1..400
loop
insert into t_basic2 values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
begin
for i in 1..400
loop
insert into t_oltp2 values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic2 group by substr(rowid, 10, 6 );
select count(*) , substr(rowid, 10, 6 ) from hr.t_oltp2 group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid)
from hr.T_BASIC2 group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid);
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid)
from hr.T_OLTP2 group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid);
--hr--
alter table t_nocompression compress for oltp;
select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP', 'T_BASIC2','T_OLTP2' );
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--hr--
begin
for i in 1..400
loop
insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--hr--
alter table t_nocompression move tablespace tbs_nocompression;
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--hr--
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
insert /*+ append */ into t_basic select * from t_basic;
commit;
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
--hr--
alter table t_basic move tablespace TBS_NOCOMPRESSION;
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
--sys-evolution--
drop table hr.t_basic_big;
create table hr.t_basic_big compress as select * from dba_source;
--sys-advisor--
declare
v_blkcnt_cmp number;
v_blkcnt_uncmp number;
v_row_cmp number;
v_row_uncmp number;
v_cmp_ratio number;
v_comptype_str varchar2(200);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO(scratchtbsname => 'USERS',
ownname =>'HR',
tabname =>'T_BASIC_BIG',
partname =>null,
comptype => 2,
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp =>v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str =>v_comptype_str);
DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || v_blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || v_blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || v_row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || v_row_uncmp);
DBMS_OUTPUT.PUT_LINE('ratio: '||v_cmp_ratio);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || v_comptype_str);
end;
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid)
from hr.T_BASIC_BIG group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid);
--hr--
create table t_basic_col( a number , b varchar2(20)) compress ;
insert into t_basic_col values ( 1,'A') ;
commit;
create table t_oltp_col( a number , b varchar2(20)) compress for oltp;
insert into t_oltp_col values ( 1,'A') ;
commit;
select * from t_basic_col;
select * from t_oltp_col;
alter table t_basic_col drop column b;
alter table t_basic_col drop ( b);
alter table t_oltp_col drop column b;
select * from t_oltp_col;

复制代码

suspend.sql:

select count(*) , dbms_compression.get_compression_type(ownname => 'HR',
tabname => 'T_BASIC_BIG',row_id => rowid)
from hr.T_BASIC_BIG
group by dbms_compression.get_compression_type(ownname => 'HR',
tabname => 'T_BASIC_BIG',row_id => rowid);
declare
v_blkcnt_cmp number;
v_blkcnt_uncmp number;
v_row_cmp number;
v_row_uncmp number;
v_cmp_ratio number;
v_comptype_str varchar2(200);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO(scratchtbsname => 'USERS',
ownname =>'HR',
tabname =>'T_BASIC_BIG',
partname =>null,
comptype => 1,
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp =>v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str =>v_comptype_str);
DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || v_blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || v_blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || v_row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || v_row_uncmp);
DBMS_OUTPUT.PUT_LINE('ratio: '||v_cmp_ratio);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || v_comptype_str);
end;
alter table "HR"."TBIG" shrink space;
select bytes/1024/1024 from dba_segments s where s.owner='HR' and s.segment_name='TBIG';
select count(*) from hr.tbig;
select * from v_$session s where s.BLOCKING_SESSION is not null;
grant resumable to hr;
create tablespace tbs05318_small datafile size 5M autoextend off;
select bytes/1024/1024 from dba_data_files df
where df.tablespace_name='TBS05318_SMALL';
CREATE
OR REPLACE TRIGGER TRG_SUSPEND
AFTER SUSPEND
ON DATABASE
declare
v_size number;
pragma AUTONOMOUS_TRANSACTION;
begin
select bytes into v_size from dba_data_files
where file_name='+DATA/orcl/datafile/tbs05318_small.271.889552355';
v_size := v_size + 1048576;
execute immediate 'alter database datafile
''+DATA/orcl/datafile/tbs05318_small.271.889552355'' resize '||v_size;
commit;
end;

复制代码