One Oracle Job writing

Compiling jobs in Oracle

In Oracle, you can write a Job to regularly execute a stored procedure. The specific method is to use the Submit () process.

Several parameters of the Submit process: job, what, next_date, interval, and no_parse.

The signature of the Submit method is:
PROCEDURE Submit (
Job out binary_ineger,
What in varchar2,
Next_date in date,
Interval in varchar2,
No_parse in booean: = false)

Signature Parameter Parsing:
Job Parameters
Binary_integer returned by the submit () process. Used to uniquely identify a job

What Parameter
PL/SQL code block to be executed

Next_date
Indicates when the job will run

Interval
When the job will be re-executed

No_parse
Indicates whether the job should perform syntax analysis at the time of submission or execution -- true

-- Run the test process once every 1440 minutes.
Begin
SYS. dbms_job.submit (job =>: Job,
What => 'prc _ g_test ',
Next_date => to_date ('2017-11-30 15:00:00 ', 'yyyy-mm-dd hh24: MI: ss '),
Interval => 'sysdate + 1/1440 ');
);
Commit;
End;
/

-- 1. create TABLE
Create table g_test (
Id number (12 ),
C_date DATE
);

-- 2. create sequence
Create sequence g_seq
MINVALUE 1
Max value 9999999999
Start with 1
Increment by 1
CACHE 20;

-- 3. create procedure
Create or replace procedure prc_g_test IS
BEGIN
Insert into g_test VALUES (g_seq.NEXTVAL, SYSDATE );
END prc_g_test;

-- 4. create job
-- This process has five parameters: job, what, next_date, interval, and no_parse.

PROCEDURE submit (job OUT BINARY_INTEGER,
What IN VARCHAR2,
Next_date in date,
Interval in VARCHAR2,
No_parse in boolean: = FALSE
)

DECLARE
Job NUMBER;
BEGIN
Sys. dbms_job.submit (job, 'prc _ g_test; ', SYSDATE, 'sysdate + 100 ');
END;

-- View Job information
SELECT * FROM DBA_JOBS;
SELECT * FROM DBA_JOBS_RUNNING;
SELECT * FROM g_test order by id asc;

-- Run Job
-- Job = 22

BEGIN
DBMS_JOB.run (22 );
END;
/
6. Run the JOB
Note: The Run () process is used to immediately execute a specified job. This process only receives one parameter:
SQL> begin
2 dbms_job.run (: job );
3 end;
4/

----------------------------------------------------------------------------------
In PLSQL, my approach is:
Begin
Dbms_job.execute (3017 );
End;
----------------------------------------------------------------------------------
7. delete a job
SQL> begin
2 dbms_job.remove (: Job); --: job can be replaced by the value of dba_jobs.job, for example, 1198.
3 end;
4/
 
 
 

Oracle job sample executed at every day

DECLARE
X number;
BEGIN
SYS. DBMS_JOB.SUBMIT
(Job => X,
What => 'etl _ RUN_D_Date ;',
Next_date => to_date ('2017-08-26 01:00:00 ', 'yyyy-mm-dd hh24: mi: ss '),
Interval => 'trunc (sysdate) + 1 + 123 ',
No_parse => FALSE
);
SYS. DBMS_OUTPUT.PUT_LINE ('job Number is: '| to_char (x ));
COMMIT;
END;
/The preceding statement explicitly specifies that the job is executed at every day. If you specify to execute interval at every day, you must specify it as 'trunc (sysdate) + 1 + 123 ', if you only specify interval as one day, the daily execution time of a job changes when you manually run the job once with dbms_job.run (job, if you want to execute a job at a fixed time every day, refer to the above example.

Parameter Value of Interval
'Trunc (sysdate + 1) 'At midnight every day )'
Every morning 08:30 'trunc (sysdate + 1) + (8*60 + 30)/(24*60 )'
Next _ day (trunc (sysdate), ''tuesday'') + 12/24 at every Tuesday'
'Trunc (last_day (sysdate) + 1) 'at midnight on the first day of each month )'
'Trunc (add_months (sysdate + 2/24, 3), 'q')-100' at on the last day of each quarter'
'Trunc (least (next_day (sysdate, ''saturday "), next_day (sysdate," Sunday ") + (6 × 60 + 10) /(24 × 60 )'
 
 
Reference Source: http://guyuanli.itpub.net/post/37743/484763
 
 
 
 
 
 
 
 
 
 
 
 
 

Oracle tablespaces are insufficient ORA-01654

17:06:34 | category: Oracle | font size subscription

When inserting data into a data table, the ORA-01654: Index sService appears. ix_msi_wdr_input_1 cannot be expanded by 1024 (in the tablespace userd). It turns out that the data volume is too large, the tablespace does not automatically grow, and the space is insufficient.

Solution: Expand the tablespace:

-- (1) reset the data file size

Alter database datafile 'datafile path name' RESIZE 2000 M;

-- (2) Set automatic growth of data files

Alter database datafile '...
'Autoextend ON | off next 20 m maxsize 300 M;

-- View which tablespaces are automatically increasing

SELECT FILE_NAME, TABLESPACE_NAME, autoextensible from dba_data_files;

 

/* (3) Add a data file

The Data Files Added to the tablespace cannot be directly deleted from the tablespace, unless the entire tablespace is deleted.

Adding data files will help balance I/O

The maximum number of tablespace files is one. The more files, the higher the cost of executing a checkpoint */

Alter tablespace tablespace_name add datafile '... 'size XX;

Alter tablespace tbs2 add datafile '/u01/app/oracle/oradata/orcl/tbs2.dbf' size200m
Autoextend on;

 

View the tablespace utilization:

The script is as follows: I found it online and forgot the link. Sorry.

Select ff. s tablespace_name,

Ff. B Total,

(FF. B-Fr. B) usage,

Fr. B free,

Round (FF. B-Fr. B)/ff. B * 100) | '%' usagep

From (select tablespace_name S, sum (bytes)/1024/1024 B

From dba_data_files

Group by tablespace_name) ff,

(Select tablespace_name S, sum (bytes)/1024/1024 B

From dba_free _