|
本帖最后由 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;
复制代码
|
|