SQL & PL/SQL :: How To Get Time A DML Was Last Ran On Table
Sep 14, 2011
Is is possible to get the time last DML was ran on a table.
My aim is to get the list of tables which were not modified(Select,insert,update,delete) within last one year.
DB version details:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Solaris: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
We are doing a Proof of concept, and we created a test table with record count as 3,00,000(3 lakhs).
An update query takes, 9 minutes against this table.
Actual Scenario: ================
In client environment we have 75 million records. Here we have to run a set of update query against this table.
We need to give a estimated time for the update in our estimation plan. Can the estimated time be arrived based on the 9 minutes time above, or will the time increase if number of records increase.
What is the need of Temp table in Oracle ? what is the advantage of Temp table over normal table ? Is temp table a log operation one. What is the scenario of using temp table in real time ?
I am working on 10.2.0.4 oracle version database of my production,
when executed a simple drop command , the total time it took is 26 secs. on Avg. the table is holding only 20 records. this is happening for the last few weeks , prior to that it took less than 0 secs.
05:22:58 SQL> drop table C$_100GL_INTERFACE ;
Table dropped.
Elapsed: 00:00:26.67
but on successive executions the elapsed time falls to 10-15secs.
And on the same test env, we are achieving the expected results less than 0 secs.
say for example there is a column called 'date_txn' in a table .When i select that particular column it display output as 'June 2013'.But i want output "with date and time".
how to get data from a Table in Date and Time format? Curently I'm using To_TIMESTAMP for a date range.
Here is the code Snippet -
Select * FROM TABLE_Name WHERE COUNTRY= 'IN' AND CALC_DATETIME between TO_TIMESTAMP('2013/06/05', 'YYYY/MM/DD HH:MI:SS') AND TO_DATE ('2013/07/08', 'YYYY/MM/DD HH:MI:SS')
I have a table which is getting populated from a source. I dont have a time-stamp column in the table. Is there anyway to find the time when a record was inserted into the table?
We are firing a normal Drop command on our database and the database version is 10.2.0.4.The database is running on AIX v5.The command is taking more time than usual .
When i am monitoring the session i can see that a call is being made to procedure "aw_drop_proc".Could i ask you if this is something that is taking more time than usual.
We are not having any partitions on the nested tables .We have a pack of tables and we are dropping this pack through a procedure.The pack comprises of nested tables & normal tables.To drop a nested table it is taking around 6 seconds(Table with no rows) and a normal table(With no rows) it is taking 17 milli seconds.We have a partition on Normal table.
The same operation in windows is taking very less time when compared to AIX.
i have emp table of 5 columns empno,ename,dept,sal,edit_timing(edit timing datatype is timestamp).and my form contains empno,ename,dept,sal. is if i insert a record into forms it automatically insert the date and timing into edit_timing columns.
I have a table which contains 8,21,177 amount of data totally.Now I am trying to delete around 4,84,000 of data from this table by using just one filter i.e. my query is something like below
DELETE /*+ parallel(resource,4) */ FROM resource where created_by = 'MIGN'
This is going to delete 4,84,000 rows of data . But my current issue is this is taking lots of time to delete the data . To be precise , its almost taking 25 hours to delete this data..The created_by column is indexed .
Execution Plan ---------------------------------------------------------- Plan hash value: 2389236532
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------
I am trying to break down a row of data that has nine periods that I want to enter into a table one period at a time. I am trying to use "IF EXISTS ( SELECT * FROM TABLENAME WHERE FIELD1 IS NOT NULL) THEN" through all nine fields. It keeps returning lines that have a null column as well as not null columns. if there is a better way of doing this. Here is an example of what I am trying to do:
IF EXISTS (SELECT * FROM TABLE_NAME WHERE LAST_DT9 IS NOT NULL) THEN SELECT NUMBER, 1COL9, 2COL9, 3COL9, FIRST_DT9, LAST_DT9, 4COL9 FROM TABLE_NAME; ELSE IF EXISTS (SELECT * FROM TABLE_NAME WHERE LAST_DT8 IS NOT NULL) THEN
update tab1 set col1 = ( select col2 from tab2 where tab1.id = tab2.id) table 1 has arnd 10,000 rows
table 2 has arnd 1,700,000 rows and has a primary key on column id.This query is taking around 20 secs to execute. I checked the x-plan and most of time taken for table access by index rowid.I checked the stats for the tab2, its just three days old.
need to build an application for registering the teaching hours of academic staff based on faculty's Time TableWhat is the best plugin for doing that?
For example, is this the best from your point of view - Region Plugin - SkillBuilders Calendaror the calendar region of apex can do that I need ability of adding lectures with their detail in flexible
I use the following query to find out the remaining time to complete the table statistics which is running currently.
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,username,context, ROUND(SOFAR/TOTALWORK*100,2) COMPLETE FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1;
SOFAR column shows 9325 and totalwork column shows 12287.How to calculate the columns in terms of hours and minutes ?
And also sometime the query never shows the output of the query for the current running query.
I want to use a function in join clause. so i go for pipelined function(using for loop to get record & 1 more loop to fetch in table type variable). i achieved what i required. but problem is it takes much time to fetch data. is there any other approach which returns table records without pipelined function.
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].......
the control file code in this path (c:externalctrl.ctl)
load data infile 'C:externalmy_data.txt' into table emp2 fields terminated by ',' (empno, ename, hiredate, etime, ejob, deptno)
this is the error :
C:>sqlldr scott/tiger control=C:externalctrl.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 31 09:45:10 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5 C:>
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?
