From database server, I need to monitor the details about the sql statements which are being currenlt running in client machines.
I tried with V_$SQLTEXT view where I can only see the SQL statements, hash value,address,SQL_id. but I'm not able to get the user name,name the client machine .
find out these details?.Which Data Dictionary i need to use ?.
Recently we have upgraded from 11.1 to 11.2 . But after upgrade SQL statements that are running fine in 11.1 was running for hours in 11.2. Statistics are collected 100%...
View 2 Replies View RelatedIt started out pretty simple where I had to update about 40 contacts in the database and I would have 40 separate update statements I would run. The task has jumped to about 300 contacts and I don't want to run 300 update statements. I would like to run this all at once. For example:
update contact
set name = 'Name1'
where row_id = 'row_id1'
update contact
set name = 'Name2'
where row_id = 'row_id2'
update contact
set name = 'Name3'
where row_id = 'row_id3'
My DB is oracle 10, and TOAD is version 9.
I am trying to find a way to identify what are the user_jobs currently running. I have found 1 way which requires DBA User ID to execute (e.g. DBA_JOBS and DBA_JOBS_RUNNING) which I don't want.
I believe there is NO equivalent User table for DBA_JOBS_RUNNING table.
I have done my 20 mins search in this forum and unable to find.
find the Execution time frequency of the database jobs?i need the Execution time like this..
Job A is Running on every monday 10:30 AM OR
Job A is Running on every MONTH OR
Job A is Running on every DAY 10:30 AM
How do i find a particular SQL or a set of SQL's which are excuted against a table (user identified table) that is either a very frequently executed query against that table or high impact SQL against that table? I am currently looking through the AWR reports to go through all the queries but i was wondering if there are any dictionary views where we can find this info from?
View 2 Replies View RelatedHow to find currently running scheduled jobs using query !?
View 3 Replies View RelatedHow can we check completion status for running sql query. i.e. how much % completed
SQL> begin
2 delete from gsmcrmdw.wc_loy_txn_f_aa
3 where integration_id in
4 ( select integration_id
5 from support_olap.recover_wc_loy_txn_f_953to955
6 );
7 commit;
8 end;
9 /
Can we execute more than one insert statements at a time (eg 10) in database and givecommit at the end of insert statements or else give a commit one by one after each insert statements ?
View 8 Replies View RelatedI know, importing a *.CSV-table is easy using a few clicks with the GUI, but I want to know, how to import and create new tables, using the existing *. CSV-files, with SQL statements.
how to import a .CSV-file using SQL-statements?
I have a column in a database that contains both numerical and char data. I would like to be able to do two different things (two different queries)
1. divide the numerical data in the column by 10 and leave the char data alone (just return it)
2. detect the numerical data in the column and treat is as a different value so I can run averages & counts on it while disregarding the char data
I'm not at all sure how to do number 2. I thought a CASE statement would work for number 1, but then I realized CASE doesn't like different datatypes:
select
case when '1234' = 'checked' then 'checked'
when '1234' = 'gen.nograde' then 'gen.nograde'
when '1234' = null then null
else '1234'/10 end as "GRADE"
from dual
Error report:
SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s"
I have a read access on all the 200+ tables in a schema, How can i generate 200+ .sql files with insert statements on all the tables of schema.
View 13 Replies View RelatedBeen trying to concatenate the following two select statements (to then pass as a parameter in DML statement) without much luck.
Select lpad(ST_DAY,'2',0) || '/' || lpad(ST_MONTH,'2',0) from RET_FORMATS;
Select to_char(sysdate,'YYYY')"Year" FROM DUAL;
I have a question regarding the SQL statements embedded behind the Self Service Pages in Oracle Applications.
Taking an example of Oracle E-business Suite, Is there a way to check what SQL statement is hard coded or embedded in a particular required Self Service Page?
I'm in a bit of a pickle with some SQL syntax, and while my Oracle-fu is weak, an associate with SQL skills is also stuck. I am developing a database for my department that is backed by Oracle Enterprise 11g and the front end is ChemAxon (basically, chemistry database software). The short of it is that I have one large table with every compound from different groups. Each row has a flag (column PI) on it that indicates which group it belongs to (let us say 'Smith' and 'Jones' for example). The software can apply row-level filtering which will basically only show the rows that a particular username are allowed to see.
I have a FLAG_TABLE table, which contains two columns: USERNAME and FLAG. An example set up is this:
USERNAME FLAG
------------- ----------
smith_minion Smith
jones_minion Jones
jones_minion Smith
The software automatically applies a SQL filter that begins with:
SELECT DISTINCT CHEMAXON.STRUCTURES."ID" FROM CHEMAXON.STRUCTURES WHERE
I can set up filtering to work dandy, such that when smith_minion logs in, he can only see rows with the Smith flag (or jones_minion can see both Jones and Smith) by using the filter:
STRUCTURES.PI IS NULL or STRUCTURES.PI IN(SELECT ALL FLAG from FLAG_TABLE where USERNAME = '__IJC_USERNAME__')
('__IJC_USERNAME__' is how the third party software passes the logged in username into SQL)
But we have a new problem: there also exists a master user (chemaxon) who needs to see every row no matter what the flag. The row filtering is applied no matter who logs on, so we need to set up the SQL filter to basically say "If chemaxon, then select all rows, otherwise, select rows based on the username". This is proving a problem as the select statement must be prefaced with SELECT DISTINCT CHEMAXON.STRUCTURES."ID" FROM CHEMAXON.STRUCTURES WHERE.
I've tried using DECODE in a few capacities, but I am always thwarted. My last attempt was:
STRUCTURES.PI IS NULL or STRUCTURES.PI IN (DECODE('__IJC_USERNAME__','chemaxon',(!='something'),(SELECT ALL FLAG from FLAG_TABLE where USERNAME = __IJC_USERNAME__));
But this throws a ORA-00936 missing expression error, with a * under the != portion (I test it by replacing '__IJC_USERNAME__' with 'CHEMAXON').
Is it possible to have $ substitutions in Select statements ?
For example
Select * from my_table where ID in ${ID_LIST} and DAY >= to_date(${SOME_DATE})
$ID_LIST = (100,200,300)
$SOME_DATE = 10-10-2011 12:12:00
For the ID_LIST is using Prepared statements with ?,?,? the way to go ? Or are there are any Define we can do in SQL plus for this substitions ?
The link from where I can get syntax of all the commands available in oracle?
View 6 Replies View RelatedMy teacher taught the lesson of DML statemnts, he told us how does merge works , but he did not give us any query for that,provide query for Merge and if possible then explain it too , I am using Oracle 10g Sql Plus.
View 3 Replies View RelatedDDL statements automatically end with COMMIT the user transactions in which they appear. Foe example:
-------------------------------------------
create table mytable01 (i integer);
insert into mytable01
select 1 from dual;
create table mytable02 (i integer);
-------------------------------------------
After all three statements are executed, data are committed in mytable01.
In the Oracle DB server SQL guide we read:
"DDL commands, such as TRUNCATE, will fail if there is any DML command active on the table. A transaction will block the DDL command until the DML command is terminated with a COMMIT or a ROLLBACK."
But I executed the following without any problem:
-------------------------------------------
create table mytable (i integer);
insert into mytable
select 1 from dual;
commit;
update mytable
set i = 2;
alter table mytable add (j integer);
-------------------------------------------
So where's the truth? Are DDL statements blocked when they refer to an active object accessed from a DML or not?
Can we have 2 insert statements for 1 for loop? how can we have 2 insert statements into 1 for loop!
FORALL j IN stu.FIRST .. stu.LAST
insert into CHASSISM_test
(make, stu, invoiceno, gross_tot, discount)
values
(make(j), stu(j), INVOICEno(j), GrossTot(j), discount(j));
[Code]...
I am new to oracle programming.
I have a statement below
The statement below correctly filters the records that I require.. I now want to;
Count the number of records that meet the criteria and Delete the records that meet the criteria
select
sl.project_code,
sl.COST_code,
sl.category_code,
sl.supplier_code,
[Code]....
I don't have development env .create and insert statements work below for sample data:
View 8 Replies View RelatedI 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.
connect the following concepts/information I've been collecting. This is not my field but I'm interested in filling some of mine conceptual/technical gaps.
From a JDBC perspective, one of the benefits of Prepared (and so Callable) statements have over the regular ones is that the statement is "compiled"(*) once and then reused (performance gain).
(*) for SQL statements: building of parse tree and exec.plan
In which way can this notion be extrapolated to invocation of Oracle Stored Procedures through CallableStatements? (After clearing my doubts, I may end concluding that the only relevant feature of CallableStatements is their capacity to deal with stored procedure invocations)
According to procedure's precompiled execution plan SQL compilation implies execution plans generation PL/SQL compilation implies P-code generation and, SQL statements (from PLSQL code) are treated no differently by Oracle than SQL from Java or C/C++. These SQLs will be parsed and execution plans for those SQLs created.
...
When the PL code executes the SQL statement, only then does the SQL engine receive the SQL, parse it, and create an execution plan for it.
Therefore, even when the stored procedure can be parsed and cached in SGA (through the OracleConnection.preparedCall("proc") invocation), the SQL statements won't be effectively compiled until they are executed, right? And going deeper, will those SQL statements be cached to be reused in future invocations of the containing stored procedure? Is this a characteristic of the regular stored procedure execution in Oracle? or is it due to the CallableStatement "origin"?
I'm attempting to audit unsuccessful Select statements in order to trap a problem we're experiencing with our application. I have set the AUDIT_TRAIL initialization parameter to DB_EXTENDED, and bounced our database.
I've issued the AUDIT SELECT ANY TABLE WHENEVER NOT SUCCESSFUL command, and when I issue a SELECT statement as an application user, nothing appears in SYS.AUD$ even though the application has issued a select statement which returned no rows.
We are doing select statements. I have 3 tables that I need to get information out of and I believe I need to use a join but everything I put into oracle gives me an error.I'm doing the selects for a pharmacy and have a customer table, a drug table, and a prescriptions table.
I need to write a select statement that shows what customers are taking what drugs and how many mgs they take
customer_id,
customer_first_name,
customer_last_name,
drug_id,
drug_name,
prescription_unit
i think i need that information for the select but I cant seem to write a select statement that runs without errors.
1. I Wnat to analyze the buffer cache hit ratio. This is what i did.
DECLARE
bufcac NUMBER(10, 2);
BEGIN
[Code]....
2. I would like to analyze the PGA and determine what percentage out of the maximum allocated PGA is being used. I tried the code below but can't find the percentage. Sad
SELECT NAME,
To_char(Decode(unit, 'bytes', VALUE / ( 1024 * 1024 ),
VALUE), '999,999,999.9') VALUE,
[Code]....
Apart from the little problem I have with number 2, not figuring out how to get the precentage seeing that all the data is store in the field VALUE.
I would also like to create a view that has both 1 and 2 in it, but whenever I try creating it, I got and error stating and INTO clause is expected.
how can i insert and update to a table in oracle database 10g through a select statement. not using merge.
View 2 Replies View RelatedI have a proc/func which has a string having comma separated numbers :
'1,2,3,4,5'
I have a query where I need to use the string in way like :
SLEECT * FROM ABCD WHERE ABCD.A IN (1,2,3,4,5);
..and whatever is selected has to be returned as a ref cursor or as a table type object.
How can we prepare a statement to do so
One of the procedures that am working on is failing with ORA-0000: normal, successful completion error.
The procedure has got several update and delete statements and have logging enabled after each step. The problem with that again is, each time the log table gets updated thereby losing the history of until what point the procedure ran successfully.I have this issue only in production environment and unable to simulate it in dev environment which limits my options of troubleshooting the procedure code. I was using SQLERRM in the code.
Is there a way I can identify the bad records/ record causing this issue? Am very new to PL/SQL and do not know how to proceed with this.How do you debug this sort of issues??(where one procedure internally invokes another one which again invokes other one etc)