-
编辑 删除
sql执行计划是否改变
select SQL_ID,SQL_PLAN_HASH_VALUE,SQL_EXEC_START from V$ACTIVE_SESSION_HISTORY;select PLAN_HASH_VALUE,FIRST_LOAD_TIME from v$sql where sql_id='bksyj647thx15'select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMPfrom dba_hist_sql_plan where SQL_ID=...
-
编辑 删除
记录数据库所有ddl操作
NAME TYPE VALUE------------------------------------ ----------- ------------------------------enable_ddl_logging boolean FALSE通过ash查询历史执行sql：V$ACTIVE_SESSION_HISTORYDBA_HIST_SQLTEXTselect s.*,b.SQL_TEXT from (select a.SAMPLE_TIME,a.sql_id &nb...
-
编辑 删除
ora 脚本
#!/bin/bashif [ "$LOGNAME" = "oracle" ]; then SQLPLUS_CMD="/ as sysdba";else SQLPLUS_CMD="/ as sysdba";ficase $1 in si) if [ "$LOGNAME" = "oracle" ]; then sqlplus "/ as sysdba" ...
-
编辑 删除
ora 脚本
#!/bin/bashif [ "$LOGNAME" = "oracle" ]; then SQLPLUS_CMD="/ as sysdba";else SQLPLUS_CMD="/ as sysdba";ficase $1 in si) if [ "$LOGNAME" = "oracle" ]; then sqlplus "/ as sysdba" ...
-
编辑 删除
How to Resize a Datafile (文档 ID 1029252.6)
In this DocumentPurposeScopeDetails =============================1. Increase Datafile Size============================= ...
-
编辑 删除
How to Resolve ORA-03297
APPLIES TO:Oracle Database - Enterprise Edition - Version 8.1.5.0 and laterInformation in this document applies to any platform.GOALIn a number of cases it is desired to shrink an oversized datafile. The challenge is then up to find to which size the datafile can be shrunk? Using the ...
-
编辑 删除
段调优顾问回收大表的分配空间和高水位线(转载)
--通过段调优顾问回收大表的分配空间和高水位线 --创建用户 SQL> create tablespace hzqtbs datafile '/u01/app/oracle/oradata/prod/hzqtbs.dbf' size 30m;Tablespace created.SQL> create user hzq identified by hzq default tablespace hzqtbs quota unlimited on hzqtbs account unlock; User created.SQL> gran...
-
编辑 删除
Buffer Cache Hit Ratio
rem rem This script uses PL/SQL. rem le hit ratio rem col global_name for A20 variable d number variable c number variable r number begin select value into :c from v$sysstat ...
-
编辑 删除
Why is no space released after an ALTER TABLE ... SHRINK?
APPLIES TO:Oracle Database - Enterprise Edition - Version 10.1.0.2 and laterInformation in this document applies to any platform.SYMPTOMSAn ALTER TABLE ... SHRINK has been done and no space seems to have been releasedCAUSEMisunderstanding of how Oracle Storage worksSO...
-
编辑 删除
Running the Segment Advisor Manually
--回收表空间alter table test enable row movement; --开启行迁移功能。alter table test shrink space compact;--(可以在压缩期间进行DML操作和查询) ,收缩表，不会降低hwmalter table test shrink space; --( 调整HWM时将阻塞DML操作),收缩表，并且降低hwmalter table test_idx shrink space cascade;--收缩表并降低...
