Generate AWR Report Between Two Timings (Not Snapshot ID)
Aug 12, 2013
I have a requirement to create a generic script to generate awr report on the basis of two timings . We are planning to do load test on multiple server. The user will key in the start time and the end time of the load test and the script should round off the user entered time to the nearest snapshot id and generate a awr report. I have tried using the dba_hist_snapshot table fields begin_interval_time and end_interval_time column. However it failed.
The servers are running on 11g as well as 10g and few are in RAC and few single instances.
/************************************
* INPUT PARAMETERS
************************************/
UNDEFINE FROM_TIME
UNDEFINE TO_TIME
[Code]....
View 4 Replies
ADVERTISEMENT
Feb 1, 2011
I am trying to generate AWR report for database observation. But I am not getting any snapshot listed there. below is the output of my awrrpt.sql
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1140984076 AFCCV 1 afccv
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1140984076 1 AFCCV afccv SERVICEDB1
Using 1140984076 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots.
Enter value for num_days: 3
Listing the last 3 days of Completed Snapshots
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
View 8 Replies
View Related
Feb 18, 2012
I have a requirement to generate a report in csv file, attached scripts, sql query and actual output and expected output.
In the query, I am hard-coding month&year, when I run in march 2012 it should include march 2012 in output (similarly for every month going forward), to do that I have to modify my query to include march 2012 & preceding months in every individual query, this report needs to be generated once every month.
I am looking for a generic solution which does not need to be modified every month, also in the output,under "Mail File" data should be in ascending order, in the "Total Mailed" all the months should have grand total.
oracle version : 10.2.0.1.0
View 25 Replies
View Related
Feb 6, 2012
Can we collect information(generate report) about user sessions connected, database hit ratios, memory usage and other system metrics of all target databases from OEM grid control repository database. Which tables/views in repository DB will be holding these information.
View 1 Replies
View Related
Jun 14, 2012
I need to generate a report much like a program guide from the script attached. My problem is that my script works, but it's wrong, all the information is wrong. Here's the two queries I came up with;
SELECT CHANNELS.channel_NO,
CHANNELS.CHANNEL_NAME,
PROGRAM_PACKAGES.PACKAGE_NAME,
SUPPLIER_LISTING.PROGRAM_NAME,
[code]...
And
SELECT CHANNELS.channel_NO,
CHANNELS.CHANNEL_NAME,
PROGRAM_PACKAGES.PACKAGE_NAME,
SUPPLIER_LISTING.PROGRAM_NAME,
[code]...
View 9 Replies
View Related
May 17, 2013
*How can we generate the report of backup status,tablesapce(usedf,free space) for all the databases from OEM or RMAN*
1.)we need generate the report of tablespace used,free, archive...
2.)we also need we generate the Backup status report also
View 4 Replies
View Related
May 10, 2010
I am trying to generate (Oracle 10g) ADDM and ASH report using sql query in HTML format. Just like below given AWR report.
e.g. :
SELECT output
FROM
TABLE
(dbms_workload_repository.awr_report_html
(37933856,1,2900,2911 )
);
View 1 Replies
View Related
Mar 25, 2013
I've below test case
CREATE TABLE MYMERCHANT
(
SRNONUMBER(5),
PARENTSRNONUMBER(5),
LEVELNONUMBER(5),
LEVELNAMEVARCHAR2(25)
) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 1, NULL, 101, 'HQ1' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 2, 1, 102, 'DIV1' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 3, 1, 103, 'DIV2' ) ;
[code]........
Structure is like above. There is a Head Quarter (HQ1) under which we have different division like (DIV1 and DIV2). Under division we have different location where our shopping stores are operating and transactions are done (LOC1, LOC2 for DIV1) and (LOC3 for DIV2) respectively.
DESIRED OUTPUT
User can generate reports at two levels i.e. Head Quarter level and Division Level.
When user wants report at Head Quarter Level :
Page 1 (Should Display as below)
HQ1 DIV1 LOC1 450
HQ1 DIV1 LOC2 400
HQ1 DIV2 LOC3 600
Page 2 (Should Display as below)
HQ1 DIV1 LOC1 450
HQ1 DIV1 LOC2 400
Page 3 (Should Display as below)
HQ1 DIV2 LOC3 600
When user wants report at Division Level : eg DIV1
Page 1 (Should Display as below)
HQ1 DIV1 LOC1 450
HQ1 DIV1 LOC2 400
View 2 Replies
View Related
Sep 21, 2010
I want to generate a PDF report from Form Developer 6i.
View 5 Replies
View Related
Jan 10, 2012
I would like to generate HTML awr report and save it to my local machine.
After running $ORACLE_HOME/rdbms/admin/awrrpt.sql in sqlplus and specifying HTML in (Enter value for report_type: HTML) i hvae get numerous html code in my sqlplus prompt.. I want to save the html report in local machine and open it by double clicking on it.. it will be opened in a browser..
View 9 Replies
View Related
May 25, 2011
I would like to generate oracle report and to MS word format? new xml format of MS office?
View 2 Replies
View Related
May 17, 2013
How can we generate the report of backup status,tablesapce(usedf,free space) for all the databases from OEM / RMAN
1.)we need generate the report of tablespace used,free, archive...
2.)How can we generate the Backup status report also
View 3 Replies
View Related
Apr 30, 2013
I have come across a requirement to generate a report of all the users in Hyperion shared services console along with their previlages and also the different licences consumed by the users like Essbase, HFM, Hyperion Planning, Workforce planning etc.... The provisioning report in Shared services is not giving these details.
View 0 Replies
View Related
Aug 25, 2011
Is it possible to save a report directly in a table instead of the typically destination? (file, cache,...)
View 4 Replies
View Related
Oct 11, 2011
I have a table called 'orders' and having date and time of order placed in the field timeplaced. I need to generate a report which is having hourly based number of orders placed . If no order is placed in an hour then it should show 0 for that hour in the result .
create table orders (order_id integer, timeplaced date,last_updated date);
Insert into ORDERS
(ORDER_ID,
TIMEPLACED, LAST_UPDATED)
Values
(1, TO_DATE('10/11/2011 12:53:39', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/11/2011 10:53:39', 'MM/DD/YYYY HH24:MI:SS'));
[code]........
But I need the result for all the 24 hours. Actually row for the hour 1,2,3,4,5,6,8,9,13-23 are not present in the DB
TO_CHAR(TIMEPLACED,'HH')COUNT(*)
00
10
20
30
40
50
60
71
80
90
102
111
122
130
140
150
160
170
180
190
200
210
220
230
View 3 Replies
View Related
Apr 11, 2013
How to generate a report and straight send it to an Email? I want to match student reg number and the parents email provided .
View 1 Replies
View Related
Dec 4, 2010
we have a admin wizard where in there are 850 officials whose details are like
emp id
date of birth
date of joining
basic pay
photo
etc........
i have the photos of the officials out of the data base.i have created a report to generate the details of the officials. what i would like to have is to get the photo of each official from the file which is not in the data base.
i have the command in pre_form read_image_file('path where the photos are','msg.mcn');
this gives the same photo to all the officials. how do i be able to link the photos to their emp id and get it.
View 5 Replies
View Related
Feb 7, 2013
I have only select_catalog_role in my database.
Is it possible to generate statspack report without having access to perfstat account?
View 6 Replies
View Related
Aug 22, 2007
how to generate matrix report to excel file. i have done this . but the exact layout is not getting displayed in the excel file. I have attache the layout i ma trying to export to excel.
View 8 Replies
View Related
Jun 26, 2013
A select statement in our Performance environment running through toad/sql developer takes 1 min in NOde1 and same select takes 2 mins in Node2.
I have run multiple times on both the nodes and making it run first on 2 then on 1 and vice versa, timings remains same.. Node2 takes more time.
Configs,parameters,SGA and other settings are same between 2 Nodes, This is on 2 Node 11g RAC Oracle 11g Release 2.
View 2 Replies
View Related
Nov 24, 2011
I created an xml report of output type 'xsl'. When the report output is generated for more than 65,536 rows then the report out put is not opening. generate excel report output with maximum no of rows.
View 1 Replies
View Related
Sep 23, 2013
how to write pl/sql to create date o/p like .
since we wnat to design pl/sql report wich will generate monthwise sale from 01jan2009 to 01-sep-2013.
we have created funtion wich will accept argument as employee no, from date and to date.
from_date to_date
01-JAN-2009 01-FEB-2009
01-FEB-2009 01-MAR-2009
01-MAR-2009 01-APR-2009
01-APR-2009 01-MAY-2009
01-may-2009 01-JUN-2009
01-JUN-2009 01-JUL-2009
View 4 Replies
View Related
Oct 31, 2011
the below request.
My company has many products, whenever customer purchase any product purchase timestamp should be inserted into product_details table .
Note: timestamp of UK, Philippines, Singapore, India time should be inserted automatically as my company is world based company .
I have tried to convert the date format to other(uk,singpore,manalia) timings but i couldn't get proper reply.
SELECT to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM') INDIA,
to_char(new_time(sysdate, 'EST', 'GMT'),'DD-MON-YYYY HH:MI:SS AM') D2 ,
to_char(new_time(sysdate, 'PST', 'GMT'),'DD-MON-YYYY HH:MI:SS AM') D3 ,
to_char(new_time(sysdate, 'EST', 'PST'),'DD-MON-YYYY HH:MI:SS AM') D4
FROM dual
I got the below output.
INDIA D2 D3 D4
----------------------- ----------------------- ----------------------- -----------------------
31-OCT-2011 06:06:16 PM 31-OCT-2011 11:06:16 PM 01-NOV-2011 02:06:16 AM 31-OCT-2011 03:06:16 PM
View 9 Replies
View Related
Sep 16, 2002
while trying to generate a PDF or HTML file from a report previewer, I came upon a REP-0999 error message and the previewer was closed!
View 8 Replies
View Related
Sep 28, 2011
I got a issue with a query to fetch records between two dates for fixed timings
Date
From 29-09-2011 to 04-10-2011
Time
From 00:00:00hrs to 08:00:00hrs
I tried the below queries, it doesnt work
select a.detectorid,sum(b.totalvolume),a.updatetime,a.averagespeed from traffic_data a left outer join volume_data b on a.traffic_id=b.traffic_data_id
where pollinterval=1 and detectorid=�AIDC_0154� and updatetime between to_date(�29-aug-2011:00:00:00�,�DD-MON-YYYY:HH24:MI:SS�)
[code]...
View 1 Replies
View Related
Apr 29, 2010
I am getting the ORA-01555 Snapshot Too Old error while excecuting my procedure (which is running in OLTP system). My procedure contains one table which master table of my DB which holds 1.2 GB of data. The proc is using the MERGE statement which is inserting or updating the data in the master table, while the condition of MERGE is using the subquery in which 2 DBlink table is used (in which one table is updating quite frequently). The proc. is executing in the interval of 6 hrs i.e 4 times a day and its is scheduled in job. find the proc. as an attachment.
View 3 Replies
View Related
Apr 13, 2008
I am unable to drop a snapshot , i tried even from sys, it is giving the following error,
drop snapshot adm.dup_resource_status
9:46:29 ORA-08103: object no longer exists
but, when i try to create a new snapshot with the same name
CREATE MATERIALIZED VIEW ADM.DUP_RESOURCE_STATUS
..............
I get
ORA-12006: a materialized view with the same user.name already exists.
View 31 Replies
View Related
Aug 29, 2012
I wish to know about Controlfile Snapshot in RMAN. Is this equalent to controlfile backup?
View 4 Replies
View Related
Jan 12, 2012
I have a problem regarding with Oracle Flashback Query.if I'm doing flashback a database two hours before,I'm receiving ORA-01555: snapshot too old error.My question is:How to able to extend the based on the hour or day?
View 1 Replies
View Related
Feb 2, 2008
Can i create an index on a snapshot... to increase the performance of searching.... in Oracle 8i.... provide with syntax...
Or else tell me how to increase the speed of a query on Snapshot.
View 1 Replies
View Related
