|
- select count(*) from hr.t04209_uname;
- select count(distinct uvalue) from hr.t04209_uname;
- select t.num_rows from cdb_tables t
- where t.owner='HR' and t.table_name='T04209_UNAME';
-
- select * from cdb_tab_col_statistics tcs where tcs.owner='HR'
- and tcs.table_name='T04209_UNAME';
-
- begin
- dbms_stats.gather_table_stats('HR','T04209_UNAME');
- end;
- begin
- dbms_stats.gather_table_stats('HR','T04209_UNAME',
- estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,
- method_opt => 'for all columns size auto'
- );
- end;
- begin
- dbms_stats.gather_table_stats('HR','T04209_UNAME',
- estimate_percent => 100,
- method_opt => 'for columns uvalue size 254 for all columns size auto'
- );
- end;
- select * from cdb_tab_col_statistics tcs where tcs.owner='HR'
- and tcs.table_name='T04209_UNAME';
-
- select * from CDB_TAB_HISTOGRAMS cth
- where cth.owner='HR' and cth.table_name='T04209_UNAME';
-
-
- select i.index_name, i.last_analyzed from cdb_indexes i where i.owner='HR'
- and i.table_name='T04209_UNAME';
-
-
复制代码
试错(begin
dbms_stats.gather_table_stats('HR','T04209_UNAME',
estimate_percent => 100,
method_opt => 'for columns uvalue size 1 for all columns size auto'
);
end;)
- SQL> select * from t04209_uname where uvalue=99999;
- 99000 rows selected.
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 82881757
- --------------------------------------------------------------------------------
- ---------------------------
- | Id | Operation | Name | Rows | Byte
- s | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------
- ---------------------------
- | 0 | SELECT STATEMENT | | 100 | 120
- 0 | 2 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T04209_UNAME | 100 | 120
- 0 | 2 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | I04209_UNAME_UVALUE | 100 |
- | 1 (0)| 00:00:01 |
- --------------------------------------------------------------------------------
- ---------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("UVALUE"=99999)
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 6836 consistent gets
- 0 physical reads
- 0 redo size
- 2437959 bytes sent via SQL*Net to client
- 73140 bytes received via SQL*Net from client
- 6601 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 99000 rows processed
- SQL>
复制代码 个性化:
- select dbms_stats.get_prefs(pname => 'STALE_PERCENT') from dual;
- select * from cdb_tab_stat_prefs tsp where tsp.owner='HR'
- and tsp.table_name='EMPLOYEES';
-
- select dbms_stats.get_prefs(pname => 'STALE_PERCENT',
- ownname => 'HR',
- tabname => 'EMPLOYEES') from dual;
-
- begin
- dbms_stats.set_table_prefs('HR','EMPLOYEES','STALE_PERCENT','13');
- end;
复制代码- SQL> conn sys/oracle_4U@pdb2 as sysdba
- Connected.
- SQL> show parameter optimizer
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- optimizer_adaptive_features boolean TRUE
- optimizer_adaptive_reporting_only boolean FALSE
- optimizer_capture_sql_plan_baselines boolean FALSE
- optimizer_dynamic_sampling integer 2
- optimizer_features_enable string 12.1.0.2
- optimizer_index_caching integer 0
- optimizer_index_cost_adj integer 100
- optimizer_inmemory_aware boolean TRUE
- optimizer_mode string ALL_ROWS
- optimizer_secure_view_merging boolean TRUE
- optimizer_use_invisible_indexes boolean FALSE
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- optimizer_use_pending_statistics boolean FALSE
- optimizer_use_sql_plan_baselines boolean FALSE
- SQL> conn hr/oracle_4U@pdb2
- Connected.
- SQL> alter session set optimizer_use_pending_statistics=true;
- Session altered.
- SQL>
复制代码
- select t.num_rows from cdb_tables t
- where t.owner='HR' and t.table_name='T04209_UNAME';
- select * from cdb_tab_pending_stats tps
- where tps.owner='HR' and tps.table_name='T04209_UNAME';
- select dbms_stats.get_prefs(pname => 'PUBLISH') from dual;
- select * from cdb_tab_stat_prefs tsp where tsp.owner='HR'
- and tsp.table_name='EMPLOYEES';
-
- select dbms_stats.get_prefs(pname => 'PUBLISH',
- ownname => 'HR',
- tabname => 'EMPLOYEES') from dual;
- begin
- dbms_stats.set_table_prefs('HR','T04209_UNAME','PUBLISH','FALSE');
- end;
- begin
- dbms_stats.gather_table_stats('HR','T04209_UNAME',
- estimate_percent => 100,
- method_opt => 'for columns uvalue size 254 for all columns size auto'
- );
- end;
- select * from cdb_tab_pending_stats tps
- where tps.owner='HR' and tps.table_name='T04209_UNAME';
-
- begin
- dbms_stats.publish_pending_stats(ownname => 'HR',
- tabname => 'T04209_UNAME');
- end;
-
复制代码
代码KEEP:
- begin
- dbms_shared_pool.keep('HR.PROC1');
- end;
- select oc.kept from v_$db_object_cache oc
- where oc.owner='HR' and oc.name='PROC1';
复制代码
SAA怎么用:
SQL Access Advisor的捷径:
- create table hr.tbig as select * from cdb_source;
- begin
- dbms_advisor.quick_tune(advisor_name => dbms_advisor.SQLACCESS_ADVISOR,
- task_name => 'MY1',
- attr1 => 'select count(*) from hr.tbig group by line',
- template =>dbms_advisor.SQLACCESS_WAREHOUSE);
- end;
-
-
- begin
- dbms_advisor.quick_tune(advisor_name => dbms_advisor.SQLACCESS_ADVISOR,
- task_name => 'MY2',
- attr1 => 'select count(*) from hr.tbig group by line',
- template =>dbms_advisor.SQLACCESS_OLTP);
- end;
复制代码
行迁移,行链接:
- [root@station90 ~]# su - oracle
- [oracle@station90 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.1.0 Production on Sat May 30 15:13:48 2015
- Copyright (c) 1982, 2009, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- ERROR:
- ORA-28002: the password will expire within 5 days
- Connected.
- SQL> create table t05318_chain(a varchar2(2000), b varchar2(2000), c varchar2(2000), d varchar2(2000));
- Table created.
- 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)
- ------------------------------------
- 559
- 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
- Table created.
- 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)
- ------------------------------------
- 559
- 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 AAASOPAAEAAAAIvAAA 30-MAY-15
- SQL> truncate table CHAINED_ROWS;
- Table truncated.
- SQL> select * from CHAINED_ROWS;
- no rows 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 AAASOPAAEAAAAIvAAA 30-MAY-15
- SQL> truncate table CHAINED_ROWS;
- Table truncated.
- SQL> select * from CHAINED_ROWS;
- no rows selected
- SQL> alter table t05318_chain move tablspace tbs16k;
- alter table t05318_chain move tablspace tbs16k
- *
- ERROR at line 1:
- ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
- SQL> alter table t05318_chain move tablespace tbs16k;
- Table altered.
- 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.
- SQL> show user
- USER is "HR"
- begin
- for i in 1..733
- loop
- insert into t05318_migrate values('A');
- end loop;
- 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(*)
- ------------------------------------ ----------
- 559 733
- 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(*)
- ------------------------------------ ----------
- 555 100
- 559 733
- 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(*)
- ------------------------------------ ----------
- 559 733
- 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');
- ERROR:
- ORA-00972: identifier is too long
- 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 AAASOUAAEAAAAIvAAA 30-MAY-15
- 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(*)
- ------------------------------------ ----------
- 559 733
- 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(*)
- ------------------------------------ ----------
- 588 185
- 587 548
- SQL> alter table T05318_MIGRATE pctfree 20;
- Table altered.
- 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(*)
- ------------------------------------ ----------
- 555 402
- 556 331
- SQL> truncate table chained_rows;
- Table truncated.
- SQL> analyze table T05318_MIGRATE list chained rows;
- Table analyzed.
- SQL> select * from chained_rows;
- no rows selected
- SQL>
复制代码
空间不够的问题:
- SQL> conn hr/oracle_4U@pdb2
- Connected.
- SQL> create table t1 ( a number ) storage ( extent 11M ) tablespace tbs1;
- create table t1 ( a number ) storage ( extent 11M ) tablespace tbs1
- *
- ERROR at line 1:
- ORA-02143: invalid STORAGE option
- SQL> create table t1 ( a number ) storage ( initial extent 11M ) tablespace tbs1;
- create table t1 ( a number ) storage ( initial extent 11M ) tablespace tbs1
- *
- ERROR at line 1:
- ORA-02218: invalid INITIAL storage option value
- SQL> create table t1 ( a number ) storage ( initial 11M ) tablespace tbs1;
- Table created.
- SQL> insert into t1 values (1) ;
- insert into t1 values (1)
- *
- ERROR at line 1:
- ORA-01659: unable to allocate MINEXTENTS beyond 2 in tablespace TBS1
- SQL>
复制代码 SYS:
HR:
- SQL> insert into t1 values (1) timeout 5 ;
- insert into t1 values (1) timeout 5
- *
- ERROR at line 1:
- ORA-00933: SQL command not properly ended
- SQL> insert into t1 values (1) ;
- insert into t1 values (1)
- *
- ERROR at line 1:
- ORA-30032: the suspended (resumable) statement has timed out
- ORA-01659: unable to allocate MINEXTENTS beyond 2 in tablespace TBS1
- SQL>
复制代码 自治事务加上动态SQL的悬挂触发器:
- grant resumable to hr;
- select file_name, bytes, bytes/1024/1024 from cdb_data_files
- where tablespace_name='TBS1';
-
- create or replace trigger trgocp11_limit
- after suspend
- on database
- declare
- v_size number;
- pragma AUTONOMOUS_TRANSACTION;
- begin
- select BYTES into v_size from cdb_data_files
- where FILE_name='/u01/app/oracle/oradata/cdb2/pdb2_1/tbs1.dbf';
-
- execute immediate 'alter database datafile ''/u01/app/oracle/oradata/cdb2/pdb2_1/tbs1.dbf'' resize
- '||to_char(v_size+5242880);
- commit;
- end;
- select BYTES from cdb_data_files
- where FILE_name='/u01/app/oracle/oradata/cdb2/pdb2_1/tbs1.dbf';
复制代码
|
|