I need to identify, whether my current session is one of slave sessions scheduled as Oracle scheduler job via DBMS_PARALLEL_EXECUTE or not. I already succeeded using join of dba_scheduler_running_jobs, user_parallel_execute_chunks and v$session, but I feel that this is not optimal approach.
I am calling one procedure through job and after submitting the job i am using commit. problem is when job close the session it commit the code which is inside procedure and as per my understanding in another session also, which i want to avoid .
my procedure is
Create My_proc is Begin Insert into my_table values(1,2,3); End;
And my job proc is
Create my_job_proc is Begin dbms_job.submit(jobno,'my_proc;',sysdate,null,null,false); commit; End;
now i am calling my job proc is
Declare Begin my_job_proc; End;
Now problem is once it finish, it will insert the data into my_table which i don't want until I call Commit.I already try 'Set autocommit off'
We created a job yesterday which will call the below procedure. if we start this job, it initiates 92 sessions parellely. How it is initiating 92 sessions parelley?
procedure prc_HECTOR_CIDB_IN_PURGE IS cursor CUR_PROC is select rowid from CUSTMODEL.HECTOR_CIDB_IN where PROCESS_FLAG in ('Y','F');
TYPE TYP_CUR_DATA IS TABLE OF ROWID INDEX BY BINARY_INTEGER; v_typ_cur_data typ_cur_data; [code]....
During batch process record is entered in detail table as well as summary table.
The process first checks if record exists in summary table for same group_no and if 'yes' then "updates" the record with the newly added amount (sums it) else inserts a new record Whereas in the detail table it inserts the record directly
Now if the batch process runs in parallel, (out of many) two different sessions insert same group_no; This is because while sesond session inserts a record, first session inserting the same record (group_no) has not yet committed ; So second session Not knowing that already there is same Group_no (101) inserted, again inserts another record with same group_no rather than summing it.
Can it be solved without using temp table, select for update?
If we have not set parallel degree for a table then we can ( try to ) force parallel execution on a table using a parallel hint Does this 'parallelism' works on the index search in the query as well?
In which situations non-parallel non-partitioned table but parallel index (degree>2) will facilitate a query?
We have recently upgraded the database from Oracle 9i(9.2.0.8) to Oracle 11g (11.1.0.7).our environment:OS - AIX 5.3, application - COBOL server express 5.1-front end, which means cobol programs having embedded SQL connecting to Oracle database.
When executing programs, the a file with name "dbname_mmon_84444.trc" got generated in the "/trace" path with below message.
Unable to schedule a MMON slave at: Auto Flush Main 1 Slave was not permitted to be scheduled - A slave for this action is already running.
ktte_monitor_ts: unable to schedule MMON Slave, error 100 ktte_monitor_ts: unable to schedule MMON Slave, error 100
Yesterday I found in alert log file bellows warning. and In that time some clients inform us they are cannot log on to the application. after few times they could log on to database automatically.
In parameter file ---------------------- processes = 4000
Content of alert log file.
Mon Dec 6 16:03:00 2010 Thread 1 advanced to log sequence 67690 (LGWR switch) Current log# 3 seq# 67690 mem# 0: /d01/oracle/oradata/stlbas/redo03.log Mon Dec 6 16:20:39 2010 Process J000 died, see its trace file Mon Dec 6 16:20:39 2010 kkjcre1p: unable to spawn jobq slave process
[code]....
content of listener log file like bellows (in that time only)
I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.
After building the sql, here's an example
WITH ph AS (SELECT ph.* FROM po_header ph WHERE 1 = 2), pf AS (SELECT DISTINCT pf.order_id, pf.fund FROM po_fau pf, ph WHERE 1 = 1 AND ph.order_id = pf.order_id
[code]....
Where table records for
po_header = ~567746 po_fau = ~2153570
and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like
nDDL_Cursor := dbms_sql.open_cursor; dbms_sql.parse(nDDL_Cursor, sSQLStr, 2); FOR x IN 1 .. nCols LOOP sCols(x) := ''; dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100); END LOOP; nError := dbms_sql.execute(nDDL_cursor);
why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining
ph.order_id = pf.order_id
return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.
We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?
How to pass parameter value to a procedure in schedule jobs ?
--procedure : create or replace procedure updating_temptable(empcode in varchar2) is begin update ts_employee_master mas set mas.last_accessed = sysdate where mas.employee_code = empcode; end;
--scheduler
begin sys.dbms_job.submit(job => :job, what => 'updating_temptable;', next_date => to_date('24-04-2012 00:30:00', 'dd-mm-yyyy hh24:mi:ss'), interval => 'TRUNC(SYSDATE)+1+30/1440'); commit; end; /
How to pass employee_code in the procedure[updating_temptable ] in dbms job scheduler ?
I am begginer programing oracle and I have a issue to resolve but I can't resolve it.I have a procedure that upload a image from a directory. But when I pass the path, the function bfilename put a slash "/" in the path. I don't know why.Here my code.
CREATE OR REPLACE PROCEDURE p_grava_assinatura_gestor AS --------------------------------------------------------- -- Crio o Cursor dos Nomes dos Arquivos JPG. -- --------------------------------------------------------- CURSOR cursor_nome_arquivo IS SELECT p.id, (p.empresa || '_' || p.chapa || '.JPG') AS noarq
I am trying to redefine a table as to get clear of the thousands of chained and migrated rows. The darn table contains a lob column.I am trying to complete the process with the dbms_redefinition package.
I've createad the temporary table with all option to nologging. I've started the redefinition at midnight and it not yet completed and I can't find why it is taking so long. I've done the process with a table, similar in size in a Dev environement and it took at most two hours. In production, the process has been going on for 10h30 now.
I can see my tablespace size increase from time to time and when I querry the dba_segments to find out the size of both tables my temporary table is now 4 times the size of the original table.
CREATE or replace PROCEDURE table_demo (tabname IN varchar2) IS cursor_name INTEGER; rows_processed INTEGER;
[code]...
There are no compilation errors.I call this code from the following anonymous block...
DECLARE X CHAR:='T'; BEGIN TABLE_DEMO(X); END;
This also compiles successfully and without any errors. It runs properly as wellHowever when I run 'select * from T'. Then system throws up the error of table or view does not exist.
there are 4 jobs scheduled in oracle dbms_job. 3 jobs will run everyday at 4.00AM. 1 job will run at every hour.Daily jobs are running fine. But hourly job is not executing automatically. If forced (exec dbms_job.run(<enter here job number>), this execute fine.
job_queue_processes=5 total jobs in schema=2503 total jobs in db = 2614
Even there are many jobs scheduled, next_date for 2234 jobs are lesser than the sysdate. Again in 269(2503-2234) jobs, 2265 are having NULL in the interval column.
Database 2(sm02): ============= oracle, 9.2.0.6
there are 4 jobs scheduled in oracle dbms_job. 3 jobs will run everyday at 4.00AM. 1 job will run at every hour.All the jobs are not running automatically. If forced (exec dbms_job.run(<enter here job number>), these execute fine.
job_queue_processes=5 total jobs in schema=7 total jobs in db = 7
I planning to follow the below steps to avoid the above issue.
1.) Restart the job queue process by executing alter system set job_queue_processes=0
2.) Increase the value for the job_queue_processes.
3.) Restart the database
But I got stuck in the 2nd step. what value I need to put for this job_queue_processes parameter?
I'm currently busy with database consolidation, so I'm searching for a solution to generate some useful DDL to prepare the new target database before importing the application's data. This should include TABLESPACE DDL and all additional users with their grants.
So first I thought of developing a simple script, which will create the CREATE TABLESPACE DDL but with transformed datafile paths.But my throws some errors and I don't understand why:
ORA-31604: invalid NAME parameter "STORAGE" for object type TABLESPACE in function SET_FILTER declare l_hObject NUMBER; l_Objddl CLOB;
I saw bunch of other posts but I could find the post that exactly explaining about where the value returned as systimestamp / sysdate comes from or impacted Here is my situation I have an access to this db (let me call db A) and when I access it, I get following result. I don’t have full access to this db so I cannot experiment a lot here.
I’m in PST timezone.I have my db which I have full access as well as its host.I can make result like db A on my db if I started up db and its listener while TZ environment variable equal to UTC.Now I saw in other post that someone was trying to retrieve systimestamp value in a job executed via dbms_scheduler.run_job.
So I did that in two ways. 1 with use_current_session = true and 2 is false for the same.On my db, results are the same (both returns time in UTC) but on db A, I got UTC time when use_current_session = true and PST when use_current_session = false.
So questions are: What could be the difference in setup between my db and db A? Is there a query, logfile, or anything I should check to find out what can be the difference?
I tried to find the cause with my db and I could see the same result as db A which is to see UTC time if use_current_session = true and PST time if use_current_session = false by bringing up the db listener after I set TZ environment variable equal to PST8PDT. However this causes systimestamp from sqlplus session become also a PST time.
The reason I’m playing around with the setup and checking systimestamp value is because we are facing the situation where everywhere except pl/sql job submitted by enterprise scheduler service is pointing wrong timezone (PST instead of UTC)
I've written a chain with four programs. The third program is depend on the output of the second. However the third program finishes before the second. How do I wait for the second to finish.
I have successfully installed Oracle DBMS 11g R2 on Windows 7 64-bit Ultimate. I can only access the EM using only one user which SYSTEM. Although, I created different users which are unlocked but no success in accessing the EM even with the user name 'SYS'.
Oracle 10g has the feature of automatic stats gathering in this case is it necessary to run DBMS_STATS on tables manually. Does the stats gathered become stale when the auto stat runs ?
I've just installed an Oracle 11.2.0.1.0 64 bit server on my windows 7 machine in order to play around while using attempting to run
exec dbms_stats.gather_table_stats('eii','v2x4e')
I get the following: ORA-20000: Unable to analyze TABLE "EII"."V2X4E", insufficient privileges or does not exist ORA-06512: at "SYS.DBMS_STATS", line 20327 ORA-06512: at "SYS.DBMS_STATS", line 20360 ORA-06512: at line 1
My initial google searches indicate that I need the select any table and analyze any privileges. I don't think that can be right/appropriate - but I've granted them anyway to no avail.
Select * from user_tables
returns tables in the System and sysaux tablespaces, but not my own schema/tablespace?
How to create DBMS_SCHEDULER job for particular instance,we have 3 instance we want to schedule few jobs in Instance 2,how to schedule in Particular instance.
I studied the documentation and many websites about DBMS_JOB but I am still confused.How do I schedule a procedure to run i.e. every sunday 10:00 AM ?This is what I tried:
DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X