Different Block Size of ORACLE Oracle 9i supports tablespaces with different block sizes. This allows large tables and indexes to have a larger block size than smaller objects. It also means that tablespaces can be transported between databases with differing block sizes. The database is… Read More
Storage Management in Oracle -3 In article we will learn creating tablespaces with storage parameters(DEMO). Before reading this article please check previous articles. Storage Management in Oracle -2 Demo On Storage Management : To view information about storage management use DBA_TABLESPACES or V$TABLESPACE
|
1 2 3 4 5 6 7 8 9 10 11 |
SYS>> desc dba_tablespaceselect TABLESPACE_NAME,EXTENT_MANAGEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces; TS-Name Extent-Man Init-Ext Next-Ext Min-Ext Max-Ext Pct-Inc ---------- ---------- ---------- ---------- ---------- ---------- ---------- SYSTEM DICTIONARY 16384 16384 1 505 50 SYSAUX LOCAL 65536 1 2147483645 UNDOTBS1 LOCAL 65536 1 2147483645 TEMP LOCAL 1048576 1048576 1 0 USERDATA LOCAL 65536 1 2147483645 TEST LOCAL 65536 1 2147483645 6 rows selected. |
Note… Read More
Oracle:HEALTH CHECK REPORT(Capturing Archive information). In this article we will implement to capture the Archive information using Shell script. Lets create a table which can store Archive information. Connect to SYS user and execute below statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
CREATE TABLE DBMON.ARCHIVE_USAGE_HISTORY ( GENERATION_DATE DATE NOT NULL, INST_ID NUMBER NOT NULL, TOTAL_DAY_GB NUMBER ) TABLESPACE DATA01 RESULT_CACHE (MODE DEFAULT) PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; |
Create unique index and primary key on ARCHIVE_USAGE_HISTORY… Read More
Storage Management in Oracle -2 In this article we will learn about Storage Parameters . Before reading this article please go through with previous article. Storage Management in Oracle -1 Storage Hierarchy Summary In summary, the hierarchy of storage in Oracle is as follows: 1.… Read More
Storage Management in Oracle -1 In this article we will learn about Storage Management . A Database is a collection of Logical and Physical Phases.In Logical phase we have DB objects. A Tablespace is a logical storage container in Oracle that comes at the top… Read More
E-Business Suite(EBS) ARCHITECTURE-1 Hello Everyone previously we learnt about Introduction to apps dba and EBS. Please check below link. ROLES & RESPONSIBILITIES OF ORACLE APPS DBA Now let us see about EBS architecture. When we come to oracle EBS basically it is a triple tier… Read More
Dear Readers, In this article, we will see the following Moving Online Data files in Oracle 12c. In order to rename, move and relocate a datafile up to 11g, we had to put a tablespace or database completely offline. As we can see here drawback… Read More
INTRODUCTION TO ORACLE APPS DBA Oracle APPS DBA: What is Application Database? The Database which is storing the data’s from application (say front end ) that is EBS (Enterprise Business suite) is said to be Application Database . What is EBS? Oracle E-Business Suite is… Read More
Dear Readers, In this article, we will see the following Multiple Indexes On the Same Set Of Column in Oracle 12c. You can create multiple indexes on the same set of columns but only one of the indexes can be visible at a time and… Read More
Oracle :How to find Historical TOP SQL’s. In this article we will see how to find out historical TOP SQL’s. The SQL Query to find Historical Top SQL’s.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
set lines 235 set numwidth 20 set pages 3000 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 99999 set feedback off break on plan_hash_value on startup_time skip 1 break on plan_hash_value on TIME break on TIME col time for a30 select * from ( select ss.instance_number node,sql_id, to_Char(begin_interval_time,'MM-DD-YY HH24:MI') Time, sum(nvl(executions_delta,0)) execs, round(sum((elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 ),2) avg_etime, round(sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))),2) AVG_LIO, round(sum((DISK_READS_DELTA/decode(nvl(DISK_READS_DELTA,0),0,1,executions_delta))),2) AVG_PHR , round(sum((ROWS_PROCESSED_DELTA/decode(nvl(ROWS_PROCESSED_DELTA,0),0,1,executions_delta))),2) AVG_ROWS , round(sum((CPU_TIME_DELTA/decode(nvl(CPU_TIME_DELTA,0),0,1,executions_delta))/1000000),2) AVG_CPU, round(sum(CPU_TIME_DELTA)/1000000,2) TOTAL_CPU_TIME, RANK() OVER (PARTITION BY to_Char(begin_interval_time,'MM-DD-YY HH24:MI') ORDER BY sum(CPU_TIME_DELTA)*1000000 desc ) RANK from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where ss.snap_id = S.snap_id and ss.snap_id > (select max(snap_id)-1000 from DBA_HIST_SNAPSHOT ) and ss.instance_number = S.instance_number and executions_delta > 0 and begin_interval_time between to_date('27-JAN-2019 04:20','DD-MON-YYYY HH24:MI') and to_date('31-JAN-2019 06:30','DD-MON-YYYY HH24:MI') group by ss.instance_number,sql_id, to_Char(begin_interval_time,'MM-DD-YY HH24:MI') order by to_Char(begin_interval_time, 'MM-DD-YY HH24:MI'),sum(CPU_TIME_DELTA)*1000000,sum((CPU_TIME_DELTA/decode(nvl(CPU_TIME_DELTA,0),0,1,executions_delta))/1000000) ) where RANK<=10; |
Output : In screenshot Output in Text Format :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
NODE SQL_ID TIME EXECS AVG_ETIME AVG_LIO AVG_PHR AVG_ROWS AVG_CPU TOTAL_CPU_TIME RANK ------ ------------- ------------------------------ ------------ ------------ -------------- -------------------- -------------------- -------------------- -------------------- -------------------- 1 7kmbrw7q8hn4g 01-27-19 14:00 60 .010 78.0 0 1 .01 .32 10 1 3wrrjm9qtr2my 1 .390 482.0 0 2 .37 .37 9 1 4y1y43113gv8f 601 .000 50.6 0 15.18 0 .54 8 1 7hu2k3a31b6j7 614 .000 79.2 0 15.45 0 .57 7 1 6wrwqq7jkmv3w 1 .730 506.0 0 6 .72 .72 6 1 3xjw1ncw5vh27 21 .040 24,029.0 0 0 .04 .85 5 1 74cpnuu24wmx7 3 .470 18,446.3 0 2 .42 1.27 4 1 6mcpb06rctk0x 1 3.180 594,799.0 9 0 3.03 3.03 3 1 381t19fqhxdgp 1 5.180 28,698.0 2 75 5.07 5.07 2 1 b6usrg82hwsa3 1 11.360 314,544.0 22 0 11.21 11.21 1 1 39k4gf5t0831y 01-27-19 15:00 14 .000 10.4 0 0 0 .02 10 1 22356bkgsdcnh 12 .000 .0 0 1 0 .03 9 1 357cru8xpxh55 60 .000 3.0 0 0 0 .04 8 1 c9umxngkc3byq 60 .000 .0 0 0 0 .05 7 1 6ajkhukk78nsr 1 .060 1,156.0 0 1 .06 .06 6 1 d9vzav10pcpfh 60 .000 60.0 0 1 0 .06 5 1 fuws5bqghb2qh 60 .000 9.0 0 0 0 .07 4 1 772s25v1y0x8k 120 .000 .0 0 28 0 .1 3 1 79vddxwy7qz3b 3 .070 3,009.3 0 6.67 .07 .2 2 1 7kmbrw7q8hn4g 60 .010 78.0 0 1 .01 .32 1 1 2fh3rvu66aaqa 01-30-19 18:09 1 .210 369.0 11 1 .1 .1 10 1 7kmbrw7q8hn4g 11 .040 309.7 18.09 1 .01 .11 9 1 a6ygk0r9s5xuj 10 .010 71.5 1.2 0 .01 .12 8 1 02577v815yp77 1 .220 4,909.0 244 1 .13 .13 7 1 4phvdvx32a3mf 10 .020 162.4 3.3 1 .02 .18 6 1 bxywuzvtp6wjg 1 .400 4,753.0 166 0 .24 .24 5 1 3wrrjm9qtr2my 1 .840 1,232.0 56 2 .39 .39 4 1 644jqx2hk7qg0 1 .600 .0 0 1 .6 .6 3 1 12a2xbmwn5v6z 1 1.720 881,392.0 2643 3796 .71 .71 2 1 f6cz4n8y72xdc 1 2.510 914,468.0 2673 1 1.5 1.5 1 |
