How to trigger ORA-00600,ORA-7445 by manual

Sometime we’d like to trigger ORA-00600/7445 internal errors for testing purpose, But it’s not easy if you don’t know a little trick like below:

declare
a exception;
pragma exception_init(a,-600);
begin
raise a;
end;

declare
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [],
[], [], []
ORA-06512: at line 5

or
oradebug unit_test dbke_test dde_flow_kge_ora ouch! 0 0

7445:
select spidfrom v$processp, v$sessions
where p.addr= paddr
and sid= sys_context('USERENV','SID');

kill -SEGV $PID

or

declare
a exception;
pragma exception_init(a,-7445);
begin
raise a;
end;

【Oracle数据恢复】ORA-01115、ORA-01110、ORA-27091、ORA-27070、OSD-04006、O/S-Error

某用户windows 2003上的数据库由于存储故障导致系统表空间system.dbf出现IO问题,当打开数据库OPEN database时报错:

 

 

ORA-01115: IO error reading block from file 15 
ORA-01110: data file … 
ORA-27091: unable to queue I/O 
ORA-27070: async read/write failed 
OSD-04006: ReadFile() failure, unable to read from file 
O/S-Error: (OS 121) The semaphore timeout period has expired.


以上ORA-01115、ORA-01110、ORA-27091、ORA-27070、OSD-04006、O/S-Error 这堆报错本质与Oracle数据库层面没有关系,问题的根本原因是Windows上对应磁盘驱动器下的文件无法读取出来,这可能是OS bug 也可能就是对应磁盘出现了坏道或其他物理故障,所以对于该问题有限考虑在OS层面解决文件的读取问题, 如果确实发现无法从OS层面或从备份解决,那么可以考虑特殊的恢复手段。‘

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638 QQ号:47079569 邮箱:service@parnassusdata.com

 

 

Error: OSD 4006
Text: ReadFile() failure, unable to read from file
—————————————————————————
Cause: Unexpected return from Windows NT system service ReadFile()
Action: Check OS error code and consult Windows NT documentation

This is due to a problem in Windows such that when Oracle attempted to access the data file on that device, it could not because the device timed out. This suggests that Windows has run out of asynchronous I/O buffers or there is a communications delay on the device.

There is nothing you’re going to be able to do at the database level to resolve this error, unless you move the data files to another drive. Ask the O/S system administrator to run diagnostics tools to check for possible faulty hardware and disk corruption on the disk device where the error is showing in the loader log. If the error persists, then log a call with Microsoft Support.

Oracle processes may encounter various (OS 1117) errors on a Windows 2003 Server. The text of the (OS 1117) error can be seen as follows:

C:\>net helpmsg 1117
The request could not be performed because of an I/O device error.
This error may manifest itself in different ways, depending on which Oracle process encounters the error:

Oracle RDBMS Instance Encounters (OS 1117) error

1. If an Oracle RDBMS instance encounters the error, you may see messages such as the following in the alert log for the RDBMS instance:

==========================================================================

Fri Jul 13 01:21:33 2007
Errors in file d:\oracle\db\product\admin\mydb\bdump\mydb1_lmon_4608.trc:
ORA-27091 : unable to queue I/O
ORA-27070 : async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 1117) The request could not be performed because of an I/O device error.
==========================================================================

Oracle ASM Instance Encounters (OS 1117) errors

2. If an Oracle ASM instance encounters the error, you may see similar errors in the ASM instance’s Alert log:

============================================
Fri Jul 13 01:22:10 2007
Errors in file d:\oracle\asm\product\admin\+asm\bdump\+asm1_gmon_3836.trc:
ORA-27091 : unable to queue I/O
ORA-27070 : async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 1117) The request could not be performed because of an I/O device error.
============================================

CRS Daemon (crsd.exe) encounters 1117 errors
3. If you are running in an Oracle Clusterware environment, then you may also see errors in the crsd.log and/or certain resource logs, indicating a problem accessing the OCR (Oracle Cluster Registry). An example of those errors would be:

================================================
2007-07-13 01:21:51.766: [ OCROSD][4272]utwrite:4: Problem writing the buffer phy offset 184320 and oserror 1117
2007-07-13 01:21:51.766: [ OCROSD][4352]utwrite:4: Problem writing the buffer phy offset 184320 and oserror 1117
2007-07-13 01:21:51.766: [ OCRRAW][4352]beginlog: problem 26 clearing the log metadata buffer
2007-07-13 01:21:51.766: [ OCRRAW][4352]proprdkey: Problem in begin log
2007-07-13 01:21:51.766: [ OCRRAW][4352]proprseterror: Error in accessing physical storage [26] Marking context invalid.
================================================

CSS Daemon (ocssd.exe) encounters 1117 errors

4. Also, in an Oracle Clusterware environment, the Cluster Synchronization Services daemon (ocssd.exe) may experience problems accessing the voting disk. If this occurs, you will see an error in the ocssd.log similar to the following:

============================================
[ CSSD]2007-07-13 01:22:12.501 [4052] >ERROR: Internal Error Information:
Category: 1234
Operation: scls_block_write
Location: WriteFile
Other: unable to write block(s)
Dep: 1117

[ CSSD]2007-07-13 01:22:12.501 [4052] >ERROR: clssnmvReadBlocks: read failed 1 at offset 533 of \\.\votedsk2
[ CSSD]2007-07-13 01:22:12.501 [4052] >TRACE: clssnmDiskStateChange: state from 4 to 3 disk (1/\\.\votedsk2)
[ CSSD]2007-07-13 01:22:12.501 [2200] >TRACE: clssnmDiskPMT: disk offline (1/\\.\votedsk2)
[ CSSD]2007-07-13 01:22:12.501 [2200] >ERROR: clssnmDiskPMT: Aborting, 1 of 2 voting disks unavailable
[ CSSD]2007-07-13 01:22:12.501 [2200] >ERROR: ###################################
[ CSSD]2007-07-13 01:22:12.501 [2200] >ERROR: clssscExit: CSSD aborting
[ CSSD]2007-07-13 01:22:12.501 [2200] >ERROR: ###################################
==============================================

5. When you are running in an Oracle Clusterware environment, if the ocssd process encounters an I/O error when accessing the Voting Disk, the CSS daemon will evict the node from the cluster. This is done by signalling the Oracle Fence Driver (OraFencedrv.sys) to reboot the machine. When the fence driver reboots the machine, this will be seen as a bugcheck with stop code 0x0000ffff. You will be able to see this in the System Log with a message such as:

The computer has rebooted from a bugcheck.
The bugcheck was: 0x0000ffff (0x0000000000000000, 0x0000000000000000,
0x0000000000000000, 0x0000000000000000).
A dump was saved in: C:\WINDOWS\MEMORY.DMP.

Note that the bugcheck is expected behavior when ocssd.exe (the Cluster Synchornization Services daemon) encounters an I/O error when accessing the voting disk. The node experiencing the I/O error is intentionally rebooted to avoid a split-brain and possible data corruption when access to the voting disk is lost.
CHANGES

You may encounter this error after upgrading the Microsoft Storport driver to version 5.2.3790.4021 or later.

CAUSE

Reference Microsoft KB article#932755, available at the following URL:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;932755

Per that article, one of the changes introduced in this version of the Storport driver is the following:

=========================================================
If a target returns a SCSI status of BUSY or Task Set Full, the port driver retries the command immediately. Storport retries the command an unlimited number of times. Therefore, if the busy status continues, the system could eventually experience problems.

This update configures the following behavior:

• It limits the number of retries. The default is 20.

• If the target returns a status of BUSY, the Storport driver performs a time-based pause before the Storport driver retries the command.

• If the target returns a status of Task Set Full, the Storport driver performs an I/O completion-based pause before the Storport driver retries the command.
=========================================================

Therefore, prior to upgrading the Storport driver, if a storage path had become saturated, the Storport driver would immediately continue to retry – indefinitely. This would result in slow I/O and perhaps a hang or spin scenario, but no error would be returned.

With the later version of the Storport driver, the retries are limited to 20 retries by default, with a pause between each retry. After 20 failures with a device busy status, the (OS 1117) error is returned to applications waiting on I/O. For more information on changes to the Storport driver, you must contact Microsoft.

SOLUTION

This is an I/O performance problem. You will need to increase the performance/capacity of the storage system to avoid the prolonged BUSY status. Specific solutions will vary, depending on your storage vendor, so the storage vendor may need to be contacted to assist with tuning the storage. One potential solution includes implementing multi-pathing technology to improve the throughput of the storage.

Script to show Active Distributed Transactions

该脚本可以用于显示活跃的分布式事务(Distributed Transactions from dblink),可以协助诊断dblink远程事务:

 

REM distri.sql
column origin format a13
column GTXID format a35
column LSESSION format a10
column s format a1
column waiting format a15
Select /*+ ORDERED */
    substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
    substr(g.K2GTITID_ORA,1,35) "GTXID",
    substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
    substr(decode(bitand(ksuseidl,11),
               1,'ACTIVE',
               0, decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
               2,'SNIPED',
               3,'SNIPED', 'KILLED'),1,1) "S",
    substr(event,1,10) "WAITING"
from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
-- where  g.K2GTeXCB =t.ktcxbxba <= use this if running in Oracle7
where  g.K2GTDXCB =t.ktcxbxba -- comment out if running in Oracle8 or later
   and g.K2GTDSES=t.ktcxbses
   and s.addr=g.K2GTDSES
   and w.sid=s.indx;

REM distri_details.sql
set headin off
select /*+ ORDERED */
'----------------------------------------'||'
Curent Time : '|| substr(to_char(sysdate,'dd-Mon-YYYY HH24.MI.SS'),1,22) ||'
'||'GTXID='||substr(g.K2GTITID_EXT,1,10) ||'
'||'Ascii GTXID='||g.K2GTITID_ORA ||'
'||'Branch= '||g.K2GTIBID ||'
Client Process ID is '|| substr(s.ksusepid,1,10)||'
running in machine : '||substr(s.ksusemnm,1,80)||'
  Local TX Id  ='||substr(t.KXIDUSN||'.'||t.kXIDSLT||'.'||t.kXIDSQN,1,10) ||'
  Local Session SID.SERIAL ='||substr(s.indx,1,4)||'.'|| s.ksuseser ||'
  is : '||decode(bitand(ksuseidl,11),1,'ACTIVE',0,
          decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
          2,'SNIPED',3,'SNIPED', 'KILLED') ||
  ' and '|| substr(STATE,1,9)||
  ' since '|| to_char(SECONDS_IN_WAIT,'9999')||' seconds' ||'
  Wait Event is :'||'
  '||  substr(event,1,30)||' '||p1text||'='||p1
        ||','||p2text||'='||p2
        ||','||p3text||'='||p3    ||'
  Waited '||to_char(SEQ#,'99999')||' times '||'
  Server for this session:' ||decode(s.ksspatyp,1,'Dedicated Server',
                                          2,'Shared Server',3,
                                         'PSE','None') "Server"
from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
-- where  g.K2GTeXCB =t.ktcxbxba <= use this if running Oracle7
where  g.K2GTDXCB =t.ktcxbxba -- comment out if running Oracle8 or later
   and  g.K2GTDSES=t.ktcxbses
   and  s.addr=g.K2GTDSES
   and  w.sid=s.indx;
set headin on
-- end script

 

Oracle内部错误ORA-07445: [ACCESS_VIOLATION] [unable_to_trans_pc][UNABLE_TO_READ]

ORA-07445:  [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x5A1113A] [ADDR:0xFFFFFFFFFFFFFFFF] [UNABLE_TO_READ]一般是Windows平台上常见的内存不足问题引起,在64 bit 或 32bit 平台均可能发生,一般建议通过增加SGA_MAX_SIZE和SGA_TARGET(ASMM)来解决该问题,同时增加SHARED_POOL_SIZE、 JAVA_POOL_SIZE、 STREAMS_POOL_SIZE到200M以上。

也可能是由于Windows平台感染了计算机病毒引起的,建议同时也要查杀病毒。

 

ORA-07445 [ACCESS_VIOLATION] [unable_to_trans_pc] on Windows Platforms
Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.3 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
***Checked for relevance on 06-Oct-2011***
RDBMS 9.2 or greater on Windows platforms
Symptoms

ORA-07445: exception trouvee : image memoire [ACCESS_VIOLATION] [kpudcr2c+89] [PC:0x100CD3D1] 
[ADDR:0x19EC0000] [UNABLE_TO_WRITE] [] 

ORA-07445: Message 7445 not found; product=RDBMS; facility=ORA
; arguments: [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x5A1113A] [ADDR:0xFFFFFFFFFFFFFFFF] [UNABLE_TO_READ]

ORA-27300: OS system dependent operation:WaitForSingleObject failed with status: 0
ORA-27301: OS failure message: The operation completed successfully.
ORA-27302: failure occurred at: sssxcpttcs5

Changes

none known
Cause

This error

ORA-07445: Message 7445 not found; product=RDBMS; facility=ORA
; arguments: [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x5A1113A] [ADDR:0xFFFFFFFFFFFFFFFF]
[UNABLE_TO_READ]

is typical for WIN out of memory and is commonly seen when INIT.ORA parameters are set to 
that of the DBCA starter DB.

This problem appears as likely on 64 bit platforms as on 32 bit

Solution

Please refer to Note 342443.1 and apply latest minipack.

Have sufficient physical memory on Server so that you can allocate more SGA/PGA to the database.

 1) Increase SGA_MAX_SIZE  and SGA_TARGET so that you can accommodate following pools.

2) Restart the instance.

3) Increase INIT.ORA memory parameters and make sure following pools are set to recommended value i.e. 200M.

a) SHARED_POOL_SIZE

b) JAVA_POOL_SIZE

c) STREAMS_POOL_SIZE

ORA-07445: [ACCESS_VIOLATION] [unable_to_trans_pc]...[UNABLE_TO_READ] on Windows

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later   [Release: 10.2 and later ]
Microsoft Windows (32-bit)
***Checked for relevance on 06-Oct-2011***
Symptoms

Session aborted on a select statement and a trace file is created with :

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION]
[unable_to_trans_pc] [PC:0x7C34538C] [ADDR:0xFFFFFFFF] [UNABLE_TO_READ] []
Cause

Such arguments often highlight a resource failure where memory is the most common cause of this problem.

Two hidden processes on the server were generating error messages and consuming all of the CPU : LSASS and SCCHOST

These are often due to virus.

(A later case was discovered due to a  third party software named Quest Intrust Agent. )

Solution

Problem was fixed after these two actions were performed:

1. Both processes were killed.

2. Antivirus software found some viruses and eliminated them. 

ORA-7445 [ACCESS_VIOLATION] [unable_to_trans_pc] [UNABLE_TO_WRITE] ORA-27301: 
OS failure message: Not enough storage ORA-27300 ORA-27302 

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.3 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
***Checked for relevance on 22-Oct-2010***
Symptoms

These errors were encountered and the DB crashed.:

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc]
[PC:0x7C34126B] [ADDR:0x0] [UNABLE_TO_WRITE] []
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr.

You may observe this in the trace file.:

"Current SQL information unavailable - no SGA."

Changes

This could be triggered by hardware issues or an increase in volume or by day to day operations.
Cause

Insufficient memory.

Solution

Ensure that the existing memory is functioning properly.

Verify you have enough memory available to support the configuration implemented. 

Another option is to decrease the size of the SGA.

Check your OS log for hardware errors. 

ORA-07445: [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x609C6FA2] [ADDR:0x79A0040] [UNABLE_TO_READ] 

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.2 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
Symptoms

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x609C6FA2]
[ADDR:0x79A0040] [UNABLE_TO_READ] []
occuring on a select with bind variables.

The call stack will be exact or similar to:
_clscugblmterm -> _clsc_term -> _clscterm -> _prom_terminate
AND/OR
malloc -> _malloc_unlocked -> cleanfree -> realfree 

--clscugblmterm is a RAC function, however in the case of this bug, the instances ARE NOT RAC. 

Cause

The cause of this problem has been identified and verified in an unpublished Bug 4723824.

This bug was introduced in 10.2.0.2.

Solution

Your options are:
1) Verify the patch you are on for 10.2.0.2 on windows is at least at patch least 8 as this bug is
fixed in windows 10.2.0.2 patch 8.  Please see Note:161549.1 for further information about windows patching
OR
2)  If on UNIX, determine from MOS if there is a one-off patch available on your database/OS version combination
OR
3) RECOMMENDED:  Upgrade to 11g as this bug is fixed in the 11g database version.

Ora-7445 [ACCESS_VIOLATION] [unable_to_trans_pc] After Applying Windows 10.2.0.4 Patch Bundle 10 

Applies to:

Oracle Server - Standard Edition - Version: 10.2.0.4 to 10.2.0.4 - Release: 10.2 to 10.2
z*OBSOLETE: Microsoft Windows XP
Microsoft Windows XP
Symptoms

On Windows, using Oracle 10.2.0.4, getting the following error in the alert.log:

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc]
[PC:0x7C911669] [ADDR:0x0] [UNABLE_TO_READ] []

  --- Call Stack ----
  _sldmGetHostName _sldmInit _ldmInit _keltnfy _kscnfy _ksucrp _opiino _opiodr _opidrv _sou2o
  _opimai_real _opimai
Cause

High CPU usage
Solution

Install KB 951312 from Microsoft to overcome this problem of high CPU usage. http://support.microsoft.com

Drop User Cascade Results In ORA-03113 and ORA-7445 [ACCESS_VIOLATION] [unable_to_trans_pc]
Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.5 - Release: 10.2 to 10.2
Information in this document applies to any platform.
Checked for relevance on 4-Nov-2011
Symptoms

Drop user cascade fails with ORA-3113 error:

SQL> drop user vipr cascade;
drop user vipr cascade
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

The alert.log shows:

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc]
[PC:0x61FF2728] [ADDR:0x4] [UNABLE_TO_READ] []

In the trace we see:

Current SQL statement for this session:
drop java source "VIPR"."HOST"
----- Call Stack Trace -----
joxdrp opiexe opiosq0 opiosq opiodr rpidrus rpidru rpiswu2 rpidrv rpisplu rpispl kzdukl kzudrp
opiexe opiosq0 kpooprx kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o
It is failing when trying to drop java source.

Cause

JVM is in REMOVING status.

COMP_NAME VERSION STATUS
Oracle Enterprise Manager 10.2.0.3.0 VALID
Oracle Workspace Manager 10.2.0.1.0 VALID
Oracle Database Catalog Views 10.2.0.3.0 VALID
Oracle Database Packages and Types 10.2.0.3.0 VALID
JServer JAVA Virtual Machine 10.2.0.3.0 REMOVING

Solution

Follow Note 276554.1 How to Reload the JVM in 10.1.0.X and 10.2.0.X to remove or reload JVM.

Network Interface No Longer Operational?

Solaris平台上的Oracle数据库,Alert日志偶尔会出现”Network Interface No Longer Operational”的相关记录:

ospid 11223: network interface with IP address 192.4.1.22 no longer operational
requested interface 192.4.1.22 ioctl get mtu. Check output from ifconfig command

该错误一般是由Solaris操作系统Bug 6546482引起的,该错误一般可以忽略。

Know about Oracle High Water Mark

there’s no HWM for datafiles, it’s just a virtual term to describe the last block containing data in the data file, which is the minimum size allowed for sizing down the data file.

This article intends to provide an SQL script to find tables which are fragmented (i.e Data is much lower then High Water Mark),so that we can target those segments (tables) for recreation.

Software Requirements/Prerequisites

Execution Environment    :SQL, SQL*Plus

Access Privileges              :Requires dba privileges as script is to be run as   the owner SYS or SYSTEM

Prerequisites                     :Do an Analyze  with compute statistics on all tables present in the Users schema

i.e Analyze table <table_name> compute statistics

Usage                                :Sqlplus username/<password>

SQL> @fragment.sql

Advisory                            Will not work on Compressed tables, may return negative numbers.

 

Configuring the Script

1.User needs dba privileges to access dba_tables  .

2.Statistics needs to be collected on all the tables using  compute statistics

option for the input schema before fragment.sql is   run.

Running the Script

Step 1:- Copy this script to a file named fragment.sql.

Step 2:- Connect as user SYS or SYSTEM.

Step 3:- Run Analyze on all the tables present in the schema  for which you want to find the fragmented table.

SQL> Analyze table <table_name> compute statistics ;

Step 4:- Execute the fragment.sql script.Note the script will prompt for Schema name.

SQL> @fragment.sql

 

Caution

This script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it. Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.

Script

REM This is an example SQL*Plus Script to find tables fragmentated below high water mark

set heading off verify off echo off
Spool fragment.sql

REM The below queries gives information about the size of the table with respect to the High water Mark
REM note that BLOCKS*8192 is BLOCKS times the block size: 8192.  Substitue your DB blocksize.
REM SELECT BLOCKS*8192/1024/1024 FROM  DBA_TABLES WHERE  TABLE_NAME='<TABLE_NAME>'  and    owner='<owner>'   ;
REM The below queries gives the actual size in MB used by the table in terms of data .
REM SELECT NUM_ROWS*AVG_ROW_LEN/1024/1024 FROM  DBA_TABLES WHERE TABLE_NAME='<TABLE_NAME>' and  owner='<owner'
REM
REM You can use the difference of the two sql statements specified above to get the table which
REM has fragementation below high water mark prompt Enter name(s) of schema for which you want to find
REM fragemented object.
PROMPT Please enter the schema name

SELECT TABLE_NAME ,  (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB"   FROM  DBA_TABLES WHERE  UPPER(owner) =UPPER('&OWNER') order by 2 desc;

Spool off

Goal

This article explains, with examples, how to view the high water mark and when the high water mark is reset. The queries given in this article applies when the segment , whose high water mark has to be determined ,  is in one datafile and is not spawned across multiple datafiles .

Solution

The high water mark is the boundary between used and unused space in a segment. As requests for new free blocks that cannot be satisfied by existing free lists are received, the block to which the high water mark points to becomes a used block, and the high water mark is advanced to the next block. In other words, the segment space to the left of the high water mark is used, and the space to the right of it is unused.

The high-water mark is the level at which blocks have never been formatted to receive data.

When a table is created in a tablespace, some initial number of blocks / extents are allocated to the table. Later, as the number of rows inserted increases, extents are allocated accordingly.

To find out how many blocks / extents are allocated to the table, query DBA_SEGMENTS for ‘blocks’ and ‘extents’.

For example:

SQL>create table test1 (num number) tablespace tbsp1;

Table created

SQL>select blocks, extents from dba_segments where segment_name=’TEST1′

BLOCKS EXTENTS
——– ————-
8 1

Now, to view the high water mark, perform an:

SQL> analyze table test1 compute statistics;

Querying dba_tables for ‘Blocks’ and ‘Empty_blocks’ should give the high water mark.

Blocks — > Number blocks that has been formatted to recieve data
Empty_blocks —> Among the allocated blocks, the blocks that were never used

SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name=’TEST1′

BLOCKS EMPTY_BLOCKS NUM_ROWS
————– ————————– ——————–
0 7 0

If you insert some rows, then the output of the above query returns:

BLOCKS EMPTY_BLOCKS NUM_ROWS
————– ————————– ——————–
1 6 8

blocks+Empty_blocks=1+6=7 (but not 8) because 1 block is for segment header.

Insert some more rows into table ‘TEST1’ to increase the number of extents allocated so that
DBA_SEGMENTS will show :

BLOCKS EXTENTS
————– —————
32 4

And dba_tables (after analyze table) shows:

BLOCKS EMPTY_BLOCKS NUM_ROWS
————– ————————– ——————–
28 3 14338

Deleting the records doesn’t lower the high water mark. Therefore, deleting the records doesn’t raise the ‘Empty_blocks’. After deleting the records, if you query dba_segments or dba_tables, there would be no change. Even an ‘Alter table test1 deallocate unused;’ will not bring the high water mark down.

To determine the exact number of blocks that contain data i.e. space used by table below the high water mark, query rowid and get the first and last used block from the rowid.

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) “used blocks” from TEST1;

This works fine if only one file is used for the segment. If more files, we need to include the file number in some way, for instance:

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)||’-‘||dbms_rowid.rowid_relative_fno (rowid)) “used blocks” from TEST1

USED_BLOCKS
———————–
22

From this we can conclude that for table ‘TEST1’, 32 blocks are allocated out of which 28 blocks are formatted to receive data but only 22 blocks contain the actual data.

The high water mark can be reset with a truncate table or if the table is moved to another tablespace.  Additionally, in 10g the following option to shrink a segment was introduced to reset the high water mark. eg. ALTER TABLE <tablename> SHRINK SPACE;

When the table is created with CTAS from another table, the high water mark of the latter table is not reflected in the new table. If the tablespace is moved back to the same tablespace, the high water mark is reset. In this case, query on obj#, dataobj# of obj$. Obj# remains the same but dataobj# changes.

 

PURPOSE
This article describes how to find out how many blocks are really being
used within a table ie. are not empty. Please note that this article does
not cover what to do when chaining is taking place.

SCOPE & APPLICATION
For DBA’s needing to determine how many blocks within a table are
empty blocks.

How many blocks contain data (are not empty)
——————————————–
Each row in the table has pseudocolumn called ROWID.
This pseudo contains information about physical location
of the row in format
block_number.row.file

If the table is stored in a tablespace which has one
datafile, all we have to do is to get DISTINCT
number of block_number from ROWID column of this table.

But if the table is stored in a tablespace with more than one
datafile then you can have the same block_number but in
different datafiles so we have to get DISTINCT number of
block_number+file from ROWID.

The SELECT statements which give us the number of “really used”
blocks is below. They are different for ORACLE 7 and ORACLE 8
because of different structure of ROWID column in these versions.

For ORACLE 7:

SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||
SUBSTR(rowid,1,8)) “Used”
FROM schema.table;

For ORACLE 8+:

SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) “Used”
FROM schema.table;
or

SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) “Used”
FROM schema.table;

You could ask why the above information could not be determined
by using the ANALYZE TABLE command. The ANALYZE TABLE command only
identifies the number of ‘ever’ used blocks or the high water mark
for the table.

What is the High Water Mark?
—————————-
All Oracle segments have an upper boundary containing the data within
the segment. This upper boundary is called the “high water mark” or HWM.
The high water mark is an indicator that marks blocks that are allocated
to a segment, but are not used yet. This high water mark typically bumps
up at 5 data blocks at a time. It is reset to “zero” (position to the start
of the segment) when a TRUNCATE command is issued. So you can have empty
blocks below the high water mark, but that means that the block has been
used (and is probably empty caused by deletes). Oracle does not move the
HWM, nor does it *shrink* tables, as a result of deletes. This is also
true of Oracle8. Full table scans typically read up to the high water mark.

Data files do not have a high water mark; only segments do have them.

How to determine the high water mark
————————————
To view the high water mark of a particular table::

ANALYZE TABLEESTIMATE/COMPUTE STATISTICS;

This will update the table statistics. After generating the statistics,
to determine the high water mark:

SELECT blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name =;

BLOCKS represents the number of blocks ‘ever’ used by the segment.
EMPTY_BLOCKS represents only the number of blocks above the ‘HIGH WATER MARK’
.
Deleting records doesn’t lower the high water mark. Therefore, deleting
records doesn’t raise the EMPTY_BLOCKS figure.

Let us take the following example based on table BIG_EMP1 which
has 28672 rows (Oracle 8.0.6):

SQL> connect system/manager
Connected.

SQL> SELECT segment_name,segment_type,blocks
2> FROM dba_segments
3> WHERE segment_name=’BIG_EMP1′;
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
—————————– —————– ———- ——-
BIG_EMP1 TABLE 1024 2
1 row selected.

SQL> connect scott/tiger

SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name=’BIG_EMP1′;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
—————————— ———- ———- ————
BIG_EMP1 28672 700 323
1 row selected.

Note: BLOCKS + EMPTY_BLOCKS (700+323=1023) is one block less than
DBA_SEGMENTS.BLOCKS. This is because one block is reserved for the
segment header. DBA_SEGMENTS.BLOCKS holds the total number of blocks
allocated to the table. USER_TABLES.BLOCKS holds the total number of
blocks allocated for data.

SQL> SELECT COUNT (DISTINCT
2> DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
3> DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) “Used”
4> FROM big_emp1;
Used
———-
700
1 row selected.

SQL> DELETE from big_emp1;
28672 rows processed.

SQL> commit;
Statement processed.

SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name=’BIG_EMP1′;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
—————————— ———- ———- ————
BIG_EMP1 0 700 323
1 row selected.

SQL> SELECT COUNT (DISTINCT
2> DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
3> DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) “Used”
4> FROM big_emp1;
Used
———-
0
1 row selected.

SQL> TRUNCATE TABLE big_emp1;
Statement processed.

SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name=’BIG_EMP1′;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
—————————— ———- ———- ————
BIG_EMP1 0 0 511
1 row selected.

SQL> connect system/manager
Connected.

SQL> SELECT segment_name,segment_type,blocks
2> FROM dba_segments
3> WHERE segment_name=’BIG_EMP1′;
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
—————————– —————– ———- ——-
BIG_EMP1 TABLE 512 1
1 row selected.

Note: TRUNCATE has also deallocated the space from the deleted rows.
To retain the space from the deleted rows allocated to the table use:
TRUNCATE TABLE big_emp1 REUSE STORAGE

PGA Usage Larger than PGA_AGGREGATE_TARGET setting?

pga_aggregate_target is a target, as opposed to a hard limit – so it isn’t unusual to go above that.
13G above that, now that’s unusual though! There IS an enhancement request in,
to make a hard-limit setting, but that does not currently exist.
There is a known bug in 10203 with certain statements burning up memory – bug 5947623 – however,
the 10203/aix version of this patch is 64-bit, and the SR header says you are on 32-bit, so that isn’t
an option….and 10203 is old enough that I can’t get a new version of the patch made.

As I was unable to see any errors (e.g., ORA-4030) thre does not seem to be any problem with the operation of the database.

PGA_AGGREGATE_TARGET does not set a hard limit on pga usage. It is only a target value used to dynamically size the process work areas. It also does not affect other areas of the pga that are allowed to grow beyond this limit.

There are certain areas of pga that cannot be controlled by initialization parameters. Such areas include pl/sql memory collections such as pl/sql tables and varrays.

Depending on the programming code and amount of data being handled these areas can grow very large (up to 20G internal limit on 10) and can consume large amounts of memory. This memory growth can be controlled by good programming practices. As an example, use LIMIT clause with BULK COLLECT.

Additionally, programming mistakes can also lead to excessive memory usage.

You can take steps to control the size of a process. However, from within the database framework you cannot place a hard limit on the size of a process by setting any initialization parameters or database configuration.

You can limit the size of a process from the OS side by setting kernel limits or user shell limits but this leads to the ORA-4030 and will cause transaction rollback.

As noted in bug 7279150, “… this is not a hard limit and that we will exceed it when it is undersized and the workload increases rapidly, such as when they start the workload for their testing or when they spawn a new set of sessions from their application servers.”

As the DBA you need to get confirmation from your operating system administrator that the amount of memory reported as being in use by a process includes or does not include shared memory. If shared memory is included in the value displayed by the operating system utility, then the shared pool size must be deducted from that value to know how much private memory the process is actually using.

See note 174555.1 “UNIX Determining the Size of an Oracle Process”.

If an RDBMS user process is using more private memory than expected, then the DBA has three options:

– Do nothing
– Monitor the RDBMS iuser session to find out what SQL statements are being performed or were being performed by that RDBMS session. Using the SQL*Trace functionality of the database would normally be done if information from the end user cannot be obtained directly as to what they were doing since the memory usage was higher than expected or what they are doing right now.
– Kill that RDBMS user session.

Gather DBMS_STATS Default parameter

What are the default parameter values ?

   select dbms_stats.get_param('cascade') from dual;
   select dbms_stats.get_param('degree') from dual;
   select dbms_stats.get_param('estimate_percent') from dual;
   select dbms_stats.get_param('method_opt') from dual;
   select dbms_stats.get_param('no_invalidate') from dual;
   select dbms_stats.get_param('granularity') from dual;


DEFAULT PARAMETER

DBMS_STATS.AUTO_CASCADE
NULL
DBMS_STATS.AUTO_SAMPLE_SIZE
FOR ALL COLUMNS SIZE AUTO
DBMS_STATS.AUTO_INVALIDATE
AUTO

Oracle常用诊断事件清单

事件 说明 例子
Event 10013 – Monitor Transaction Recovery 在Startup时跟踪事务恢复 ALTER SESSION SET EVENTS ‘10013 trace name context forever, level 1’;
Event 10015 – Dump Undo Segment Headers- 在事务恢复后做Dump回退段头信息 ALTER SESSION SET EVENTS ‘10015 trace name context forever, level 1’;
Event 10032 – Dump Sort Statistics Dump排序的统计信息 ALTER SESSION SET EVENTS ‘10032 trace name context forever, level 10’;
Event 10033 – Dump Sort Intermediate Run Statistics 排序过程中,内存排序区和临时表空间的交互情况 ALTER SESSION SET EVENTS ‘10033 trace name context forever, level 10’;
Event 10045 – Trace Free List Management Operations FREELIST的管理操作 ALTER SESSION SET EVENTS ‘10045 trace name context forever, level 1’;
Event 10046 – Enable SQL Statement Trace 跟踪SQL,有执行计划,邦定变量和等待的统计信息,level 12最详细。 ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12’; 

LEVEL定义如下:

1:SQL 语句,执行计划和执行状态

4:1的内容加上绑定变量信息

8:1的信息加上等待事件信息

12:1+4+8

Event 10053 – Dump Optimizer Decisions 在分析SQL语句时,Dump出优化器所做的选择,级别level 1最详细 ALTER SESSION SET EVENTS ‘10053 trace name context forever, level 1’; 

LEVEL定义如下:

1:状态和估算信息

2:只显示估算信息

Event 10060 – Dump Predicates DUMP SQL语句中的断语信息。需要在需要DUMP的用户下创建以下表 

CREATE TABLE kkoipt_table
(c1 INTEGER,

c2 VARCHAR2(80));

断语信息会写入该表

ALTER SESSION SET EVENTS ‘10060 trace name context forever, level 1’;
Event 10065 – Restrict Library Cache Dump Output for State Object Dumps 限制对象状态DUMP的时候LIBRARY CACHE信息的详细程度
1 Address of library object only 

2 As level 1 plus library object lock details

3 As level 2 plus library object handle and library object

缺省是LEVEL 3

ALTER SESSION SET EVENTS ‘10065 trace name context forever, level level’;
Event 10079 – Dump SQL*Net Statistics- Dump SQL*NeT的统计信息 ALTER SESSION SET EVENTS ‘10079 trace name context forever, level 2’;
Event 10081 – Trace High Water Mark Changes HWM的改变 ALTER SESSION SET EVENTS ‘10081 trace name context forever, level 1’;
Event 10104 – Dump Hash Join Statistics HASH JOIN的统计信息 ALTER SESSION SET EVENTS ‘10104 trace name context forever, level 10’;
Event 10128 – Dump Partition Pruning Information 分区表调整信息 ALTER SESSION SET EVENTS ‘10128 trace name context forever, level level’; 

Level取值:

1   Dump pruning descriptor for each partitioned object

0x0002 Dump partition iterators

0x0004 Dump optimizer decisions about partition-wise joins

0x0008 Dump ROWID range scan pruning information

在9.0.1或者后面的版本,在level 2后还需要建立如下的表:

CREATE TABLE kkpap_pruning

(

partition_count    NUMBER,

iterator           VARCHAR2(32),

partition_level    VARCHAR2(32),

order_pt         VARCHAR2(12),

call_time        VARCHAR2(12),

part#             NUMBER,

subp#              NUMBER,

abs#               NUMBER

);

事件 说明 例子
Event 10200 – Dump Consistent Reads DUMP一致读的信息 ALTER SESSION SET EVENTS ‘10200 trace name context forever, level 1’;
Event 10201 – Dump Consistent Read Undo Application DUMP一致性读涉及UNDO信息的内容 ALTER SESSION SET EVENTS ‘10201 trace name context forever, level 1’;
Event 10220 – Dump Changes to Undo Header Dump出Undo头信息的改变 ALTER SESSION SET EVENTS ‘10220 trace name context forever, level 1’;
Event 10221 – Dump Undo Changes Dump Undo的改变 ALTER SESSION SET EVENTS ‘10221 trace name context forever, level 7’;
Event 10224 – Dump Index Block Splits / Deletes 索引块的分裂和D删除信息 ALTER SESSION SET EVENTS ‘10224 trace name context forever, level 1’;
Event 10225 – Dump Changes to Dictionary Managed Extents DUMP字段管理的扩展变化 ALTER SESSION SET EVENTS ‘10225 trace name context forever, level 1’;
Event 10231 全表扫描时跳过坏块,在有坏块的情况下做数据拯救时很有用 ALTER SYSTEM SET EVENTS ‘10231 trace name context forever,level 10’;
Event 10241 – Dump Remote SQL Execution 远程SQL语句的执行信息 ALTER SESSION SET EVENTS ‘10241 trace name context forever, level 1’;
Event 10246 – Trace PMON Process 跟踪PMON进程 只能修改参数,不能用ALTER SYSTEM 

event = “10246 trace name context forever, level 1”

Event 10248 – Trace Dispatcher Processes 跟踪DISPATCHER的工作情况 event = “10248 trace name context forever, level 10”
Event 10249 – Trace Shared Server (MTS) Processes- 跟踪共享服务器的工作情况 event = “10249 trace name context forever, level 10”
Event 10270 – Debug Shared Cursors 跟踪共享CURSORS的情况 event = “10270 trace name context forever, level 10”
Event 10299 – Debug Prefetching 跟踪表数据块和索引数据块的PREFETCHING event = “10299 trace name context forever, level 1”
Event 10357 – Debug Direct Path ALTER SESSION SET EVENTS ‘10357 trace name context forever, level 1’;
Event 10390 – Dump Parallel Execution Slave Statistics 跟踪并行操作中的SLAVE的状态 ALTER SESSION SET EVENTS ‘10390 trace name context forever, level 1;
Event 10391-Dump Parallel Execution Granule Allocation 跟踪并行操作的粒度 ALTER SESSION SET EVENTS ‘10391 trace name context forever, level 2’;
Event 10393 – Dump Parallel Execution Statistics 跟踪并行操作的状态(每个SLAVE单独列出状态) ALTER SESSION SET EVENTS ‘10393 trace name context forever, level 1’;
Event 10500 – Trace SMON Process 跟踪SMON进程 event = “10500 trace name context forever, level 1”
Event 10608 – Trace Bitmap Index Creation 跟踪BITMAP索引创建的详细过程 ALTER SESSION SET EVENTS ‘10608 trace name context forever, level 10’;
Event 10704 – Trace Enqueues 跟踪锁的使用情况 ALTER SESSION SET EVENTS ‘10704 trace name context forever, level 1’;
Event 10706 – Trace Global Enqueue Manipulation 跟踪全局锁的使用情况 ALTER SESSION SET EVENTS ‘10706 trace name context forever, level 1’;
Event 10708 – Trace RAC Buffer Cache 跟踪RAC环境下的BUFFER CACHE ALTER SESSION SET EVENTS ‘10708 trace name context forever, level 10’;
事件 说明 例子
Event 10710 – Trace Bitmap Index Access 跟踪位图索引的访问情况 ALTER SESSION SET EVENTS ‘10710 trace name context forever, level 1’;
Event 10711 – Trace Bitmap Index Merge Operation 跟踪位图索引合并操作 ALTER SESSION SET EVENTS ‘10711 trace name context forever, level 1’;
Event 10712 – Trace Bitmap Index OR Operation 跟踪位图索引或操作情况 ALTER SESSION SET EVENTS ‘10712 trace name context forever, level 1’;
Event 10713 – Trace Bitmap Index AND Operation 跟踪位图索引与操作 ALTER SESSION SET EVENTS ‘10713 trace name context forever, level 1’;
Event 10714 – Trace Bitmap Index MINUS Operation 跟踪位图索引minus操作 ALTER SESSION SET EVENTS ‘10714 trace name context forever, level 1’;
Event 10715 – Trace Bitmap Index Conversion to ROWIDs Operation 跟踪位图索引转换ROWID操作 ALTER SESSION SET EVENTS ‘10715 trace name context forever, level 1’;
Event 10716 – Trace Bitmap Index Compress/Decompress 跟踪位图索引压缩和解压缩情况 ALTER SESSION SET EVENTS ‘10716 trace name context forever, level 1’;
Event 10717 – Trace Bitmap Index Compaction ALTER SESSION SET EVENTS ‘10717 trace name context forever, level 1’;
Event 10719 – Trace Bitmap Index DML 跟踪位图索引列的DML操作(引起位图索引改变的DML操作) ALTER SESSION SET EVENTS ‘10719 trace name context forever, level 1’;
Event 10730 – Trace Fine Grained Access Predicates 跟踪细粒度审计的断语 ALTER SESSION SET EVENTS ‘10730 trace name context forever, level 1’;
Event 10731 – Trace CURSOR Statements 跟踪CURSOR的语句情况 ALTER SESSION SET EVENTS ‘10731 trace name context forever, level level’; 

LEVEL定义

1     Print parent query and subquery

2     Print subquery only

Event 10928 – Trace PL/SQL Execution 跟踪PL/SQL执行情况 ALTER SESSION SET EVENTS ‘10928 trace name context forever, level 1’;
Event 10938 – Dump PL/SQL Execution Statistics 跟踪PL/SQL执行状态。使用前需要执行rdbms/admin下的tracetab.sql ALTER SESSION SET EVENTS ‘10938 trace name context forever, level 1’;
flush_cache 刷新BUFFER CACHE ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;
DROP_SEGMENTS 手工删除临时段。当这些临时段无法自动清除的时候可以手工清除 alter session set events ‘immediate trace name DROP_SEGMENTS level ts#+1’; 

ts#是指要删除临时段的表空间的ts#

 

Script:Datafile Report

以下脚本用于列出Oracle中数据文件的状况:

REM Datafile Report

set linesize 120 pagesize 1400;

SELECT t.tablespace_name,
       'Datafile' file_type,
       t.status tablespace_status,
       d.status file_status,
       ROUND((d.bytes - NVL(f.sum_bytes, 0)) / 1048576) used_mb,
       ROUND(NVL(f.sum_bytes, 0) / 1048576) free_mb,
       t.initial_extent,
       t.next_extent,
       t.min_extents,
       t.max_extents,
       t.pct_increase,
       d.file_name,
       d.file_id,
       d.autoextensible,
       d.maxblocks,
       d.maxbytes,
       nvl(d.increment_by, 0) increment_by,
       t.block_size
  FROM (SELECT tablespace_name, file_id, SUM(bytes) sum_bytes
          FROM DBA_FREE_SPACE
         GROUP BY tablespace_name, file_id) f,
       DBA_DATA_FILES d,
       DBA_TABLESPACES t
 WHERE t.tablespace_name = d.tablespace_name
   AND f.tablespace_name(+) = d.tablespace_name
   AND f.file_id(+) = d.file_id
 GROUP BY t.tablespace_name,
          d.file_name,
          d.file_id,
          t.initial_extent,
          t.next_extent,
          t.min_extents,
          t.max_extents,
          t.pct_increase,
          t.status,
          d.bytes,
          f.sum_bytes,
          d.status,
          d.AutoExtensible,
          d.maxblocks,
          d.maxbytes,
          d.increment_by,
          t.block_size
UNION ALL
SELECT h.tablespace_name,
       'Tempfile',
       ts.status,
       t.status,
       ROUND(SUM(NVL(p.bytes_used, 0)) / 1048576),
       ROUND(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) /
             1048576),
       -1, -- initial extent
       -1, -- initial extent
       -1, -- min extents
       -1, -- max extents
       -1, -- pct increase
       t.file_name,
       t.file_id,
       t.autoextensible,
       t.maxblocks,
       t.maxbytes,
       nvl(t.increment_by, 0) increment_by,
       ts.block_size
  FROM sys.V_$TEMP_SPACE_HEADER h,
       sys.V_$TEMP_EXTENT_POOL  p,
       sys.DBA_TEMP_FILES       t,
       sys.dba_tablespaces      ts
 WHERE p.file_id(+) = h.file_id
   AND p.tablespace_name(+) = h.tablespace_name
   AND h.file_id = t.file_id
   AND h.tablespace_name = t.tablespace_name
   and ts.tablespace_name = h.tablespace_name
 GROUP BY h.tablespace_name,
          t.status,
          t.file_name,
          t.file_id,
          ts.status,
          t.autoextensible,
          t.maxblocks,
          t.maxbytes,
          t.increment_by,
          ts.block_size
 ORDER BY 1, 5 DESC
/

沪ICP备14014813号

沪公网安备 31010802001379号