How to view the number of locks defined for a database? I am using Oracle 10g.
How to calculate the number of locks for a given sql select statement?
say for example,
select a.eno,b.ename,c.salary,d.dob,e.address
from a,b,c,d,e
where...
...
..
Assume all of them are row level locking.
I want to calculate the number of locks . How many locks will be held while running the above query?
oracleDatabase version:11gR2
Os:solaris
how to check locks on database objects .it might be any lock? any select sql query to check database object levlel locks?what basis locks will occur?
I used v$locked_object and v$lock query to get the output.. But still I'm an one year exp in ORACLE. How to analyze the output of lock queries. what are the parameters to be analyzed on AWR report.
How to do proper performance checkup in ORACLE database as well analyze it.
recently i saw SYS_CONTEXT function in one package.
query, which can display the All Namespaces defined in database.
I've created a stored procedure which creates itself a view (a MV to be honest); the instructions to create this m.view are dinamically built insinde my procedure, so each time i run it, based on the different input parameters, i've got a different result (my output m.view can have three colums the first time, or ten the next time) how can I read my output view to put the data into file? I've tried with "select * bulk collect into my_array from my_ output_ view"...after declaring "my_array" as a varying array with the max number of colums I could ever have...but nothing: if the array dimension doesn't match the number of columns that i've on my view, i.e. i receive "ora-00947 not enough values" error.
Is there a method to dimension dinamucally the array to store my data? Or should I change the code to fetch some other way the data i need to put to a file?
is there a way to find out how many rows a View has? Something similar to NUM_ROWS with regular tables perhaps? Or do I have to use Count(*)?
View 4 Replies View Relatedin the case below, when i created VIEW to add a new Department for some reason, its length is un-identified but i want the length as describe in table, how i can get ?
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options
devtest@ Test.DB> desc dept
Name Null? Type
----------------------------------------------------------------- -------- ------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
devtest@ Test.DB> l
1 select deptno, dname, loc from dept
2 union all
3 select 99 deptno, 'MY DEPTT' dname, 'MY LOCATION' LOC
4* from dual
devtest@ Test.DB> create view my_dept as
2 select deptno, dname, loc from dept
3 union all
4 select 99 deptno, 'MY DEPTT' dname, 'MY LOCATION' LOC
5 from dual;
[code]....
I am working on an application that pulls data from an Oracle view into Microsoft Excel (Oracle 11g, MS Excel 2003). It is an automated pivot table created through vba. The problem I'm having is that the decimal points from number columns are being truncated - they don't make it to the pivot table.
When I use the pivot table wizard and set the external data source using a SQL string (select * from view), the precision is not lost. When I create the pivot table automatically in vba, the precision is lost. The pivot table settings, regardless of how the data is brought in, shows the format of the number column as general. This tells me that Excel is trying to figure out what the data type is, and can't (not smart enough).
When looking at the description of the view, the data type is NUMBER (no precision). The table that it is pulling from has the precision set (NUMBER(11,3)). I have tried using the following, but it doesn't work:
select to_number(field_name, '99999999.999') field_name from view
I have following tables:
EMPLOYEE (E-Number, Name, Department, age)
ASSIGNMENT (E-Number, P-Number )
PROJECT( P-Number, Project, Manager)
Create a view to show employee names, age and total number of projects they are assigned to
I have the below questions on the locking mechanism in a partition table. Example I execute the below query,
delete from table_name subpartition (subpartition_name);
In that case,
1. If we perform above query, then which level of lock is happened to the table/partition & sub-partitions?
2. If we perform above query on same sub-partition of the same table with different where clause, what will happen?
3. If we perform above query for same table but different sub partition at same time, what will happen?
I have a sql script where triggers, procedures and functions are written. The triggers are causing db outages and causing problems in the application as well.
I am trying my best but with my low experience and expertise, am not able to make good progress.
Scenario: Course enrollments are inserted, deleted and updated in course_main and course_users table. This is done in gui as well as in background snapshot scheduler in a cron process. Course_main table contains all course enrollments and course_users table has crsmain_pk1 as foreign key.
Its quite a big file and am not sure what should i paste here so am uploading the file in txt.
We have encountered dead locks in EBS R12 Database 10.2.0.3. Even after bouncing the appstier and dbtier completely does not release the locks. How to clear the locks?
View 4 Replies View RelatedUnder what scenarios do we lock a table in the exclusive mode.
View 1 Replies View RelatedI have a job which runs the procedure below concurrently in 4 different sessions; It updates two tables.
PROCEDURE UPDATE_TAB_1_AND_TAB_2( traceIdTab IN pl_sql table,
individualIdTab IN pl_sql table,
fileSeqIdTab IN pl_sql table,
[Code].....
Each session is passed a sequence generated file ID; so the records processed in each session are completely different.
However, everytime this job runs, without failure, ONLY TWO sessions process concurrently, while the other two sessions are blocked. Once the first two have finished, then the remaining two sessions start work.
I have being monitoring the sessions on each run daily, and realized that the first two sessions processing are each holding a transaction exclusive lock on objects; hence blocking the other two sessions.
When I tried to find the objects that the two running sessions have a lock on, I cant seem to find anything by searching DBA_OBJECTS.object_id. However, when I look for the objects that are being waited on, its either a partitioned table or a partitioned index on either table_1 or table_2.
At first, I thought it was an ITL lock and after much analysis, I did not find anything which definitely pointed to an ITL lock. However, just to make sure, I increased the INITTRANS value from 2 to 5, but the problem did not go away.
At this point, I have applied all I know to understand the origin of the TX lock and how I can work around this situation, and decided to ask higher powers in Oracle for advise.
At a customers site I see lot of long lasting library cache locks during a complex ETL run. Several Sessions run in parallel and create Database tables with dynamic sql ( CREATE TABLE AS SELECT ....) .
Sometimes these procedures wait for each other with wait event 'Library Cache' . I presume that this is a side effect of the dynamic DDL in the stored procedures. Is that possible even when the Procs create different Tables but reference the same tables in their SELECT clauses ?
I presume that this is plausible but I need some arguments to convince my colleagues.
On DB123, I have 2 KILLED sessions which are not disappearing and are holding locks on some tables in ONE schema.
The sessions belonged to ETL process and I have killed them last Friday after they have been hanging for 3 days. Unfortunately, I've also killed the corresponding Unix session - and now I've read on the Internet that in those cases the KILLED sessions might be hanging forever (=until the DB is shutdown).
how to remove these locks.
After ran db health check, my database report gives the following details
dml_locks OK. dml_locks = 3396, transactions = 849.
how to avoid blocking locks.
View 27 Replies View Relatedwriting a script where there is a blocking lock for more than 30 minutes ?
View 2 Replies View RelatedMy code executes a Select For Update before updating a table. In some cases the network is disconnected and it causes the lock to hang. Then, I must kill the session in order to realese this lock.
I want to do it automatically. I would like to create a job that kills session that has a lock due to Select For Update that is not alive.
I have a forms 11g application that of course has several message and alert windows that pop up when various errors or messages need to be displayed. This all works great on the developer machine, but when I try and run the form on another machine using any browser the message and alert windows appear to be hidden and the application locks up with the rolling bar across the bottom. I would guess it is waiting for a response, but I cannot get the window to appear.
Following is an example of an alert to be displayed:
declare
vAlertButton number;
begin
vAlertButton := show_alert('NO_EMAIL');
end;
Scenario:
Our application is using a two instance, one for the live active data and the other for the reports data. We have a process which moves the data from the live instance to reports instance every night. In a single db environment the process is working without any issues. However when we move to the RAC environment the reports db's (insert) in large table get locked and we are unable to insert data to the reports db.
What we are performing is:
Insert into my_table_rpt select * from may_table_live@db_link_to_livedb;
Issues:
my_table_rpt get locked
We have found the workaround by disable locking in destination and subsequent to the insert enable locking
ALTER TABLE my_table_rpt DISABLE TABLE LOCK;
Insert the data to the reports database table
Then
ALTER TABLE my_table_rpt ENABLE TABLE LOCK
Question:
Why does the large destination table (my_table_rpt) get locked in the RAC environment?
I have been out of work for 2+ years. Am about to start a job next week doing Oracle back end, Forms, and Reports development among other things. I was asked if I could take a look at 3 report requirements and give an estimate on how long it would take to correct errors in these reports. All I have is a user requirement document stating what the report is currently doing and what it should be doing, a partial screen print of an Oracle Form showing correct data, and a sample report page showing incorrect data.
I am finding it rather difficult to give an estimate without seeing tables, relations, code, etc. Is it me or does this seem nearly impossible?I do not have access to their system yet so cannot view the database or run select statements, run the report, etc. All I have are the documents I listed above.
DB_VERSION=10.2.0.4
OS_VERSION=Windows 2008 R2
RAM_SIZE=32 GB
We have 2 production (OLTP) database( DB1 (18GB) and DB2 (200GB) ) on a server out of which DB2 database has almost 20 Materalized Views all (REFRESH FORCE ..ON DEMAND).
I have already informed the client that this is not the OLAP database where you are going to make use of the M Views for reporting purpose. However currently the client does not have any alternative.
Breif details :-
Out of 20 M Views 3 refresh once a week and where as the rest refresh every night daily.
We are currently testing a new website for which we had to re-schedule all M Views from their Old timings to New Timings i.e. Daily Basis.
But post re-scheduling the database performance was badly degarded as the database was too slow also the number of archives increasted to 300GB which was earlier 100 GB.To resolve the issue , we had to go back to the old timings and the database is currently working fine.
Requirement :-Client is looking out for an alternative solution as he needs to once again change the M View refresh time i.e. Daily Refresh.We already have 2 Oracle Instance on the server
Questions :-
1. Can i create another database ( sayTESTDB2 ) in no archivelog mode and create a db link to reduce the load on the server ?
2. If Yes .. Then is there any relavent link /example or Metalink Note ID for the same??
3. What all parameters do i need to consider while creating a DB Link ?
3. Out of 32 GB RAM , I have given 4GB and 8GB sga_max to DB1 and DB2 Respectively . Also the cpu utilization is normally 50-%60%.
Since currently we do not have any Licence for other server .. Can i make use of the same server ( i.e. creating 3 rd Oracle Instance )?
Since the main intention of this activity is reduce the load from the DB2 onto TESTDB when the daily refresh has set once again
Here is the information about database I need to connect from my view, as the underlying table for my view is in this database
Database instance : orcl
Machine name : contentm (not sure, if this needs to be used)
so in my schema, I tried the following
CREATE DATABASE LINK product_lnk2 USING 'orcl'
select * from cont20.V_FB_PRODUCT_NM@product_lnk2
where cont20 is the schema on orcl
But it gives me this error ORA-12154: TNS:could not resolve the connect identifier specified
I'm trying to create a function that simply returns the current database name (e.g: select db_unique_name FROM v$database ) from a function but when I compile it comes up with :
Error(9,5): PL/SQL: SQL Statement ignored
Error(9,44): PL/SQL: ORA-00942: table or view does not exist
I am compiling and running this as the SYSTEM user and I do think that I need to set privledges/roles, etc to allow this (since I have read that using synonyms in functions/procedures requires permissions...but I cannot seem to find anything that tells me exactly what role/priveledge I need to grant/allow to let this happen.
I have two tables:
Table 1:
CONTENT_ID number primary key,
URL VARCHAR2(1024) not null,
TITLE VARCHAR2(200) not null
Table 2:
CONTENT_ID number primary key,
URL VARCHAR2(1024) not null,
TITLE VARCHAR2(200) not null
Both tables are the same layout however I need to merge the data to a DB View on demand. What would the SQL look like to join like this?
How can we find the size of a view or synonym or table in a database. What is the code.
View 4 Replies View RelatedIs there a way to view/obtain the password once a database link has been created. Some sys dba table?
View 7 Replies View Related