SYSTEM

SYS_SQLHIST

This function return database total SQL information like below.

							 select DBSCHEMA,
TOTAL_SQL_CNT,
UNIQUE_SQL_CNT,
PHYSICAL_IO,
DISK_IO,
EXEC_TIME_AVG,
EXEC_TIME_MAX,
WORST_SQL_10SEC,
WAITING_TIME,
USED_MEMORY,
CPU_TIME from table(h.SYS_SQLHIST)
DB1 712 300 253 0 0.02 0.84 0 0 165143 52047
DB2 2342 183 46 0 0.01 10.86 1 0 101305 8212324

Usage

In case of user define specific database schema name, it return full SQL history.

							 select DBSCHEMA,LOADTIME,SERVICE,PHYSICAL_IO,DISK_IO,EXEC_TIME,EXECUTION,SQL_ID,SQL_TEXT 
from
table(h.SYS_SQLHIST,'DB1')
DB1 2015-04-13/20:20:03 SYS$USERS 0 0 10.87 0 1gt687xv0hrfp BEGIN :EXEC_STR := SYS.DBMS_EXPORT_EXTENSION.POST_TABLES(:OWNER, :TABNAME); END;
DB1 2015-04-14/21:08:28 SYS$USERS 2409 0 .09 4 96ux18zx7r57t select * from ( SELECT o.OBJECT_NAME, o.OBJECT_ID ,'' short_name, decode(bitand(t.property, 32), 32, 'YES', 'NO') partitioned, decode(bitand(t.property, 64), 64, 'IOT', decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW', decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null))) iot_type, o.OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, case when xt.obj# is null then 'N' else 'Y' end EXTERNAL FROM SYS.Dba_OBJECTS O ,sys.tab$ t, sys.external_tab$ xt WHERE O.OWNER = :SCHEMA and o.object_id = t.obj#(+) and o.object_id = xt.obj#(+) AND O.OBJECT_TYPE = 'TABLE' union all SELECT OBJECT_NAME, OBJECT_ID , syn.SYNONYM_NAME short_NAME, decode(bitand(t.property, 32), 32, 'YES', 'NO') partitioned, decode(bitand(t.property, 64), 64, 'IOT', decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW', decode(bitand(t.flags, 536870912), 53687091

Usage

In case of user define specific database schema name and SQL Text.

							 select DBSCHEMA,LOADTIME,SERVICE,PHYSICAL_IO,DISK_IO,EXEC_TIME,EXECUTION,SQL_ID,SQL_TEXT 
from
table(h.SYS_SQLHIST,'DB1','BEGIN')
DB1 2015-04-13/20:20:03 SYS$USERS 0 0 10.87 0 1gt687xv0hrfp BEGIN :EXEC_STR := SYS.DBMS_EXPORT_EXTENSION.POST_TABLES(:OWNER, :TABNAME); END;
DB1 2015-04-13/20:20:01 orcl 19000 0 .06 86647 1h2tv9vvk0xbx BEGIN hs_select.hsstart; END;

SYS_DIAGNOSIS

This function check your database status automatically.

							 select * from table(h.SYS_DIAGNOSIS)

Check list

1) Oracle Instance Information.
2) Resource Limit.
3) Current Session Count.
4) Buffer Cache Hit Ratio.
5) Library Cache Hit Ratio.
6) Dictionary Cache Hit Ratio.
7) Shared Pool Free Space.
8) Redo Log Switch Time Interval.
9) File I/O.
10) Memory Sort Ratio.
11) System Wait Event.
12) Invalid Object Count.
13) Rollback Segment Hit Ratio.
14) Tablespace Usage and Fragmentation.
15) Chained Row Ratio.

				 
============================================================================================================================================
Oracle Instance Information
============================================================================================================================================
Host                          Version      DB Name      Startup Time  Parallel      Parallel      Log Mode      Archiver      
--------------------------------------------------------------------------------------------------------------------------------------------
hadstore                      11.2.0.3.0   ORCL         15/04/13      NO            NOARCHIVELOG  STOPPED       
============================================================================================================================================
Resource Limit
============================================================================================================================================
Resource Name                 Current Util Max Util     Initial Alloc Limit         
--------------------------------------------------------------------------------------------------------------------------------------------
processes                     28           36                 1000          1000    
sessions                      32           42                 1524          1524    
enqueue_locks                 46           73                17460         17460    
enqueue_resources             43           43                 6924     UNLIMITED    
ges_procs                     0            0                     0             0    
ges_ress                      0            0                     0     UNLIMITED    
ges_locks                     0            0                     0     UNLIMITED    
ges_cache_ress                0            0                     0     UNLIMITED    
ges_reg_msgs                  0            0                     0     UNLIMITED    
ges_big_msgs                  0            0                     0     UNLIMITED    
ges_rsv_msgs                  0            0                     0             0    
gcs_resources                 0            0             UNLIMITED     UNLIMITED    
gcs_shadows                   0            0             UNLIMITED     UNLIMITED    
smartio_overhead_memory       0            68632                 0     UNLIMITED    
smartio_buffer_memory         0            0                     0     UNLIMITED    
smartio_metadata_memory       0            0                     0     UNLIMITED    
smartio_sessions              0            1                     0     UNLIMITED    
dml_locks                     0            0                  6704     UNLIMITED    
temporary_table_locks         0            6             UNLIMITED     UNLIMITED    
transactions                  0            0                  1676     UNLIMITED    
branches                      0            0                  1676     UNLIMITED    
cmtcallbk                     0            2                  1676     UNLIMITED    
max_rollback_segments         11           21                 1676         65535    
sort_segment_locks            0            5             UNLIMITED     UNLIMITED    
k2q_locks                     0            0                  3048     UNLIMITED    
max_shared_servers            1            1             UNLIMITED     UNLIMITED    
parallel_max_servers          0            0                     0          3600    
============================================================================================================================================
Current Session Count
============================================================================================================================================
Time                          Total Sessions Active Sessions
--------------------------------------------------------------------------------------------------------------------------------------------
2015/06/13 00:29:54           25             23             
============================================================================================================================================
Buffer Cache Hit Ratio
============================================================================================================================================
Time                          Logical Reads  Physical Reads           Hit Ratio      
--------------------------------------------------------------------------------------------------------------------------------------------
2015/06/13 00:29:54           3,513,699,580  410,804                  99.988         
============================================================================================================================================
Library Cache Hit Ratio
============================================================================================================================================
Time                          Hit Ratio(Pins-Reloads)            
--------------------------------------------------------------------------------------------------------------------------------------------
2015/06/13 00:29:54           99.942                             
============================================================================================================================================
Dictionary Cache Hit Ratio
============================================================================================================================================
Time                          Hit Ratio      
--------------------------------------------------------------------------------------------------------------------------------------------
2015/06/13 00:29:54           99.499         
============================================================================================================================================
Shared Pool Free Space
============================================================================================================================================
Shared Pool Total(MB)         Shared Pool Free(MB)          Shared Pool Reserved(MB)                Shared Pool Other(MB)         
--------------------------------------------------------------------------------------------------------------------------------------------
2,512                         92.62                         Free : 122.12,Used : 15.57              2,281.69                      
============================================================================================================================================
Redo Log Switch Time Interval
============================================================================================================================================
THREAD    TIME                Interval(Min)       
--------------------------------------------------------------------------------------------------------------------------------------------
1         2015/06/11 07       584.87              
1         2015/06/11 09       76.83               
1         2015/06/11 23       233.93              
1         2015/06/12 09       555.57              
1         2015/06/12 17       518.33              
1         2015/06/10 22       585.1               
1         2015/06/11 19       651.23              
1         2015/06/10 12       193.97              
============================================================================================================================================
File I/O 
============================================================================================================================================
Name                                              Physical Read       Physical Write      Total IO            Read(%%)            Write(%%)           Total IO(%%)        Average IO Time     
--------------------------------------------------------------------------------------------------------------------------------------------
/oracle/oradata/orcl/sysaux01.dbf                 199,257             354,308             553,565             57.36               57.34               57.35               0                   
/oracle/oradata/orcl/system01.dbf                 88,144              68,355              156,499             25.37               11.06               16.21               0                   
/oracle/oradata/orcl/undotbs01.dbf                22                  146,479             146,501             0                   23.7                15.17               0                   
/oracle/oradata/orcl/sysaux02.dbf                 438                 19,013              19,451              .12                 3.07                2.01                0                   
/oracle/oradata/orcl/hs_info_data2.dbf            16,387              2,213               18,600              4.71                .35                 1.92                0                   
/oracle/oradata/orcl/hs_info_data4.dbf            12,757              1,934               14,691              3.67                .31                 1.52                0                   
/oracle/oradata/orcl/hs_info_data3.dbf            11,626              2,551               14,177              3.34                .41                 1.46                0                   
/oracle/oradata/orcl/hs_info_data1.dbf            7,675               4,165               11,840              2.2                 .67                 1.22                0                   
/oracle/oradata/orcl/hs_info_data5.dbf            3,751               985                 4,736               1.07                .15                 .49                 0                   
/oracle/oradata/orcl/sysaux03.dbf                 107                 4,306               4,413               .03                 .69                 .45                 0                   
/oracle/oradata/orcl/hs_info_idx1.dbf             2,091               2,077               4,168               .6                  .33                 .43                 0                   
/oracle/oradata/orcl/system02.dbf                 47                  3,093               3,140               .01                 .5                  .32                 0                   
/oracle/oradata/orcl/hs_weight_idx1.dbf           331                 2,102               2,433               .09                 .34                 .25                 0                   
/oracle/oradata/orcl/users01.dbf                  2,214               7                   2,221               .63                 0                   .23                 0                   
/oracle/oradata/orcl/hs_weight_data2.dbf          321                 1,886               2,207               .09                 .3                  .22                 0                   
/oracle/oradata/orcl/hs_info_idx2.dbf             389                 1,815               2,204               .11                 .29                 .22                 0                   
/oracle/oradata/orcl/hs_hist_idx1.dbf             1,261               334                 1,595               .36                 .05                 .16                 0                   
/oracle/oradata/orcl/system03.dbf                 3                   1,171               1,174               0                   .18                 .12                 0                   
/oracle/oradata/orcl/system04.dbf                 2                   819                 821                 0                   .13                 .08                 0                   
/oracle/oradata/orcl/hs_weight_idx3.dbf           252                 16                  268                 .07                 0                   .02                 0                   
/oracle/oradata/orcl/hs_hist_data2.dbf            142                 94                  236                 .04                 .01                 .02                 0                   
/oracle/oradata/orcl/hs_hist_idx2.dbf             34                  97                  131                 0                   .01                 .01                 0                   
/oracle/oradata/orcl/hs_weight_data1.dbf          49                  24                  73                  .01                 0                   0                   0                   
/oracle/oradata/orcl/hs_hist_data1.dbf            42                  10                  52                  .01                 0                   0                   0                   
/oracle/oradata/orcl/hs_third_part1.dbf           2                   0                   2                   0                   0                   0                   0                   
/oracle/oradata/orcl/hs_weight_idx2.dbf           2                   0                   2                   0                   0                   0                   0                   
/oracle/oradata/orcl/hs_hist_data5.dbf            2                   0                   2                   0                   0                   0                   0                   
/oracle/oradata/orcl/hs_hist_data4.dbf            2                   0                   2                   0                   0                   0                   0                   
/oracle/oradata/orcl/hs_hist_data3.dbf            2                   0                   2                   0                   0                   0                   0                   
/oracle/oradata/orcl/hs_weight_data5.dbf          2                   0                   2                   0                   0                   0                   0                   
/oracle/oradata/orcl/hs_weight_data4.dbf          2                   0                   2                   0                   0                   0                   0                   
/oracle/oradata/orcl/hs_weight_data3.dbf          2                   0                   2                   0                   0                   0                   0                   
============================================================================================================================================
Memory Sort Ratio 
============================================================================================================================================
Sort(memory)        Sort(disk)          Memory Sort Ratio   
--------------------------------------------------------------------------------------------------------------------------------------------
135,134,831         0                   100                 
============================================================================================================================================
System Wait Event 
============================================================================================================================================
Event                                                                Total Waits     Time Waited(cs)   Avg Wait Time(cs)      Total Timeouts
--------------------------------------------------------------------------------------------------------------------------------------------
DIAG idle wait                                                        10,382,821       1,039,328,674               100.1          10,382,821
jobq slave wait                                                       11,137,059         557,692,264               50.08          11,111,914
Space Manager: slave idle wait                                         1,082,251         539,441,771              498.44           1,077,307
dispatcher timer                                                          86,649         519,902,756             6,000.1              86,649
shared server idle wait                                                  173,300         519,901,693            3,000.01             173,295
Streams AQ: qmn coordinator idle wait                                    371,240         519,900,567            1,400.44             185,618
Streams AQ: qmn slave idle wait                                          185,618         519,899,740            2,800.91                   0
Streams AQ: waiting for time management or cleanup tasks                     213         516,078,597        2,422,904.21                 111
VKRM Idle                                                                     60         178,559,932        2,975,998.87                   0
os thread startup                                                        194,359             357,115                1.84                   0
log file parallel write                                                1,926,635             178,074                 .09                   0
log file sync                                                            523,035             138,901                 .27                   0
control file parallel write                                            1,850,856             134,298                 .07                   0
db file async I/O submit                                                 100,484              33,557                 .33                   0
ADR block file read                                                       27,860              17,013                 .61                   0
PL/SQL lock timer                                                            125              12,512               100.1                 125
latch free                                                                 7,215               5,268                 .73                   0
Disk file operations I/O                                                 450,600               4,348                 .01                   0
library cache: mutex X                                                    19,938               3,183                 .16                   0
control file sequential read                                           3,439,452               2,103                   0                   0
db file sequential read                                                  347,341               1,292                   0                   0
enq: CR - block range reuse ckpt                                           4,378                 918                 .21                   0
db file single write                                                       6,800                 592                 .09                   0
db file scattered read                                                     2,897                 512                 .18                   0
asynch descriptor resize                                                 865,861                 511                   0             865,859
control file heartbeat                                                         1                 400              400.11                   1
Data file init write                                                       1,028                 361                 .35                   0
ADR block file write                                                      10,573                 349                 .03                   0
buffer busy waits                                                         15,023                 220                 .01                   0
direct path sync                                                             531                 147                 .28                   0
latch: shared pool                                                        42,738                 111                   0                   0
local write wait                                                             511                  94                 .18                   0
class slave wait                                                          21,280                  80                   0                   0
db file parallel read                                                        160                  77                 .48                   0
enq: RO - fast object reuse                                                3,448                  75                 .02                   0
log file switch completion                                                    67                  61                 .92                   0
reliable message                                                           8,917                  40                   0                   0
JS coord start wait                                                            1                  35               35.43                   0
enq: PR - contention                                                          24                  27                1.14                   0
cursor: pin S                                                              2,485                  26                 .01                   0
log buffer space                                                               2                  23               11.73                   0
resmgr:internal state change                                                   2                  20               10.14                   2
latch: call allocation                                                     2,218                  18                 .01                   0
LGWR wait for redo copy                                                    6,804                  13                   0                   0
log file single write                                                        426                  13                 .03                   0
direct path write temp                                                     1,380                  12                 .01                   0
direct path read                                                           1,896                   8                   0                   0
enq: TC - contention                                                           4                   8                1.96                   0
latch: row cache objects                                                   2,598                   6                   0                   0
latch: cache buffers chains                                                1,178                   6                   0                   0
ADR file lock                                                              8,824                   3                   0                   0
enq: HW - contention                                                          73                   3                 .04                   0
Parameter File I/O                                                            96                   2                 .02                   0
latch: enqueue hash chains                                                   339                   2                   0                   0
cursor: pin S wait on X                                                        3                   2                 .77                   0
enq: JS - queue lock                                                          52                   1                 .02                   0
undo segment extension                                                         6                   1                 .19                   6
cursor: mutex S                                                                2                   1                 .58                   0
library cache lock                                                             1                   1                 .86                   0
utl_file I/O                                                                 315                   1                   0                   0
direct path write                                                             71                   1                 .01                   0
============================================================================================================================================
Invalid Object Count 
============================================================================================================================================
Object Type                Invalid Count
--------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE                              1
PACKAGE BODY                           1
FUNCTION                               1
============================================================================================================================================
Rollback Segment Hit Ratio 
============================================================================================================================================
Rollback Segment                             Rsize              WritesXacts               Status              Waits               Gets                Hit ratio           
--------------------------------------------------------------------------------------------------------------------------------------------
_SYSSMU8_2687183721$                    37,937,152         262,942,8640                   ONLINE              22                  467,429             99.99529            
_SYSSMU3_304315760$                     50,454,528         292,395,3880                   ONLINE              21                  471,138             99.99554            
_SYSSMU10_3585045185$                   48,357,376         288,007,2860                   ONLINE              18                  473,636             99.99619            
_SYSSMU6_2110685595$                    42,065,920         265,424,1360                   ONLINE              17                  467,363             99.99636            
_SYSSMU1_996739248$                     33,677,312         248,420,2500                   ONLINE              16                  467,989             99.99658            
_SYSSMU2_3216160665$                    36,823,040         247,166,9980                   ONLINE              15                  467,002             99.99678            
_SYSSMU9_3002152463$                    42,590,208         255,305,2580                   ONLINE              13                  465,883             99.9972             
_SYSSMU5_2304325666$                    43,245,568         262,982,2280                   ONLINE              13                  470,096             99.99723            
_SYSSMU4_1884028219$                    43,114,496         258,956,4940                   ONLINE              11                  468,160             99.99765            
_SYSSMU7_3897603324$                    34,725,888         287,518,4880                   ONLINE              10                  470,645             99.99787            
SYSTEM                                     385,024             112,7960                   ONLINE              0                   18,157              100                 
============================================================================================================================================
Tablespace Usage and Fragmentation 
============================================================================================================================================
Tablespace                          Total(MB)       Used(MB)       Free(MB)  Free(%%)    Pieces    MaxFree(MB)
--------------------------------------------------------------------------------------------------------------------------------------------
UNDOTBS1                                  500         394.94         105.06        21        16             97
SYSTEM                                  2,260       1,443.31         816.69        36         6            331
USERS                                       5           2.88           2.13        43        15           1.25
SYSAUX                                  1,700            710            990        58        18            488
HS_INFO_DATA                            2,500         132.25       2,367.75        95        20            473
HS_INFO_IDX                             1,000           7.06         992.94        99         3            497
HS_HIST_IDX                             1,000           6.63         993.38        99         2            498
HS_HIST_DATA                            2,500           6.69       2,493.31       100         5            499
HS_WEIGHT_IDX                           1,500           5.44       1,494.56       100         3            499
HS_WEIGHT_DATA                          2,500           6.56       2,493.44       100         5            499
HS_THIRD_PART                             500              1            499       100         1            499
============================================================================================================================================
Chained Row Ratio
============================================================================================================================================
Chained row Access  Index Access        Table Scan Access   Ratio               
--------------------------------------------------------------------------------------------------------------------------------------------
14,540              2,270,718,721       8,624,555,570       .00013              

SYS_DEPENDENCY

This function check dependency of user defined object.
SYS_DEPENDENCY(dbusername,any obejctname)
object name can be table, type, procedure , function name ...

							 select * from table(h.SYS_DEPENDENCY('HADSTORE','HSAPPLICATIONINFO'))
			   
   +-PACKAGE "HADSTORE"."HS_CONSTANTS"        PACKAGE BODY "HADSTORE"."HS_EXECUTION"        14/09/13
      +-PACKAGE "HADSTORE"."HS_CONSTANTS"     PACKAGE BODY "HADSTORE"."HS_EXECUTION"        14/09/13
   +-PACKAGE "HADSTORE"."HS_CONSTANTS"        PACKAGE BODY "HADSTORE"."HS_SELECT"           14/07/07
      +-PACKAGE "HADSTORE"."HS_CONSTANTS"     PACKAGE BODY "HADSTORE"."HS_SELECT"           14/07/07
   +-PACKAGE "HADSTORE"."HS_CONSTANTS"        PACKAGE BODY "HADSTORE"."HS_SUPPORT"          14/09/13
      +-PACKAGE "HADSTORE"."HS_CONSTANTS"     PACKAGE BODY "HADSTORE"."HS_SUPPORT"          14/09/13
   +-PACKAGE "HADSTORE"."HS_CONSTANTS"        PACKAGE BODY "HADSTORE"."HS_UTIL"             14/09/13
      +-PACKAGE "HADSTORE"."HS_CONSTANTS"     PACKAGE BODY "HADSTORE"."HS_UTIL"             14/09/13
   +-PACKAGE "HADSTORE"."HS_TYPES"            PACKAGE "HADSTORE"."HS_CONSTANTS"             14/09/13
   +-PACKAGE "HADSTORE"."HS_TYPES"            PACKAGE "HADSTORE"."HS_UTIL"                  13/05/21
   +-PACKAGE "HADSTORE"."HS_TYPES"            PACKAGE "HADSTORE"."HS_VARIABLES"             14/03/15
   +-PACKAGE "HADSTORE"."HS_TYPES"            PACKAGE BODY "HADSTORE"."HS_EXECUTION"        14/09/13
   +-PACKAGE "HADSTORE"."HS_TYPES"            PACKAGE BODY "HADSTORE"."HS_SUPPORT"          14/09/13
   +-PACKAGE "HADSTORE"."HS_TYPES"            PACKAGE BODY "HADSTORE"."HS_UTIL"             14/09/13
      +-PACKAGE "HADSTORE"."HS_UTIL"          PACKAGE BODY "HADSTORE"."HS_EXECUTION"        14/09/13
      +-PACKAGE "HADSTORE"."HS_UTIL"          PACKAGE BODY "HADSTORE"."HS_SUPPORT"          14/09/13
      +-PACKAGE "HADSTORE"."HS_UTIL"          PACKAGE BODY "HADSTORE"."HS_UTIL"             14/09/13
      +-PACKAGE "HADSTORE"."HS_VARIABLES"     PACKAGE BODY "HADSTORE"."HS_EXECUTION"        14/09/13
      +-PACKAGE "HADSTORE"."HS_VARIABLES"     PACKAGE BODY "HADSTORE"."HS_SUPPORT"          14/09/13
      +-PACKAGE "HADSTORE"."HS_VARIABLES"     PACKAGE BODY "HADSTORE"."HS_UTIL"             14/09/13
  TABLE "HADSTORE"."HSAPPLICATIONINFO"        PACKAGE "HADSTORE"."HS_CONSTANTS"             14/09/13
  TABLE "HADSTORE"."HSAPPLICATIONINFO"        PACKAGE "HADSTORE"."HS_TYPES"                 14/03/15
  TABLE "HADSTORE"."HSAPPLICATIONINFO"        PACKAGE BODY "HADSTORE"."HS_EXECUTION"        14/09/13
  TABLE "HADSTORE"."HSAPPLICATIONINFO"        PACKAGE BODY "HADSTORE"."HS_SUPPORT"          14/09/13
  TABLE "HADSTORE"."HSAPPLICATIONINFO"        SYNONYM "HADSTOREMGR"."HSAPPLICATIONINFO"     13/04/06

                 

SYS_WHICH

This function find string which used by object.

Search Object list

1) Package
2) Package Body
3) Procedure
4) Function
5) Type
6) Table
7) View
8) Index
9) Synonyms
10) Partition

							 select valdata1,keydata1,valdata2,valdata3  from table(h.SYS_WHICH('BLOCK'))
                      
PACKAGE BODY l_stmt := rpad('Data Block',30,' ')||rpad('Size of database block',40,' ')||lpad('2K/4K/8K/16K',30,' ')||rpad(' 32K(64bit)',40,' '); 72 H$SYSTEM
PACKAGE BODY l_stmt := rpad(' ',30,' ')||rpad('Number of max block of single data file',40,' ')||lpad('4,194,303',30,' ')||rpad(' Block size(4K) = 16GB Data file',40,' '); 74 H$SYSTEM
PACKAGE BODY l_stmt := rpad(' ',30,' ')||rpad('Number of max block of BIGFILE',40,' ')||lpad('4,294,967,295',30,' ')||rpad(' Block size(4K) = 16TB',40,' '); 76 H$SYSTEM
PACKAGE BODY l_stmt := rpad('Extent',30,' ')||rpad('Dictionary mode',40,' ')||lpad('4GB*Block Size',30,' '); 81 H$SYSTEM
PACKAGE BODY l_stmt := rpad(' ',30,' ')||rpad('Local mode',40,' ')||lpad('2GB*Block Size',30,' '); 83 H$SYSTEM
PACKAGE BODY l_stmt := rpad('File',30,' ')||rpad('Size of control file',40,' ')||lpad('Block Size * 20,000',30,' '); 90 H$SYSTEM
PACKAGE BODY l_stmt := rpad(' ',30,' ')||rpad('Size of data file',40,' ')||lpad('4,194,303*Block Size',30,' '); 94 H$SYSTEM
PACKAGE BODY where a.name = ''db block gets'' 395 H$SYSTEM
PACKAGE BODY AND c.name = ''db_block_size'' 1084 H$SYSTEM
TABLE HBLOCK 2015/01/31 04:58:23 VALID
COLUMN BLOCKCODE VARCHAR2 HBLOCK
COLUMN BLOCKCODE VARCHAR2 HLOCATION

SYS_INFO

This function get information of connected database.

							 select * from table(h.SYS_INFO)
			            
terminal               unknown
language               ENGLISH_ENGLISH.AL32UTF8
sessionid              4330399
instance               1
entryid	
isdba                  FALSE
nls_territory          ENGLISH
nls_currency           $
nls_calendar           GREGORIAN
nls_date_format        RR/MM/DD
nls_date_language      ENGLISH
nls_sort               BINARY
current_user           H
current_userid         92
session_user           H
session_userid         92
proxy_user	
proxy_userid	
db_domain	
db_name                orcl
host                   HAD
os_user                HAD
external_name	
ip_address             183.107.129.199
network_protocol       tcp
bg_job_id	
fg_job_id              0
authentication_type    DATABASE
authentication_data	
current_sql	
client_identifier	
global_context_memory  0						
						
						

SYS_DU

This function get each object size in connected database.

  select keydata1,valdata1,h.to_comma(valdata2) kb from table(h.SYS_DU(h.c_kb)) order by to_number(valdata2) desc
			            
HBLOCK              TABLE      73,728
HLOCATION           TABLE      33,792
HCOUNTRY            TABLE      8,192
MSITEINFO           INDEX      256
MUSERINFO           INDEX      128
DR$MS_CTXIDX1$I     TABLE      128
MSITEINFO           TABLE      64
DR$MS_CTXIDX1$R     LOBINDEX   64
DR$MS_CTXIDX1$N     INDEX      64
DR$MS_CTXIDX1$I     LOBINDEX   64
TEST                TABLE      64
DR$MS_CTXIDX1$R     TABLE      64
MCATEGORY_PN2M      TABLE      64
MUSERCATEGORY_PN2M  INDEX      64
MUSERCATEGORY       TABLE      64
DR$MS_CTXIDX1$K     INDEX      64
DR$MS_CTXIDX1$I     INDEX      64
TEST2               TABLE      64
DR$MS_CTXIDX1$I     LOBSEGMENT 64
MUSERINFO           TABLE      64
MCATEGORY           TABLE      64
MPROCLOGSUMMARY     INDEX      64
MPROCLOGSUMMARY     TABLE      64
MUSERCATEGORY       INDEX      64
MUSERCATEGORY_PN2M  TABLE      64
DR$MS_CTXIDX1$R     LOBSEGMENT 64
MLANGUAGESET        TABLE      64
		                
						

SYS_STORAGE

This function get information of all tablespace.

  select * from table(h.SYS_STORAGE)
			            
========================================
Storage Status
========================================
TABLESPACE_NAME FILE_ID   FILE_NAME             SIZE(MB) FREE(MB) AUTO_EXTENSIBLE NEXT_INCREMENT MAX NEXT_EXTEND
-----------------------------------------------------------------------------------------------------------------
HS_HIST_DATA    15        /oracle/oradata/orcl/hs_hist_data1.dbf     500  498    YES     50   2,048   1,498
HS_HIST_DATA    16        /oracle/oradata/orcl/hs_hist_data2.dbf     500  498.3  YES     50   2,048   1,498
HS_HIST_DATA    17        /oracle/oradata/orcl/hs_hist_data3.dbf     500  499    YES     50   2,048   1,498
HS_HIST_DATA    18        /oracle/oradata/orcl/hs_hist_data4.dbf     500  499    YES     50   2,048   1,498
HS_HIST_DATA    19        /oracle/oradata/orcl/hs_hist_data5.dbf     500  499    YES     50   2,048   1,498
HS_HIST_IDX     24        /oracle/oradata/orcl/hs_hist_idx1.dbf      500  495.4  YES     50   2,048   1,498
HS_HIST_IDX     25        /oracle/oradata/orcl/hs_hist_idx2.dbf      500  498    YES     50   2,048   1,498
HS_INFO_DATA    10        /oracle/oradata/orcl/hs_info_data1.dbf     500  474.2  YES     50   2,048   1,498
HS_INFO_DATA    11        /oracle/oradata/orcl/hs_info_data2.dbf     500  466.4  YES     50   2,048   1,498
HS_INFO_DATA    12        /oracle/oradata/orcl/hs_info_data3.dbf     500  475    YES     50   2,048   1,498
HS_INFO_DATA    13        /oracle/oradata/orcl/hs_info_data4.dbf     500  476.1  YES     50   2,048   1,498
HS_INFO_DATA    14        /oracle/oradata/orcl/hs_info_data5.dbf     500  476    YES     50   2,048   1,498
HS_INFO_IDX     22        /oracle/oradata/orcl/hs_info_idx1.dbf      500  495.9  YES     50   2,048   1,498
HS_INFO_IDX     23        /oracle/oradata/orcl/hs_info_idx2.dbf      500  497    YES     50   2,048   1,498
HS_THIRD_PART   32        /oracle/oradata/orcl/hs_third_part1.dbf    500  499    YES     10  32,768  32,258
HS_WEIGHT_DATA   5        /oracle/oradata/orcl/hs_weight_data1.dbf   500  498    YES     50   2,048   1,498
HS_WEIGHT_DATA   6        /oracle/oradata/orcl/hs_weight_data2.dbf   500  498.4  YES     50   2,048   1,498
HS_WEIGHT_DATA   7        /oracle/oradata/orcl/hs_weight_data3.dbf   500  499    YES     50   2,048   1,498
HS_WEIGHT_DATA   8        /oracle/oradata/orcl/hs_weight_data4.dbf   500  499    YES     50   2,048   1,498
HS_WEIGHT_DATA   9        /oracle/oradata/orcl/hs_weight_data5.dbf   500  499    YES     50   2,048   1,498
HS_WEIGHT_IDX   20        /oracle/oradata/orcl/hs_weight_idx1.dbf    500  497    YES     50   2,048   1,498
HS_WEIGHT_IDX   21        /oracle/oradata/orcl/hs_weight_idx2.dbf    500  499    YES     50   2,048   1,498
HS_WEIGHT_IDX   26        /oracle/oradata/orcl/hs_weight_idx3.dbf    500  498.6  YES     0   32,768  32,268
SYSAUX           2        /oracle/oradata/orcl/sysaux01.dbf          700  25.5   YES     10  32,768  32,058
SYSAUX          28        /oracle/oradata/orcl/sysaux02.dbf          500  476.7  YES     0   32,768  32,268
SYSAUX          31        /oracle/oradata/orcl/sysaux03.dbf          500  488    YES     0   32,768  32,268
SYSTEM           1        /oracle/oradata/orcl/system01.dbf          760  5.7    YES     10  32,768  31,998
SYSTEM          27        /oracle/oradata/orcl/system02.dbf          500  157    YES     0   32,768  32,268
SYSTEM          29        /oracle/oradata/orcl/system03.dbf          500  323    YES     0   32,768  32,268
SYSTEM          30        /oracle/oradata/orcl/system04.dbf          500  331    YES     0   32,768  32,268
UNDOTBS1         3        /oracle/oradata/orcl/undotbs01.dbf         500  109.1  NO
USERS            4        /oracle/oradata/orcl/users01.dbf           5    2.1    YES     1.3  32,768 32,761.7						
		                
						

SYS_LIMIT

This function just show limit of each object. just reference.

  select * from table(h.SYS_LIMIT)
			            
SECTION         ITEM                                             LIMIT     DESCRIPTION                            
------------------------------------------------------------------------------------------------------
Schema Object   Number of Column                                  1,000
                Length of Column name                                30
                Length of Table name                                 30
                Length of Sequence                                   38
                Number of join index column                          32
                Number of pk Column                                  32
-------------------------------------------------------------------------------------------------------
Data Block      Size of database block                     2K/4K/8K/16K    32K(64bit)                             
                Number of max block of single data file       4,194,303    Block size(4K) = 16GB Data file        
                Number of max block of BIGFILE            4,294,967,295    Block size(4K) = 16TB                  
-------------------------------------------------------------------------------------------------------
Extent          Dictionary mode                          4GB*Block Size
                Local mode                               2GB*Block Size
                Max extent                                    Unlimited
-------------------------------------------------------------------------------------------------------
File            Size of control file                Block Size * 20,000
                Number of data file                              65,535
                Size of data file                  4,194,303*Block Size
-------------------------------------------------------------------------------------------------------
SQL restriction Size of SQL statement                          65,535 B
                Size of Dynamic SQL                               32 KB
                Size of FROM statement                        Unlimited
                Size of WHERE statement                       Unlimited
                Number of IN list                                 1,000
                Number of GROUP BY                            Unlimited
                Number of ORDER BY                            Unlimited					
					
					

Required privilege for use above functions

grant select on V_$SQLTEXT to USERNAME;
grant select on V_$SQL to USERNAME;
grant select on v_$resource_limit to USERNAME;
grant select on v_$database to USERNAME;
grant select on v_$instance to USERNAME;
grant select on v_$session to USERNAME;
grant select on v_$sysstat to USERNAME;
grant select on v_$librarycache to USERNAME;
grant select on v_$rowcache to USERNAME;
grant select on v_$shared_pool_reserved to USERNAME;
grant select on v_$loghist to USERNAME;
grant select on v_$filestat to USERNAME;
grant select on v_$datafile to USERNAME;
grant select on v_$system_event to USERNAME;
grant select on v_$rollstat to USERNAME;
grant select on v_$rollname to USERNAME;
grant select on v_$option to USERNAME;
grant select on v_$sgastat to USERNAME;
grant select on v_$parameter to USERNAME;
grant select on dba_tablespaces to USERNAME;
grant select on dba_data_files to USERNAME;
grant select on dba_segments to USERNAME;
grant select on dba_free_space to USERNAME;
grant select on dba_dependencies to USERNAME;
grant select on dba_objects to USERAME;