SQL & PL/SQL :: Snap ID - Show Differences Over Time For Historical And Trending
Jun 25, 2013
I have an existing view that stores some data. I was asked to create a table based on the columns present in the view. I only copied the table structure from the view.
Now I would need to create a stored procedure that executes the query and stores the data in the table with a snap id and date of snap column. The reason this needs to be done is to show differences over time for historical and trending.
I have an application in which time is show as . But, in the table in Oracle it is showing as 13:00. The application is taking the time from OS. OS time is 18:30 IST. Time in SYSDATE is also showing as 18:30. DBTIMEZONE is '+5:30'. what is the problem in Database and how that can be rectified to show the time as same as OS time.
One of my colleaque gives the below informations. it displays table count information while gathering stats. But I dont know from which view he picked the historical information. pls let me know the view name to get this detail.
When I want to generate AWR report for database, I dont see any Snap IDs. What was disabled?
Is it possible to generate awr reports for unseen snap IDs?
Here below is the log: Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 49472052 WPSDBSTG 1 wpsdbstg
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 ------------ -------- ------------ ------------ ------------ * 49472052 1 WPSDBSTG wpsdbstg rcolnx88700
Using 49472052 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: 1
Listing the last day's Completed Snapshots
Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap:
I have some questions about Oracle + EMC shared storage. I have Oracle 11gR1 RAC (2nodes) + ASM environment with shared shorage EMC Clariion AX4.
The database is running no archivelog mode. I'd like to implement point-in-time recovery using Snap View snapshots.
Currently my AX4 platform has the following LUNS:
LUN1 - registry 1 LUN2 - registry 2 LUN3 - vote 1 LUN4 - vote 2 LUN5 - vote 3 LUN6 - ASM - DISKGROUP DATA DISK DATA01 (actual db datafiles) LUN7 - ASM - DISKGROUP DATA DISK DATA02 (actual db datafiles)
Using source LUNs in consistent session will take sync snapshots of all the LUNs working against my database. Once something happens with database, the LUNs can be returned to specific point in time when snapshot consistent session was taken and I'm expecting the database will up and continue to work.
Questions:
1. Is my approach correct at all? (The database is running noarchivelog mode, not dealing with hot backups. The recovery point in time implemented purely via EMC snapshot consistent sessions.) 2. The AX4 platform has a limit 8 source LUNs in session. If I understand it correctly, I can't place more than 8 LUNs in session. What will be the workaround if my database will occupy more than 8 LUNs, I'd like to take their consistent snapshot; for example:
LUN1 - registry 1 LUN2 - registry 2 LUN3 - vote 1 LUN4 - vote 2 LUN5 - vote 3 LUN6 - ASM - DISKGROUP DATA DISK DATA01 (actual db datafiles) LUN7 - ASM - DISKGROUP DATA DISK DATA02 (actual db datafiles) LUN8 - ASM - DISKGROUP DATA DISK DATA03 (actual db datafiles) LUN9 - ASM - DISKGROUP DATA DISK DATA04 (actual db datafiles)
I am trying to change the default behavior of Hide/Show Region to show, after some attempts i got it partially working but now clicking the icon to toggle hide/show doesn't work also changed the icons and added type="" but its not working.
Both are having same tables but with diffrent data. I want to write a generic code piece for a table to get that diffrences out, it can be for multiple tables. There will be a DB link between two DB.
3. What the appropriate situation for CAT and TAB?
sys@ORCL> desc cat Name Null? Type ----------------------------------------------------- -------- ------------------------------------ TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11)
sys@ORCL> desc tab Name Null? Type ----------------------------------------------------- -------- ------------------------------------ TNAME NOT NULL VARCHAR2(30) TABTYPE VARCHAR2(7) CLUSTERID NUMBER
sys@ORCL> select * from dict where table_name='CAT';
TABLE_NAME COMMENTS ---------- ----------------------------------- CAT Synonym for USER_CATALOG
sys@ORCL> select * from dict where table_name='USER_CATALOG';
TABLE_NAME COMMENTS -------------------- ----------------------------------------------------------------- USER_CATALOG Tables, Views, Synonyms and Sequences owned by the user
I am planing to write the web-application which use Oracle DB 11g.I would like to understand what are the differences (specially, performance issues, steps of execution, optimizer possibilities) between calling SQL statements and PL/SQL procedures/functions. Which approach is more appropriate, and why?
Examples: a) WebApp->Call("select * from employees where department_id = ?", 10) ;
b) WebApp->Call("? := mypackage.get_emp(?)", refCursor, 10);
create package mypackage is function get_emp(dep_id in number) return sys_refcursor is begin open cur for select * from employees where department_id = dep_id; end; end;========================================== Requirements: High-concurrency, 100+ db sessions, DB will not be used for business-logic.
we have an issue on a server, where if we insert a value containing foreign characters from SQL+ we see it screwed up from SQLdev, whereas if we do the same insert from SQLdev it's then screwed up in SQL+.
All operations are performed on the DB server itself, SQLdev connection type is TNS and the entry being used is the same as the one used by SQL+.We checked v$nls_parameters and the values contained there are the same from both SQL+ and SQLdev.
We have recently upgraded application (from Oracle Applications 11.5.9 to 12.1.3) and database (from 9.2.0.5.0 to 11.2.0.3.0).Since we are confronting to performances issues, i try to analyse some queries which Explains plans seems strange (in my opinion).Studying one of them i discover the next case (which according to my logic, i can't explain): --
Just bind variable --select *from MTL_MATERIAL_TRANSACTIONS mmtwhere 1 = 1and mmt.INVENTORY_ITEM_ID = :p1and mmt.ORGANIZATION_ID = :p2and mmt.TRANSACTION_REFERENCE = :p3--and mmt.SUBINVENTORY_CODE = :p4 PlanSELECT STATEMENT ALL_ROWS Cost: 5 Bytes: 361 Cardinality: 1 2 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_MATERIAL_TRANSACTIONS Cost: 5 Bytes: 361 Cardinality: 1 1 INDEX RANGE SCAN INDEX XXSPE.XXSPE_MTL_MATERIAL_TRANSAC_N99 Cost: 3 Cardinality: 2-- Nvl on bind variable --select *from MTL_MATERIAL_TRANSACTIONS mmtwhere 1 = 1and mmt.INVENTORY_ITEM_ID = :p1and
1) We get a daily Oracle 11 Dump 2) We only want to import the current changes in a MySQL database .
For this we need:
a) Is there a tool, script or best practices to get only the differences (delta) ? b) How to import an Oracle dump or only teh differences into a MySQL database ?
differences of enabling thread as public or private?
lets assume that i have two threads (1, 2) for two instances (1, 2) and each of thread consists of 2 redolog groups - minimum configuration.in case i need add third node i need third thread.
what will be difference if i set this thread as public instead private?
We are trying to use the methods/constructors in the object types and find it more similar to the procedures and functions in the packages. I am wondering how they are different from stored procs and functions and what are the advantages?
How oracle deals with the setting of init.ora parameter compatible.
e.g. we have DB Version=10.2.0.4 compatible=10.2.0
what is the possible leak for us ? Is there any description about the features changed in 10.2 up to 10.2.0.4 in all steps - so that we can check out which functions we do not have.
When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.
If I try the same in SQLplus with to_date it works however fails in PL/SQL.
XML data: <?xml version="1.0"?> <RECORD> <REGTIMESTAMP>20120601130010</REGTIMESTAMP> </RECORD>
PL/SQL Extract:
CURSOR c_xml_record IS SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP, FROM t_xml_data x, [code].......
I have one inline view query which shows exec\ fetch : 2 sec\ 19 sec It gives 500 rows as final out put, when i give rownum<100 it shows exec\ fetch : 1 sec\ 000 sec, and i cannot use this rownum< 100 alternative as this is inline subquery of big query.
I Have three field and first field for START TIME ,Second END TIME & Third DURATION AND Putting START TIME AND END TIME i am getting duration in minutes by using code
I have a table which stores apointment start times and appointment end times. For the sake of this thread I will call them appt.start_time and appt.end_time. I then have a check in time and a check out time for the customer. The only thing is they ONLY way to distinguish between a check in time and a check out time is which one has the earlier time and which one has the later time. Obviously the earlier time will be the check and the later time will be the check out.
This is fine, however sometimes they may forget to check a person in or out and I need to determine whether the time should be insert into the check_in column or the check_out column. To do this I was thinking of comparing the time with the appointment start and end time and if it was closer to the appointment start time put it into the check_in column and if its closer to the appointment end time put it into the check_out column. But I was wondering how I would go about doing this.
The time I will want to compare against the appointment start and end time I will store in a variable called v_time and have this as part of my query, im just unsure of what way to write the query so as to check if the time is closer to the start or end time.
select asl1.agentsessionid, asl1.endtime, asl2.starttime, 127 as agentstatus from ( select asl1.agentsessionid as sessionid1, min(asl2.agentsessionid) as sessionid2 from cti.agentsessionlog asl1
[code]...
As you can see from my where statement I want to compare the endtime with the startime. This query returns zero results. Is there a way to write the where statement different so I can have results?
My time zone has the offset of 2 hrs during summer and 1 hr during winter.If I want Oracle to tell me what was offset for particular day for example I want to know the offset for February 01, 2010 and August 01, 2010, is it possible?
I have a requirement where I have a value stored in varchar2(12), and no matter what the size of data, I should mask all the characters except the last 4
eg: - 23115645
after masking should be - xxxx5645,
create table test1(x varchar2(12)) insert into test1 values('34567745') / insert into test1 values('438765311') / insert into test1 values('1112345') / commit
[Code].....
y should store value of x in such a way that no matter what the size of x is (7 characters or 9), we should see only the last 4, but the remaining should be masked with xxxx
I tried the above update, but using substr, i am able to get this if x has 9 characters
not sure how to achieve this if x has 7, or 4 characters
select in1.empid,in1.atttime,out.atttime from (select empid ,atttime from attend where status=1 and to_char(atttime,'mmyyyy')='022012' order by empid,atttime desc) in1, (select empid,atttime from attend where status =0 and to_char(atttime,'mmyyyy')='022012' order by empid,atttime desc) out where in1.empid=out.empid and in1.empid='02256' order by in1.atttime,out.atttime
But this query do .one value relation with all column.means first february in time with all out time.
i am using oracle 10 forms ! in report i have used form Query "select sum(sale_price) from (table) expense. i attached it to oracle forms but at that time it does nt show any value!
i want that when i run above query every time bas_cod column should start with difference value. now it is starting from 1, i need when i run next time it should start with another number except 1,
i mean whenever i run above query,order of bas_cod must be change from last.
