I have a database job to ANALYZE TABLES,, it takes more than 40 hours. I want to know exactly how much time does it take..
My job has started on 19th Jul at 02:32 hrs.. What is the way to know the exact time when the job ends.
Tablespace usage alerts are checked every ten minutes. You can request a check every minute when you set the threshold, and confirm that this has been set:
orclz> execute DBMS_SERVER_ALERT.SET_THRESHOLD(-
> metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,-
> warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT,-
> warning_value => '50',-
> critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT,-
> critical_value => '75',-
[code]....
orclz>but you will still have to wait up to ten minutes for the alert to be raised. know whether this frequency can be changed? And why this particular alert behaves differently from all the others? This is the bahaviour in all releases since server alerts arrived.
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.
View 3 Replies View RelatedI have scheduled few jobs (scheduled for each day) which has not run today, which was running successfully till yesterday. When I query dba_scheduler_jobs, i could see last_run_date as yesterday's date 6am and next_run_date is still showing today's date and time (it should show tomorrow's date/time).
View 3 Replies View RelatedWhen performing test recoveries of a database we often wish to prevent database jobs from running application processes such as (a) sending emails; or (b) logging events to a central logging database. In order to accomplish this, we set the job_queue_processes to zero before opening the database.
This means that no dbms_jobs run on the open instance, but it does not prevent dbms_scheduler jobs from running.How can we prevent any dbms_scheduler jobs from running?
I have looked at setting the MAX_JOB_SLAVE_PROCESSES attribute using DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE, but this cannot be set to zero.
have a requirement to load .dmp files into existing staging tables and there is package to load the ODS tables from staging.So,I thought of using DBMS_Datapump utility to import the data from .DMP files to the Tables and this need be automated.
--Create Directory
CREATE
OR REPLACE DIRECTORY test_dir AS 'C:Test'
--grant Access to the User
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
--Script to import
DECLARE
l_dp_handle1 NUMBER;
BEGIN
l_dp_handle1 := dbms_datapump.OPEN(operation => 'IMPORT',
[code]...
Errors
ERROR at line 1:
ORA-31634: job already exists
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 938
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4590
ORA-06512: at line 4
I have a problem with DBMS_DATAPUMP.metadata_filter.Let's suppose that I need to export a huge list of tables (a,b,c,d,e,f,g,h,i...). Let's suppose that the list is dynamic do NOT want to use
DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN (''a'', ''b'', ...)',
object_type => NULL);
In my_export_table there is the list:
CREATE TABLE my_export_table
(
EXPORT_OBJECT_NAME VARCHAR2(50 BYTE)
)
Now I'm trying to use this form:
DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN (SELECT a.export_object_name FROM my_export_table a, user_objects b WHERE a.export_object_name = b.object_name AND b.object_type = ''TABLE'')',
object_type => NULL
);
but it results in error.
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-00942: table or view does not exist
[code]...
IS it possible TO schedule same JOB WITH different timings.
I need only one SCHEDULER which should run ON saturday AT 6 pm ,AND monday AT 9 pm.
CREATE TABLE dept1 AS SELECT * FROM dept;
CREATE OR REPLACE
procedure update_record is
BEGIN
insert into dept1 values(10,'xxx','xxx');
END;
[code].....
I have the following query
Select ei.emp_code,
TO_CHAR(ei.intime, 'dd/mm/rr') "IN Date" ,
--TO_CHAR(ei.intime, 'hh24:mi:ss') IN_Time,
MIN(TO_CHAR(ei.intime, 'hh24:mi:ss')) IN_Time,
TO_CHAR(ei.intime, 'FMDAY') IN_DAY
[Code].....
no I want to modify this query, I mean I want to get data between time 09:15 to 09:12 In time.
how to set the timing on for all SELECT statements in a procudeure...and i want that should come as a output for very SELECT statement slong with my original output.
View 6 Replies View RelatedJust want to know about archive log shipping time from production to physical standby. How can i know the timing that ARCHIVE LOG takes to reach from production to standby server.
I think we can get this through ALERT log but m not sure.
I'm facing a problem where I create a form and I want to feed time in "HH:MI" format, Firstly I choose the "Date" datatype, but during the feeding of time i.e "00:30", means if I want to feed only mints, then it doesn't accept, at the other hand if I feed "01:30" means mints with hr then it accepts.
To get rid of this problem I changed datatype "Date" to "Varchar2".
I create 3 columns on my form "TTL_WORKING_TIME" - "TTL_RUN_TIME" = "BRK_DWN_TIME". If I get any value in "BRK_DWN_TIME" column thn it has to be distribute in 3 or 4 reason of B/D, means if I get 00:30 mints brkdwn thn 00:15 mints for "reason1" and 00:15 mints for "reason2".
How I make calculation where I use "Varchar2" datatype.
User complaints fetching data from table A is timing-out ( as per application standards). I decided to collect latest statistics(dbms_stats) on index used by query which is running for more than 12 hours.not.It is partition table and got close to 10 millions records.
1) I checked dba_waiters - no rows
2) I checked v$locked_object/v$locks - no rows
3) I'm unable to run explain-plan for any criteria on that table
4) I'm unable to open table data-tab in Toad/ Sql developer.
5) I'm unable to query a single partition; no error but it keeps on running.
I have a question about system logon / logoff triggers that can provide a tracking mechanism for Oracle user activity you've described:
[URL].......
If this approach is good for high-concurrency OLTP databases?
I am studying about these two technologies and the only difference that I found was that optimization doesn't backup the duplicated archivedlogs.
If both ignore not changed blocks, what is more effective?
The optimization backup also has some tracking file?
I have a form developed using Oracle 10g Forms Builder. It is deployed on server. I need to track who is remotely accessing the desktop and running this form. How can we track this. Are there any webutil commands to trace the application used to access the desktop? What is the work around for this?
View 2 Replies View Relateddoes, block change tracking file is read during incremental level 0 backup?
View 4 Replies View Relatedhow can i track the exception for three select statement in one pl-sql block. here is synario.......
begin
select * from emp where empno=1234; --statement 1
select * from cusotmers where cust_id=125; --statement 2
select * from products where product_id='a-3'; --statement 3
end;
i want to track exception any one for ex no_data_found for all these three different statement.
I know if i put this three statement in three different pl-sql sublock then i can trap it....
how can i trap it in one single block?
i want to enable block change tracking as our incremental level1 backup take a lot of time but i have a worry which i found it on oracle support and tha is as follows Database Instance Hang at Database Checkpoint With Block Change Tracking Enabled. [ID 1326886.1]
View 2 Replies View Relatedi did such commands
1. RMAN -> backup incremental level 0 database;
2. SQLPLUS -> alter database enable block change tracking using file '/home/oracle/block_change_tracking.tr';
3. RMAN -> backup incremental level 1 cumulative database;
4. RMAN -> backup incremental level 1 cumulative database;
5. RMAN -> backup incremental level 1 cumulative database;
and for such order i noticed that block change tracking file wasn't used for any cumulative backup. for each cumulative backup was done full scan over database.
it was used after i did incremental level 0 backup, and again incremental 1 cumulative.
if i want this feature to be usefull i should enable it before my incremental level 0, or i should do incremental level 0 immediately after i enable it?
I have created a table: INDEX_SIZE_TRACKING with the following attributes
Index Name: name of the index. Type: VARCHAR(255). This is the primary key of the table.
Allocated Space: the memory space (in bytes) allocated to the index. Type: NUMBER
Used Space: the memory space used by the index. Type: NUMBER
Last Update: the time when index details are updated to this table. Type: VARCHAR(255)
I want to write a PL/SQL script to query index statistics data and update tracking entries in the INDEX_SIZE_TRACKING table. If there is no existing entry for the index, create a new one; otherwise, update the existing one. have a PL/SQL statement that can do this in oracle XE?
How to check in which port : OC4j instance is running ??
How to run the report using : OC4j instance and deploy into application server?
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
[code]...
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.
I am creating the following dbms_sql procedure...
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.
SET serveroutput on size 1000000
SET wrap on
SET linesize 80
DECLARE
v_text VARCHAR2 (4000);
v_res NUMBER;
v_num NUMBER;
[code]...
Database 1(sm01):
=============
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.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?
Under what userid(privileges) does a scheduled DBMS Jos run, is it under the same user that scheduled the job?
Does oracle internally log in(opens a new session) in order to run a scheduled DBMS Job?
some reference websites where I can get some details about how the scheduled jobs are executed?