Which Query Give Lock Conflicts History In Database
Aug 29, 2013I would like to know 2 days back lock conflicts session information which user held the lock,sql text,when the lock released. any sql query or view in oracle 11g db.
View 1 Replies
ADVERTISEMENT
How To Give Table Name Dynamically In A Select Query
Aug 23, 2012I have 12 different tables
Record_jan
Record_feb
.
.
.
Record_nov
Record_dec
Based on the month i need to do some i/o operations from these table.
For eg: If it is january then Record_jan table should be accesed or for nov Record_nov table should be accesed.
If I try to store the table name in some variable and then give the variable name in place of table name then it gives error.
var:=Record_month;
Select * from var;
this gives error.
SQL & PL/SQL :: Query That Will Give List Of Views Using Three Tables?
Aug 16, 2011I wanted a query that will give me the list of views using three tables
when i fire
select * from dba_views where text like '%SELECT%';
it is giving me error as expected number got long.
SQL & PL/SQL :: Query To Find History In Oracle 11?
Jul 29, 2011find the recent SQL history from Oracle 11. could the following SQL retrieve all SQL history happened in last 5 mins?
SELECT h.user_id,
s2.username,
h.sample_time,
s1.sql_text
FROM v$active_session_history h,
v$sqlarea s1,
[code]....
PL/SQL :: How To Give Space Delimited Input Values In A Query
Dec 21, 2012Example:
select * from emp
where empno in (123 234 345 124)
JDeveloper, Java & XML :: Section Break Conflicts With Dynamic Header In RTF File
Jun 26, 2012I have dynamic header in my rtf file. I have section break on start group of body. but it does not display dynamic header value. If i remove section break then it display dynamic header.
I have to display dynamic header and section break is also required there.
Forms :: FRM-40735 Upload And Download On Database Server Give Error?
Mar 12, 2013 i have succusffly install webutil and also i did configuration also using this url
[URL]...
and i succussfuly upload and download .pdf using my form on my test database server my test database its a single database its version also 10.2.0.4.0 .but when i upload and download my .pdf file on my live database server using this same form its give me error while upload
Quote:FRM-40735: WHEN - CUSTOM -ITEM -EVENT trigger raised unhandled exception ORA-06509
my live database server its a RAC Database server its version 10.2.0.4.0 . i serach on google on this ora-06509 they told me
Cause: This indicates a version clash between some package distributed with an Oracle product and the product executable.
Action: Contact Oracle Support Services.
but my question its my test database server and live database both server version are same its 10.2.0.4.0 just only its differece its test database its a single database server and live its a rac database server.
Server Administration :: Use Of LM ( Lock Mode) In V$lock?
Oct 4, 2012we know we can see lock mode held in session can be analysed using LM column in v$lock.But i confused in seeing LM column it all shows in numbers from 0 to 6.
eg
0,'None(0)',
1,'Null(1)',
2,'Row Share(2)',
3,'Row Exclu(3)',
4,'Share(4)',
5,'Share Row Ex(5)',
6,'Exclusive(6)')
How And When The Database Release A Row Lock
May 30, 2013We are using the following statement to obtain a row lock in a table in the database(ORACLE 10G), SELECT * FROM {TABLE_NAME} WHERE ID = 1 for update and if we succeed grabbing the row lock we will continue to issue a update statement every 30 seconds to preserve the lock as far as possible. here is the update statement to preserve the lock,
UPDATE {TABLE_NAME} SET time = ? WHERE ID = 1.
As you see more longer we keep holding the row lock , more update statements are submitted in the pending transaction. In normal case our application can grab the exclusive row lock and works for a long time,however sometimes a connection reset exception is thrown and our application will close the connection(I assume the pending transaction will be rolled back by the database) and exit the JVM. Since other applications will keep trying to grab the same row lock to become the master role, we expect one of them can succeed but they are all failed because the database has not released the row lock as expected. how and when the row lock can get released in our use case?
Database User Account Lock And Expire
Jun 28, 2012I have altered an user account and set the account status as expired.When I tried to connect with that user, oracle prompted for changing password.
But, after I changed the password, I got an error message as ora-01017 invalid username/password logon denied password unchangedWhat is the cause and solution for it? I am using 11g database and 10g client.
Server Administration :: Many Lock On Oracle Database Using Forms
Mar 30, 2012I have a RAC environment with 3 nodes and application using Oracle Form Reports that access the database. Every day the application cause many locks on database when execute insert, update and delete. I don't understand why so much locks. Are there a way to avoid this? Always I have to eliminate session causing locks.
View 27 Replies View RelatedSecurity :: Lock User Accounts In Oracle Database
Jul 12, 2011I have a doubt in locking user account in oracle database.I was told to drop some unusable users in database which my clients dont require them.I verified the dependent objects of those users but not sure if these users can be dropped.
Instead can i make those user accounts locked so that they cannot connect to database? Would there be any impact on database performance if i lock those user accounts?
Is locking an account and dropping users is similar in anyway?
Server Administration :: Database Server Session History?
Apr 17, 2011Can I extract data from our Oracle server regarding the no. of sessions per day? But the problem is that our Boss wants a previous dates from April 11-17, 2011.
View 4 Replies View RelatedSQL & PL/SQL :: Give New ID To Existing Employee?
Mar 13, 2012 want to create a PL/SQL procedure, update_id(id_emp in number), that gives a new id_emp (id_emp=y) to an existing employee (id_emp=x).So before updating the EMP table, we have to :
1- create a new row on EMP(with id_emp=y) that has the same informations of the employee (id_emp=x),
2- update all tables that contains the id_emp column (update <TAB> set id_emp=y where id_emp=x),
3- delete employee (id_emp=x).
The problem is in step 2 : it creates a lot of locks and makes the DB unusable.To deal with this problem, I thought for many solutions, but the problem is how to implement them correctly and efficiently.
Before executing step 2, we have to ensure (through a marker I guess) that all the tables that have the id_emp column, are managed by our session, and any other acces (through SELECT, UPDATE, DELETE, INSERT statment) from another session will be rejected since we have a marker on that table.
When step 2 ends, we release all the tables from the markers.
My simple question : how to achieve this ?
Forms :: How To Give Hyperlinks
Jan 14, 2012i would like to give a hyper link in forms. how it possible, any way to do it.
View 8 Replies View RelatedPL/SQL :: Can Give Name To Block Of Code In SP
Aug 29, 2013Version- 10G I have a stored procedure which contain code like
if () then---update---else if() theninsert --update--------else if()-----------------------end if;end if;end if;
I have to use this code numbers of times in my procedure , is there any way I can give name this code & use it or call it where I need it in my SP.Don't want to make the above code a separate procedure.
SQL & PL/SQL :: Give Select Grant For Few Tables?
Jul 8, 2010I created a user and granted connect,resource priviliges. I gave access to this user for only 5 tables. when i check it later, other tables are also given access. How can i avoid this and give access to selected tables.
View 9 Replies View RelatedPL/SQL :: How To Give A User Administrative Permissions
Oct 15, 2012Suppose I using "scott" user, I need to give administrative permissions to scott. How can I give??
View 6 Replies View RelatedSQL & PL/SQL :: Give Access Only On 10th Row For Schema2
Mar 26, 2010I have two schemas.In schema1 i hava a table with 20 records,i want to give access only on 10 th row for schema2.
View 6 Replies View RelatedPL/SQL :: How SQL Queries Act In Back End To Give The Result
Jul 22, 2013When we are running a query it is giving us the result based on the conditions .But to know what exactly is happening in the database when we are running a query against it and how finally it returns the result.
View 1 Replies View RelatedHow To Find SQL Text History
May 27, 2011I have SQL ID , sql with bind variables ("SYS_B_005")from AWR report which is taken last week.I need find the full exact sql text with bind variable value (sql ran 1 week before).
View 4 Replies View RelatedSQL & PL/SQL :: History Table Join
Aug 16, 2010two history tables with each record having effective date and end date needs to join (date is in dd/mm/yyyy)
table one
effdate enddate ID Name
01/08/2010 04/08/2010 01 devendra
04/08/2010 06/08/2010 01 deven
table two
effdate enddate ID Family
01/08/2010 02/08/2010 01 X
02/08/2010 03/08/2010 01 Y
03/08/2010 05/08/2010 01 Z
05/08/2010 06/08/2010 01 W
Expected output
effdate enddate ID Name Family
01/08/2010 02/08/2010 01 devendra X
02/08/2010 03/08/2010 01 devendra Y
03/08/2010 04/08/2010 01 devendra Z
04/08/2010 05/08/2010 01 deven Z
05/08/2010 06/08/2010 01 deven W
what can be optimum sql for this?
SQL & PL/SQL :: Invalid Object History?
Jun 10, 2010I can get a list of invalid objects, know how to recompile them but I was wondering if there was a way to query the date/time of when an object went invalid.
I'm working on a 10.2.0.4.0 (Enterprise Edition) database running on SLES10.
History Of Frequency Of Execution Of A Particular Sql
Jun 18, 2012I am having a particular SQL, i want to know the frequency/count of execution of this particular sql in my prod DB. how can i get this.
I get the sql in v$sql but didn't get the same record in dba_hist_sqlstat view.
SQL & PL/SQL :: Useful Analysis From History Tables?
Mar 8, 2010I have 2 tables that I am going to use. These are AUDIT_LOG, and AUDIT_PROPERTIES. AUDIT_LOG tells me that who made the change and at what time. AUDIT_PROPERTIES basically tells me that what the old value was and what the new value is.
These tables operate at the field level. For example, I have a module called BUG. Now a BUG (as a module entity or record) has several fields. There are around 25 fields. But I am interested in only 3 of these fields- these are BG_STATUS (Status), Priority (BG_PRIORITY) and Severity (BG_SEVERITY). This is my requirement:
Between a given period of time (let us say the month of January), give me the count of bugs that, 1) had an "Open" Status, 2) Were of "Medium" Severity, 3) And had a "Low" priority....each of these 3 conditions must be true simultaneously, means, that to be a "qualified" bug, the bug must be Open, Medium severity and low priority at the same given time within the month.
Below are the query and an image of the data looks like.
Server Administration :: Alter SGA But It Give Error?
Dec 9, 2010 I try to alter the SGA it give me error The Steps which I do show sga
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
[code]...
Select From Main If There Is No Record In History
Aug 9, 2010I have 3 main tables as projects, tasks, clients. Then I have a history table for each. I created a trigger on update/delete that takes the old records and inserts into the history.
Now, I need to retrieve the history but I have no clue how to do so. I need to join these 3 history tables with:
1. select from projects_history with projectID = 1
2. if no records, then go back to projects and get projectID = 1
3. select from task_history with taskID = 1
4. if no records, then go back to tasks with taskID = 1
5. select from client_history with clientID = 1
6. if no records, then go back to client with clientID = 1
where client.taskID = task.taskId and task.projectID = project.projectID.
SQL & PL/SQL :: Updating Fields Of History Table
Aug 25, 2011pgit_policy is transaction table having producer code field.
pgith_policy is history table, on that table if any endorsement passed new records created with same polh_sys_id and increment on POLH_END_NO_IDX.
I am trying to update all records of the history table but its updating only higest POLH_END_NO_IDX only. i need to update all producer code.
update pgith_policy a
set a.polh_producer_code= (select b.pol_producer_code
from pgit_policy b
where b.pol_no=a.polh_no
--and b.POL_END_NO_IDX= a.POLH_END_NO_IDX and b.POL_END_SR_NO = a.POLH_END_SR_NO
and b.pol_producer_code is NOT NULL
and b.pol_class_code='10')
where a.polh_class_code='10'
and a.polh_producer_code is null
and a.polh_appr_dt between to_date('01-06-2011', 'dd-mm-yyyy') and to_date('30-06-2011', 'dd-mm-yyyy')
SQL & PL/SQL :: Trigger To Move Records To History?
Mar 23, 2010I have a table CISCOWORKS that contains dumps from Ciscoworks. Now I want to make a history table CISCOWORKS_HISTORY that contains all information I no longer need actively (entries that have the same CISCOWORKS_MAC as newer entries).
The two tables are like this, exactly the same.
DROP TABLE CISCOWORKS IF EXISTS;
CREATE TABLE CISCOWORKS
(
CISCOWORKS_IDNUMBER(9,0),
CISCOWORKS_MACVARCHAR2(20 BYTE),
CISCOWORKS_SWITCHVARCHAR2(10 BYTE),
CISCOWORKS_PORTVARCHAR2(10 BYTE),
[code].....
I guess I will have to check the INSERTED table and check every entry to see if there is a entry in the CISCOWORKS table with the same CISCOWORKS_MAC. If so, insert an entry in CISCOWORKS_HISTORY with the attributes of the CISCOWORKS entry, and then delete the CISCOWORKS entry. how would I check every entry separately?
SQL & PL/SQL :: How To Return Last 2 Rows In History Table
Nov 23, 2011I have a history table which has a new record written to it for every time static data is updated. What I want to do is return any record that has been changed on a daily basis returning both the new record and the previous record so you can see the 2 records one below the other and compare.I have written the below but it returns all records per account:
SELECT H.Timestamp,H.AccountNo FROM History H
where exists (select H2.AccountNo,count(*) from History H2
where H2.AccountNo = H.AccountNo
group by H2.AccountNo having count(*) > 1 )
Where H.Timestamp > '20111101'
order by H.AccountNo,H.Timestamp