-
编辑 删除
AUDIT_SYS_OPERATIONS in 12c
12c里AUDIT_SYS_OPERATIONS参数的缺省值是TRUE, 以前的版本缺省值是FALSE导致的结果是audit_file_dest目录的aud文件变大，因为很多系统任务的sql写到了aud文件里，比如收集统计信息的自动任务db1_j000_16692_20170216234611354372143795.audThu Feb 16 23:46:11 2017 +08:00LENGTH : '758'ACTION :[515] 'sel...
-
编辑 删除
unused column
由于对大表dorp column很耗时，可以先用 set unused逻辑删除，等找到时间窗口再做物理删除，所以usused的列是不可以恢复的。create table t ( x int not null, y int not null, z int not null); insert into t values (1,2,3); commit; alter table t set unused column z;查看有unused列的表se...
-
编辑 删除
multi index on same column in 12c
12c可以在相同的列上建多个索引，但是只能有一个是"visible"的，应用这个功能，可以比较方便地把某个索引修改为分区索引来减少"热块"。create table t as select * from dba_objectswhere object_id is not null;create unique index t_ind1 on T ( object_id );alter table T add constraint T_PK primary key ( o...
-
编辑 删除
vector i/o and BATCHED TABLE ACCESS
从11g开始, Nested Loop Joins 的i/o操作有了优化, 叫做vector i/oOracle Database 11g introduces a new implementation for nested loop joins to reduce overall latency for physical I/O. When an index or a table block is not in the buffer cache and is needed to process the join, a physical I/O is req...
-
编辑 删除
abou AMM
在12cR2的文档中建议使用AMMAbout Memory Management (page 6-2)The memory structures that must be managed are the system global area(SGA) and the instance program global area (instance PGA). OracleDatabase supports various memory management methods, which arechosen by initialization parameter sett...
-
编辑 删除
using sql prfile to ignore hints
有些开发工程师喜欢在sql里加hint, 希望走自认为高效的执行计划，但往往事与愿违，加了hint的sql反而走了不好的执行计划，碰到这种情况，在不能修改代码的情况下，可以用sql profile来忽略这些hint,创建sql profile的hint就是
-
编辑 删除
sqlplus -L
用shell脚本执行sql，经常会需要写入账号，密码，例如:cat a.sqlselect * from dual;exitcat a.shsqlplus test/test @a.sqlsh a.shSQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 13 15:09:37 2017Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Ent...
-
编辑 删除
SQL Translator Profiles in 12c
SQL> select * from v$version;BANNER ...
-
编辑 删除
adjust scn by poke kcsgscn_
11g以后手工修改scn的手段：adjust_scn，_minimum_giga_scn都失效了但是可以修改内存变量来修改scnSYS@bbb>select current_scn from v$database; CURRENT_SCN-------------------- 146028890382SYS@bbb>oradebug setmypidStatement processed.S...
-
编辑 删除
where did the data of function:scn_to_timestamp come from?
scn_to_timestamp的scn和time的对应关系从何而来?是来自于系统表smon_scn_time吗?用10046去trace"select scn_to_timestamp(xxxxxxx) from dual"并没有发现访问smon_scn_time.但是对scn_to_timestamp来说，最小的有效的scn是"select min(scn_bas) from smon_scn_time"SYS@bbb>select min(scn_bas) SCN_BASE from smon...
-
编辑 删除
根据hint手工创建sqlprofile
TEST@bbb>select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 Production...
-
编辑 删除
VIPs Often Go Offline Unexpectedly and Relocate to Another Node
两个两节点的RAC数据库(OS:AIX 5300-11-04-1015, DB:10.2.0.4/10.2.0.5)，经常发生VIP漂移到另一个节点.$ crs_stat -tName Type Target State Host -----------------------------------...
-
编辑 删除
11g的v$sql_shared_memory视图没数据
查询视图v$sql_shared_memory没有数据返回SYS@db1>select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2....
-
编辑 删除
Hijacking user's password
在不知道数据库账号密码的情况下，如果需要以此账号登录，有两个方法:1.临时修改密码SYS@db1>select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2...
-
编辑 删除
partition table with Pending statistics
用Pending statistics测试sql的执行计划时,如果是分区表，Pending statistics无效.select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - Pro...
-
编辑 删除
Pstart and Pstop in sql plan with interval partition
select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for Lin...
-
编辑 删除
impdp with ORA-39083: Object type INDEX_STATISTICS failed to create with error
version(source,destination): 11.2.0.4impdp导入数据是报错:Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSORA-39083: Object type INDEX_STATISTICS fai...
-
编辑 删除
dbca in Silent Mode
version: 11.2.0.4DBCA response file sample: $ORACLE_HOME/assistants/dbca/dbca.rspdbca -silent -responseFile ./dbca.rspcommand line:dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -gdbname orcl -sid orcl -responseFile NO_VALUE \ -characterSet zhs16gbk \&nb...
-
编辑 删除
put table in read-only mode
SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS fo...
-
编辑 删除
Setting up Oracle Network Tracing on the Client and Server in 11g
客户端:修改$ORACLE_HOME/network/admin/sqlnet.oraTRACE_LEVEL_CLIENT=16TRACE_UNIQUE_CLIENT=ONTRACE_DIRECTORY_CLIENT=/tmp/net_client_traceDIAG_ADR_ENABLED=OFF服务端(不需要重启listener):修改$ORACLE_HOME/network/admin/sqlnet.oraTRACE_LEVEL_SERVER = 16TRACE_FILE_SERVER = SERVERTRACE_DIRECTORY...
