Xing AiMing's Home Page – 邢爱明的个人网站

10g关于shutdown的官方文档：Shutting Down a Database 使用下面任意一种SHUTDOWN方法后： No new connections are allowed, nor are new transactions allowed to be started. SHUTDOWN = SHUTDOWN NORMAL Before the database is shut down, the database waits for all currently connected users to disconnect from the database. SHUTDOWN IMMEDIATE Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this [...]

概念说明 行链接：记录在插入的时候由于长度大于一个数据块的大小，被拆分放到多个数据块中。特别是当表中含有CLOB或BLOG字段的时候容易发生这样的情况。   行迁移：记录在更新时，在原有的数据块中找不到足够的空间，此时记录的内容保存到另外数据块，原数据块保留一个指向新数据库的链接，这样做是为了保持记录的rowid不变。如果发生大量的行迁移，需要核实表的PCTFREE参数是否太小。 创建每行长度超过db_block_size的表，模拟行迁移和行链接 SYS@orcl>show parameter db_block_size NAME TYPE VALUE ———————————— ———– —————————— db_block_size integer 8192 SYS@orcl>create table t_large(a char(2000), b char(2000), c char(2000), d char(2000), e char(2000)); Table created. SYS@orcl>insert into t_large values (’1′,’1′,’1′,’1′,’1′); 1 row created. SYS@orcl>insert into t_large values (’2′,’2′,’2′,’2′,’2′); 1 row created. SYS@orcl>insert into t_large values (’3′,’3′,’3′, null, null); 1 [...]

一、修改系统参数，打开审计功能 SYS@orcl>show parameter audit; NAME TYPE VALUE ———————————— ———– —————————— audit_file_dest string /opt/ora10g/oracle/product/10. 2.0/db_1/admin/orcl/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string NONE SYS@orcl>alter system set audit_trail = db_extended scope=spfile; System altered. –Restart the database. SYS@orcl>show parameter audit; NAME TYPE VALUE ———————————— ———– —————————— audit_file_dest string /opt/ora10g/oracle/product/10. 2.0/db_1/admin/orcl/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB_EXTENDED [...]

设置日期格式，方便查看log文件的日期相关字段 SYS@orcl>alter session set NLS_DATE_FORMAT = ‘yyyy-mm-dd hh24:mi:ss’ SYS@orcl>alter session set NLS_TIME_STAMP_FORMAT = ‘yyyy-mm-dd hh24:mi:ss 查看redo log文件相关信息 SYS@orcl>archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oralog Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7 SYS@orcl>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS [...]

测试表及索引： ADAM@orcl>desc t Name Null? Type ———————————————- ——– ——- N NUMBER ADAM@orcl>select index_name, column_name from user_ind_columns where table_name = ‘T’; INDEX_NAME COLUMN_NAME —————————— —————————— IND_T N ADAM@orcl>select * from t; N ———- 1 50 20 100 10 500 501 查看执行计划的脚本p2 select * from table(dbms_xplan.display_cursor) 原始语句及执行计划,使用的是index scan ADAM@orcl>select * from t where n = 1; N [...]

Make sure the current use has been granted select privilege on v$sql, v$sql_plan, v$session. SYS@orcl>grant select on v_$sql to plustrace; SYS@orcl>grant select on v_$sql_plan to plustrace; SYS@orcl>grant select on v_$session to plustrace; SYS@orcl>grant plustrace to adam; Make sure set parameter serveroutput to off, otherwise you will the following errors: PLAN_TABLE_OUTPUT ——————————————————————- SQL_ID 9babjv8yq8ru3, child number [...]

Create role plustrace SYS@orcl>@?/sqlplus/admin/plustrce.sql SYS@orcl>select role from dba_roles where role like ‘P%’; ROLE —————————— PLUSTRACE Grant it to user SYS@orcl>grant plustrace to adam; Grant succeeded.

Recently I found there are 2 similar performance views in Oracle: V$SEGSTAT and V$SEGMENT_STATISTICS, Are they same in contents? Here are the structure of those 2 tables: search@WEBDB>desc v$segstat Name Null? Type —————————————————————– ——– ————- TS# NUMBER OBJ# NUMBER DATAOBJ# NUMBER STATISTIC_NAME VARCHAR2(64) STATISTIC# NUMBER VALUE NUMBER search@WEBDB>desc v$segment_statistics Name Null? Type —————————————————————– ——– ————- [...]