/*Archive log Delete from Database*/
delete archivelog all completed before 'sysdate-3';
delete archivelog all completed before 'sysdate-14';
/*See what kind of files are available in the Flash Recovery Area*/
set pages 9999 lines 300
col name format a40
select name, to_char(space_limit, '999,999,999,999') as space_limit, to_char(space_limit - space_used + space_reclaimable,'999,999,999,999') as space_available,round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_AVAILABLE PCT_FULL
---------------------------------------- ---------------- ---------------- ----------
/u03/fast_recovery_area 268,435,456,000 116,600,224,256 56.6
-----------------------------------------------------------------------------------------------------------------------------------------
set pagesize 1000 line 200
col "db_recovery_file_dest" for a32;
col size_m for 999,999,999;
col used_m for 999,999,999;
col pct_used for 999;
select name "db_recovery_file_dest",ceil(space_limit/1024/1024) TOTAL_MB, ceil( space_used /1024/1024) USED_MB,
decode( nvl(space_used, 0),0,0,ceil(( space_used /space_limit) * 100)) "PERCENTAGE(%)"
from v$recovery_file_dest
order by 1;
exit;
-----------------------------------------------------------------------------------------------------------------------------------------
/* Check Tablespace size and free space in oracle? */
SELECT df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "% Free"
FROM
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name
) df,
(SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
tablespace_name
FROM dba_segments
GROUP BY tablespace_name
) tu
WHERE df.tablespace_name = tu.tablespace_name;
TEMP TABLE SPACE CHECK
select TABLESPACE_NAME "TEMP TBS NAME",bytes/(1024*1024) "SIZE(MBs)",BYTES_USED/(1024*1024) "BYTES USED(MBs)",BYTES_FREE/(1024*1024) "BYTES FREE(MBs)" from sys. V_$TEMP_SPACE_HEADER,v$tempfile
/* Tablespace file are located */
Select File_Name, Blocks, Tablespace_Name
From Dba_Data_Files;
/*Listener Status*/
login as: sys-admin
sys-admin@172.16.100.12's password:
Activate the web console with: systemctl enable --now cockpit.socket
This system is not registered to Red Hat Insights. See https://cloud.redhat.com/
To register this system, run: insights-client --register
Last login: Sat Apr 30 01:59:37 2022 from 172.16.100.53
[sys-admin@ifsdbdev ~]$ su - root
Password:
[root@ifsdbdev ~]#
[root@ifsdbdev ~]# su - oracle
[oracle@ifsdbdev ~]$
[oracle@ifsdbdev ~]$
[oracle@ifsdbdev ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-MAY-2022 09:54:28
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 12-MAY-2022 09:15:37
Uptime 0 days 0 hr. 38 min. 50 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.3.0/db_1/network/admin/list ener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ifsdbdev/listener/alert/l og.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ifsdbdev)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ifsdbdev)(PORT=5500))(Security=(my_ wallet_directory=/u01/app/oracle/admin/ifs10cdb/xdb_wallet))(Presentation=HTTP)( Session=RAW))
Services Summary...
Service "TEST" has 1 instance(s).
Instance "TEST", status READY, has 1 handler(s) for this service...
Service "TESTXDB" has 1 instance(s).
Instance "TEST", status READY, has 1 handler(s) for this service...
Service "b9b90dd9e147b39ce0530c6410ac804e" has 1 instance(s).
Instance "ifs10cdb", status READY, has 1 handler(s) for this service...
Service "b9b9a79ac0b5d703e0530c6410ac4621" has 1 instance(s).
Instance "ifs10cdb", status READY, has 1 handler(s) for this service...
Service "ifs10cdb" has 1 instance(s).
Instance "ifs10cdb", status READY, has 1 handler(s) for this service...
Service "ifs10cdbXDB" has 1 instance(s).
Instance "ifs10cdb", status READY, has 1 handler(s) for this service...
Service "pdb" has 1 instance(s).
Instance "ifs10cdb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ifsdbdev ~]$
/*Check the alert log if a vital error exists or not (Corruption)
Use the following script to check alert log
*/
set linesize 150
set pagesize 150
SELECT SUBSTR (MESSAGE_TEXT, 1, 300) MESSAGE_TEXT, COUNT (*) cnt
FROM X$DBGALERTEXT
WHERE (MESSAGE_TEXT LIKE '%ORA-%' OR UPPER (MESSAGE_TEXT) LIKE '%ERROR%')
AND CAST (ORIGINATING_TIMESTAMP AS DATE) > SYSDATE - 1
GROUP BY SUBSTR (MESSAGE_TEXT, 1, 300);
Exit
How To Find The Temp Tablespace Usage
Run the below scripts to get the temp tablespace usage.
select a.tablespace_name tablespace,
d.TEMP_TOTAL_MB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
from v$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
from v$tablespace b, v$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_MB;
//
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
//
SELECT *
FROM
(SELECT a.tablespace_name,
SUM(a.bytes/1024/1024) allocated_mb
FROM dba_temp_files a
WHERE a.tablespace_name = 'TEMP'
GROUP BY a.tablespace_name
) x,
(SELECT SUM(b.bytes_used/1024/1024) used_mb,
SUM(b.bytes_free /1024/1024) free_mb
FROM v$temp_space_header b
WHERE b.tablespace_name = 'TEMP'
GROUP BY b.tablespace_name
);
Check Tablespace Free Space in Oracle
SELECT DFQ.TABLESPACE_NAME "Tablespace Name"
,DFQ.TOTALSPACE "Total Size MB"
, (DFQ.TOTALSPACE - DSQ.TOTALUSEDSPACE) "Free Space MB"
,ROUND (100 * ( (DFQ.TOTALSPACE - DSQ.TOTALUSEDSPACE) / DFQ.TOTALSPACE)) || '%' "Free Space %"
FROM (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / 1048576) TOTALSPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) DFQ
,(SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024)) TOTALUSEDSPACE
FROM DBA_SEGMENTS
GROUP BY TABLESPACE_NAME) DSQ
WHERE DFQ.TABLESPACE_NAME = DSQ.TABLESPACE_NAME(+);
Query to get tablespace datafile location in Oracle
SELECT TABLESPACE_NAME
,FILE_ID
,FILE_NAME
,STATUS
FROM DBA_DATA_FILES;
Check Tablespace Growth in Oracle
/*
---------------------------------------------------- Error --------------------------------
unable to extend lob segment SYS.SYS_LOB0000000357C00040$$ by 1024 in tablespace SYSAUX
Failed executing query (ORA-01691: unable to extend lob segment SYS.SYS_LOB0000000357C00040$$ by 1024 in tablespace SYSAUX)
*/
https://ittutorial.org/ora-01691-unable-to-extend-lob-segment/
https://www.oracle-dba-online.com/managing_tablespace_size.htm
file Location --
Select * from dba_data_files ;
You can view the default location for tablespaces with
show parameter DB_CREATE_FILE_DEST;
You can change the parameter with
alter system set DB_CREATE_FILE_DEST='/ORCL/u02/app/oracle/oradata/ORCL';
create tablespace TEST datafile 'test01.dbf' size 10M;
Tablespace created.
select file_name
from dba_data_files
where tablespace_name = 'TEST';
alter tablespace SYSAUX add datafile '/oradata/datafile/SYSAUX02.dbf' size 1G autoextend on next 1024M;
alter database datafile '/u01/oracle/oradata/booktst_users_02.dbf' resize 150M;
alter database datafile '/u02/oradata/PROD/sysaux02.dbf' resize 8G autoextend on next 10M ;
alter tablespace SYSAUX add datafile '/u02/oradata/PROD/sysaux02.dbf' size 8G autoextend on next 10M;
ALTER DATABASE DATAFILE '/u02/oradata/PROD/sysaux02.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
Add Data Dile
alter tablespace IFSAPP_REPORT_DATA add datafile '/u02/oradata/PROD/ifsapp_report_data02.dbf' size 8G autoextend on next 10M;
NEXT 10M
ALTER DATABASE DATAFILE '/u02/oradata/PROD/ifsapp_report_data02.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
Resize
alter database datafile '/u02/oradata/PROD/ifsapp_report_index01.dbf' resize 8G;
/*IFS – CHECKING */
ARCHIVE LOG
TABLESPACE
DISK SPACE
INDEXING
TEMP FILE LOCATION
SELECT * FROM dba_temp_files;
/u02/oradata/PROD/temp01.dbf
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/PROD/temp02.dbf ' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
ALTER TABLESPACE IFSAPP_DATA ADD DATAFILE '/u02/oradata/PROD/ifsapp_data02.dbf' SIZE 31G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
/*27-01-2023 1PM*/
ALTER TABLESPACE IFSAPP_DATA ADD DATAFILE '/u02/oradata/PROD/ifsapp_data03.dbf' SIZE 31G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
alter database datafile '/u02/oradata/PROD/ifsapp_report_index01.dbf' resize 8G;
///
User Create and Table space
CONN / AS SYSDBA
CREATE TABLESPACE REPORTDB_DATA DATAFILE
'/u01/app/oracle/oradata/ORCL/reportdb_data01.dbf' SIZE 31G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER REPORTDB IDENTIFIED BY r
DEFAULT TABLESPACE REPORTDB_DATA
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON REPORTDB_DATA;
GRANT DBA TO REPORTDB;
# Schedule
# Oracle Job Scheduler
# Linux Schedule
# Windows Schedule
# Oracle Job Scheduler
BEGIN
dbms_scheduler.drop_job(job_name => 'IAL_CUSTOMER_BASIC_INFO_SCHEDULER_JOB');
END;
/
-- Procedure IAL_CUSTOMER_BASIC_INFO_PROC
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'IAL_CUSTOMER_BASIC_INFO_SCHEDULER_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'IAL_CUSTOMER_BASIC_INFO_PROC',
start_date => '22-JUN-22 04.50.00 PM',
--repeat_interval => 'freq=monthly;interval=1;bymonthday=1;byhour=01;byminute=01;bysecond=01',
repeat_interval => 'freq=daily;byhour=16;byminute=55',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'Ial Customer Basic Data Info Scheduler Job');
END;
/
EXEC DBMS_SCHEDULER.ENABLE('IAL_CUSTOMER_BASIC_INFO_SCHEDULER_JOB');
BEGIN
dbms_scheduler.drop_job(job_name => 'IAL_C_CO_COLLECTION_DETAIL_SCHEDULER_JOB');
END;
/
-- Procedure IAL_C_CO_COLLECTION_DETAIL_PROC
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'IAL_C_CO_COLLECTION_DETAIL_SCHEDULER_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'IAL_C_CO_COLLECTION_DETAIL_PROC',
start_date => '23-JUN-22 02.01.00 AM',
repeat_interval => 'freq=daily;byhour=02;byminute=01',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'Ial Customer Collection Detail Scheduler Job');
END;
/
EXEC DBMS_SCHEDULER.ENABLE('IAL_C_CO_COLLECTION_DETAIL_SCHEDULER_JOB');
Use PLSQL_BLOCK instead:
begin
dbms_scheduler.create_job (
job_name => 'myjob',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN myproc(''first arg'',''second arg''); END;',
start_date => sysdate,
repeat_interval => 'FREQ=HOURLY',
enabled => true
);
end;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'test_coll_detail_temp_data_jan2022_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN test_coll_detail_temp_data_jan2022_proc(''01/01/2022'',''01/31/2022''); END; ',
start_date => '30-JUN-22 02.30.00 AM',
--repeat_interval => 'freq=monthly;interval=1;bymonthday=1;byhour=01;byminute=01;bysecond=01',
repeat_interval => 'freq=daily;byhour=02;byminute=30',
end_date => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'one time schedule job');
END;
/
//////////////////////////////////////////////////////
select owner as schema_name,
job_name,
job_style,
case when job_type is null
then 'PROGRAM'
else job_type end as job_type,
case when job_type is null
then program_name
else job_action end as job_action,
start_date,
case when repeat_interval is null
then schedule_name
else repeat_interval end as schedule,
last_start_date,
next_run_date,
state
from sys.all_scheduler_jobs
order by owner,
job_name;
# run duration for DBMS_SCHEDULER jobs
col owner for a13
col job_name for a20
col actual_start_date for a37
col status for a14
col run_duration for a18
select owner, job_name, actual_start_date, status, run_duration
from DBA_scheduler_job_run_details
where job_name = 'IAL_C_CO_COLLECTION_DETAIL_SCHEDULER_JOB'
order by actual_start_date desc;
You can see dbms_scheduler errors in the dba_scheduler_job_run_detail view:
COL log_id FORMAT 9999 HEADING 'Log#'
COL log_date FORMAT A32 HEADING 'Log Date'
COL owner FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A20 HEADING 'Job'
COL status FORMAT A10 HEADING 'Status'
COL actual_start_date FORMAT A32 HEADING 'Actual|Start|Date'
COL error# FORMAT 999999 HEADING 'Error|Nbr'
TTITLE 'Scheduled Tasks That Failed:'
select
log_date,
job_name,
status,
req_start_date,
actual_start_date,
run_duration
from
dba_scheduler_job_run_details
where
-- job_name = 'MYJOB'
status <> 'SUCCEEDED'
order by
actual_start_date;
v$session
dba_scheduler_running_chains
dba_scheduler_running_jobs
v$scheduler_running_jobs
You can monitor dba scheduler running jobs as follows.
select * from dba_scheduler_running_jobs;
You can monitor dba scheduler running jobs details as follows.
select * from dba_scheduler_job_run_details;
For oracle table size in MB
select
owner as "Schema"
, segment_name as "Object Name"
, segment_type as "Object Type"
, round(bytes/1024/1024,2) as "Object Size (Mb)"
, tablespace_name as "Tablespace"
from dba_segments
where segment_name=’<table_name>’
and owner='<Table owner>';
For oracle table size in GB
select
owner as "Schema"
, segment_name as "Object Name"
, segment_type as "Object Type"
, round(bytes/1024/1024/1024,2) as "Object Size (Gb)"
, tablespace_name as "Tablespace"
from dba_segments
where segment_name=’<table_name>’
and owner='<Table owner>'
and owner='<Table owner>';
If you don’t have access to dba_segments, then you can use user_segments also
For the size in MB
select segment_name as "Object Name"
, segment_type as "Object Type"
, round(bytes/1024/1024,2) as "Object Size (Mb)"
, tablespace_name as "Tablespace"
from user_segments
where segment_name=’<table_name>’
For the size in GB
select
segment_name as "Object Name"
, segment_type as "Object Type"
, round(bytes/1024/1024/1024,2) as "Object Size (Gb)"
, tablespace_name as "Tablespace"
from user_segments
where segment_name=’<table_name>’;
Top 10 big tables in Particular schema
select * from (select
owner as "Schema"
, segment_name as "Object Name"
, segment_type as "Object Type"
, round(bytes/1024/1024/1024,2) as "Object Size (Gb)"
, tablespace_name as "Tablespace"
from dba_segments
where owner='IFSAPP' and segment_type='TABLE' order by 4 desc) where rownum < 11;
Top 10 big tables in Particular tablespace
IFSAPP_REPORT_DATA
select * from (select
owner as "Schema"
, segment_name as "Object Name"
, segment_type as "Object Type"
, round(bytes/1024/1024/1024,2) as "Object Size (Gb)"
, tablespace_name as "Tablespace"
from dba_segments
where tablespace_name =’<tablespace name>' and segment_name='TABLE' order by 4 desc ) where rownum <11;
Sum of the size of table for a particular schema
IFSAPP
select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='TABLE' group by owner;
ARCHIVELOG Mode
select name,log_mode from v$database;
archive log list
--Script to get Tablespace total, free and used space
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
round(SUM(fs.bytes) / (1024 * 1024)) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) / 1024/1024) "Used space",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
order by "% Used" desc;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'C1JOB_1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN reportdb.IAL_C_LEASING_CUSTOMER_ORDER_IFS_PROC;reportdb.IAL_C_LEASING_DET_UNORD_IFS_PROC;reportdb.IAL_C_CO_COLLECTION_DETAIL_FULL_PROC; END;',
start_date => '10-SEP-22 11.58.00 PM',
repeat_interval => 'FREQ=DAILY;BYHOUR=00;BYMINUTE=01',
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'Present Month Collection from IFS System');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'SALE_JOB01',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'BEGIN REPORTDB.IAL_SALE_CUSTOMER_ORDER_BASIC_PROC; END;',
START_DATE => '06-FEB-23 01.00.00 PM',
REPEAT_INTERVAL => 'FREQ=HOURLY',
END_DATE => NULL,
ENABLED => TRUE,
AUTO_DROP => TRUE,
COMMENTS => 'Sale Data - Cusotmer Order from IFS PROD');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'C1JOB_2',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN reportdb.IAL_C_CO_COLLECTION_DETAIL_FULL_PRE_MONTH_PROC; END;',
start_date => '10-SEP-22 11.58.00 PM',
repeat_interval => 'FREQ=DAILY;BYHOUR=03;BYMINUTE=01',
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'Previous Month Collection from IFS System');
END;
/
=============================================================================
Error
domain index is marked loading/failed/unusable in ifs prod
SELECT * FROM all_indexes
WHERE DOMIDX_OPSTATUS != 'VALID' OR STATUS != 'VALID' OR DOMIDX_STATUS != 'VALID';
CUSTOMER_INFO_TAB
alter index INDEX_NAME rebuild;
alter index CUSTOMER_TIX rebuild;
CREATE INDEX IFSAPP.CUSTOMER_TIX ON IFSAPP.CUSTOMER_INFO_TAB
(TEXT_ID$)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS(' nopopulate datastore Customers section group APPLICATION_SEARCH lexer APPLICATION_SEARCH_LEXER storage APPLICATION_SEARCH_STORAGE')
NOPARALLEL;
INDEXING
alter table IAL_C_COWISE_CUSTOMER_INFO_IFS add row_id number default REPORTDB.SEQ_IAL_C_COWISE_CUSTOMER_INFO_IFS_ROW_ID.nextval
CREATE SEQUENCE REPORTDB.SEQ_IAL_C_COWISE_CUSTOMER_INFO_IFS_ROW_ID
START WITH 1
MAXVALUE 999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER
NOKEEP
GLOBAL;
ALTER TABLE IAL_C_COWISE_CUSTOMER_INFO_IFS
ADD CONSTRAINT IAL_C_COWISE_CUSTOMER_INFO_IFS_ROW_ID_pk
PRIMARY KEY (ROW_ID);
CREATE INDEX REPORTDB.IAL_C_COWISE_CUSTOMER_INFO_IFS_ORDERNO_IDX ON REPORTDB.IAL_C_COWISE_CUSTOMER_INFO_IFS
(ORDER_NO)
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'C1JOB_1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN reportdb.IAL_C_LEASING_CUSTOMER_ORDER_IFS_PROC;reportdb.IAL_C_LEASING_DET_UNORD_IFS_PROC;reportdb.IAL_C_CO_COLLECTION_DETAIL_FULL_PROC;reportdb.IAL_C_CO_COLLECTION_DETAIL_FULL_PRE_MONTH_PROC; END;',
start_date => '10-SEP-22 11.58.00 PM',
repeat_interval => 'FREQ=DAILY;BYHOUR=00;BYMINUTE=01',
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'Two month collection from IFS System');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'C1JOB_2',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN reportdb.IAL_CUSTOMER_BASIC_INFO_PROC;reportdb.IAL_CUSTOMER_BASIC_INFO_PRE_MONTH_PROC; END;',
start_date => '11-SEP-22 11.58.00 PM',
repeat_interval => 'FREQ=DAILY;BYHOUR=00;BYMINUTE=01',
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'Customer Basic Data from IFS System');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'C1JOB_4',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN reportdb.IAL_C_LEASING_CUSTOMER_ORDER_IFS_MAN_PROC;reportdb.IAL_C_LEASING_DET_UNORD_IFS_MAN_PROC;reportdb.IAL_C_CO_COLLECTION_DETAIL_FULL_MAN_PROC; END;',
start_date => '10-SEP-22 11.58.00 PM',
repeat_interval => 'FREQ=DAILY;BYHOUR=17;BYMINUTE=25',
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'January to July');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'C1JOB_5',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN reportdb.IAL_CUSTOMER_BASIC_INFO_MAN_PROC; END;',
start_date => '10-SEP-22 11.58.00 PM',
repeat_interval => 'FREQ=DAILY;BYHOUR=17;BYMINUTE=25',
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'January to July');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'C1JOB_10',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN REPORTDB.IAL_GL_INTREST_VTS_PROC; END;',
start_date => '04-APR-23 01.01.01 AM',
repeat_interval => 'FREQ=DAILY;BYHOUR=01;BYMINUTE=05',
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'INTREST AND VTS FROM GL TWO MONTHS SCHEDULE');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'C1JOB_9',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN REPORTDB.IAL_MONEY_RECEIPT_SMS_PROC; END;',
start_date => '02-APR-23 10.00.00 AM',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5; BYSECOND=0',
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'SMS Every 5 minutes from Production');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'C1JOB_9',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN REPORTDB.IAL_MONEY_RECEIPT_SMS_PROC; END;',
start_date => '02-APR-23 10.00.00 AM',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=10;BYSECOND=0;BYHOUR=9,10,11,12,13,14,15,16,17,18,19,20,21,22,23',
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'SMS Every 10 MS from Production');
END;
/
alter table IAL_MONEY_RECEIPT_HISTORY_TAB add ROW_ID number default reportdb.SEQ_IAL_MONEY_RECEIPT_HISTORY_ROW_ID.nextval;
ALTER TABLE REPORTDB.IAL_MONEY_RECEIPT_HISTORY_BACKUP_TAB ADD CONSTRAINT IAL_MONEY_RECEIPT_HISTORY_BACKUP_ROW_ID_PK PRIMARY KEY (ROW_ID)
ALTER TABLE REPORTDB.IAL_C_CO_COLLECTION_DETAIL_LAST_BACKUP ADD CONSTRAINT IAL_C_CO_COLLECTION_DETAIL_LAST_BACKUP_ROW_ID_PK PRIMARY KEY (ROW_ID)
CREATE INDEX REPORTDB.IAL_C_CO_COLLECTION_DETAIL_LAST_BACKUP_ORDER_NO_IDX ON REPORTDB.IAL_C_CO_COLLECTION_DETAIL_LAST_BACKUP
(ORDER_NO)
ALTER TABLE IAL_C_CO_COLLECTION_DETAIL_LAST_BACKUP ADD ROW_ID NUMBER;
IAL_C_COWISE_CUSTOMER_INFO_LAST_BACKUP
ALTER TABLE REPORTDB.IAL_C_COWISE_CUSTOMER_INFO ADD CONSTRAINT IAL_C_COWISE_CUSTOMER_INFO_ROW_ID_PK PRIMARY KEY (ROW_ID)
CREATE INDEX REPORTDB.IAL_C_COWISE_CUSTOMER_INFO_ORDER_NO_IDX ON REPORTDB.IAL_C_COWISE_CUSTOMER_INFO
(ORDER_NO)
