ITPub博客

可以大致看一下hwm下有多少block是free的。[@more@]SQL> select count(*) from t; COUNT(*)---------- 126264SQL> delete from t where object_id<14000;已删除96216行。SQL> commit;提交完成。SQL> analyze table t compute statistics;表已分析。SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T' and owner='SYS'; BLOCKS EMPTY_BLOCKS NUM_ROWS---------- ------------ ---------- 1756 36 30048SQL> select count(distinct dbms_rowid.rowid_block

The Oracle Universal Installer inventory stores information about all Oracle software products installed in all Oracle homes on a host, provided the product was installed using Oracle Universal Installer.Inventory information is stored in Extensible Markup Language (XML) format. The XML format allows for easier diagnosis of problems and faster loading of data. Any secure information is not stored directly in the inventory. As a result, during removal of some products, you may be prompted to ente

困扰了很久的一个问题！[@more@]SQL> create table tt(id int , name varchar2(20)) tablespace users;表已创建。SQL> insert into tt values(1,'a');已创建 1 行。SQL> insert into tt values(2,'aa');已创建 1 行。SQL> insert into tt values(2,'aaa');已创建 1 行。SQL> insert into tt values(2,'aaaa');已创建 1 行。SQL> insert into tt values(2,'aaaaa');已创建 1 行。SQL> insert into tt values(2,'aaaaaa');已创建 1 行。SQL> insert into tt values(2,'aaaaaaa');已创建 1 行。SQL> insert into

之前的版本貌似都放在了udump中...[@more@]SQL> select distinct sid from v$mystat; SID---------- 159SQL> select paddr,server from v$session where sid=159;PADDR SERVER-------- ---------6964C00C SHAREDSQL> alter session set sql_trace=true;会话已更改。SQL> select name,paddr from v$shared_server where paddr='6964C00C';NAME PADDR---- --------S000 6964C00CSQL> select pid,spid from v$process where addr='6964C00C'; PID SPID---------- ------------ 14 544SQL> sel

主要是验证一下block内部的一个"fb"标志The ‘fb’ value gives us flags about the row. ‘H’ means that we have the head of the row. ‘F’ means that we have the first piece of the row. ‘L’ means we also have the last piece of the row. Since this is the first and last piece of the row, the row is not chained. Since this is also the head of the row, the row has not been migrated.[@more@]SQL> create table tt(a char(2000),b char(2000),c char(2000) , d char(2000) ,e char(2000)) tablespac

都是doc的内容：Templates are used to set redundancy (mirroring) and striping attributes of files created in an ASM disk group. When a file is created, redundancy and striping attributes are set for that file based on an explicitly named template or the system template that is the default template for the file type.When a disk group is created, ASM creates a set of default templates for that disk group. The set consists of one template for each file type (data file, control file, redo log file, a

问题虽小，却困扰了一段时间！[@more@]SQL> create tablespace TEST datafile '+DG/asmdb/datafile/TEST.DBF' SIZE 3M;表空间已创建。SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;FILE_NAME--------------------------------------------------------------------------------+DG/asmdb/datafile/users.266.688401581+DG/asmdb/datafile/sysaux.264.688401529+DG/asmdb/datafile/undotbs1.263.688401515+DG/asmdb/datafile/system01.dbf+DG/asmdb/datafile/test.dbf--=====================================ASMCMD> pwd+dg/asmdb

rt[@more@]14:32:24 SQL> create table tt(id int) rowdependencies tablespace users;表已创建。14:32:57 SQL> insert into tt values(1);已创建 1 行。14:33:12 SQL> insert into tt values(2);已创建 1 行。14:33:14 SQL> insert into tt values(3);已创建 1 行。14:33:16 SQL> commit;提交完成。14:33:18 SQL> select id ,ora_rowscn from tt; ID ORA_ROWSCN---------- ---------- 1 1739405 2 1739405 3 173940514:33:32 SQL> update tt set id=100;已更新3行。14:33:

讲课时随手记录的flashback table大致过程[@more@]SQL> connect / as sysdbaConnected.SQL> desc tt Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(38)SQL> select * from tt;no rows selectedSQL> set time on14:14:36 SQL> insert into tt values(1);1 row created.14:14:42 SQL> insert into tt values(2);1 row created.14:14:44 SQL> insert into tt

oracle有好几个和参数相关的视图，它们之间到底有啥区别，下面通过试验以及结合doc来理解一下。[@more@] 1.v$spparameter 这里的sp就是server parameterdoc对它的解释是：This view displays the current contents of the server parameter file. The view returns FALSE values in the ISSPECIFIED column if a server parameter file is not being used by the instance.验证一下：SQL> connect / as sysdba已连接。SQL> show parameter spfileNAME TYPE VALUE--------------------------

doc对该参数的描述根本就不清楚：CORE_DUMP_DESTPropertyDescriptionParameter typeStringSyntaxCORE_DUMP_DEST = directoryDefault valueORACLE_HOME/DBSModifiableALTER SYSTEMBasicNoCORE_DUMP_DEST is primarily a UNIX parameter and may not be supported on your platform. It specifies the directory where Oracle dumps core files.[@more@]从下面的描述来看，core dump file应该是由服务器进程产生的CORE_DUMP_DEST should point to the directory where core dumps from the Oracle server will beplaced. A core dump is a m

ash and awr[@more@]The ASH contains recent information on active sessions sampled every second. The AWR takes snapshots of the database every hour, so the information in the AWR could be almost an hour old and will not help in diagnosing issues that are current on the database. Typically, to resolve issues currently on the database, detailed information pertaining to the last 5 or 10 minutes is critical. Because recording session activity is expensive, ASH samples V$SESSION every second and reco

Temporary Tablespace Group[@more@]A temporary tablespace group can be thought of as a shortcut for a list of temporary tablespaces.A temporary tablespace group consists of only temporary tablespaces.A temporary tablespace group has the following properties:It contains at least one temporary tablespace. There is no explicit limit on how many tablespaces are contained in a group.It has the same namespace as tablespaces. It is not possible for a tablespace and a temporary tablespace group to have t

Base Statistics and Metrics[@more@]Base statistics represent the raw data collected. For example, counting the number of physical reads in the system since startup is a base statistic.Metrics are secondary statistics derived from base statistics. Most metrics track the rates of change of activities in the Oracle database.For example, the average physical reads in the system in the last 60 minutes is a metric. Metrics are used by internal client components forsystem health monitoring, problem det

AWR(Automatic Workload Repository)[@more@]This component is the central element of the new manageability infrastructure. It provides services to internal Oracle server components to collect, process, maintain, and access performance statistics for problem detection and self-tuning purposes.AWR is the infrastructure that provides services to Oracle Database 10g components to collect,maintain, and utilize statistics for problem detection and self-tuning purposes.The AWR infrastructure consists of

有关一致性读的过程确实非常复杂...希望有一天能以试验的方式清晰的解读这个过程...[@more@]CREATING A READ CONSISTENT VIEW STEPS 1. Read the Data Block. If the block is resident in memory, create a clone to perform the undo. 2. Read the Row Header. 3. Check the LockByte to determine if there is an ITL entry. 4. Read the ITL to determine the Transaction ID.Automatic UNDO Internals5. Read the Transaction Table. If the transaction has been committed and has a System Commit Number less than the query’s

doc解释的很清楚，看doc吧The number of undo segments created and brought online is a function of the SESSIONS parameter. The algorithm is roughly 1 undo segment for each 5 sessions. All undo segments are placed online when the sessions parameter is set to 46 or greater. The lower limit was not tested, as the minimum value for the sessions parameter in the test database was 16, which was derived from the minimum number of processes (10) for the database. These are sized according to the autoalloca

当并发事务的数量超过在线undo segment的数量时undo segment被smon自动创建， 我记得不知道在哪儿看到过说：undo segment的创建会记录在alert中，但是没有发现...还是记错了...One of the benefits of AUM is the dynamic creation of undo segments as needed. When the number of concurrent transactions exceeds the number of online undo segments, more segments are brought online. If there are offline segments in the current undo tablespace, they are the first ones to be used. If no offline segments exist, new ones

When a transaction needs more space in an undo segment, there is a fixed algorithm the determines when extents are reused.. The transaction will first attempt to reclaim expired extents from the current segment, then expired extents from another segment. The third method will be to autoextend the datafile(s), if this is set. Next, the unexpired extents from the current segment and other segments will be allocated, as long as the extents do not contain uncommitted transactions. Finally, an error

什么是BLOCK CLEANOUT！[@more@]BLOCK CLEANOUT When a data block is altered, important transaction information is written to the block along with the data changes. When the transaction is committed, the block is not rewritten to reflect the new transaction state. The next transaction, whether a SELECT or INSERT/UPDATE/DELETE, will ‘discover’ the out of date information and update the block to reflect the committed state of the previous transaction. This is called ‘delayed block cleanout.’ It i