--Call SEND_MAIL_X or SEND_MAIL
DECLARE
tablespace_name VARCHAR2(50);
total_space VARCHAR2(50);
used_space VARCHAR2(50);
pct_free VARCHAR2(50);
P_TXT VARCHAR2(20000) := '';
P_SUB VARCHAR2(50) := 'subject';
P_SENDOR VARCHAR2(50) := 'finch.chen@linde-lienhwa.com.tw';
P_RECEIVER VARCHAR2(50) := 'finch.chen@linde-lienhwa.com.tw';
P_SERVER VARCHAR2(50) := '172.30.160.3';
P_PORT NUMBER := 25;
P_NEED_SMTP INT := 0;
P_USER VARCHAR2(50) DEFAULT NULL;
P_PASS VARCHAR2(50) DEFAULT NULL;
P_FILENAME VARCHAR2(50) DEFAULT NULL;
P_ENCODE VARCHAR2(50) DEFAULT 'bit 7';
CURSOR table_status IS
select a.tablespace_name, a.total || 'm' total_space, (a.total - b.free) || 'm' used_space,
to_char((a.total - b.free) / a.total * 100, '99.99') || '%' pct_free
from
(select tablespace_name, sum(bytes) / 1024 / 1024 total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) / 1024 / 1024 free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by pct_free desc;
BEGIN
P_TXT := '
<html>
<head>
</head>
<body>
<Table border="1">
<tr bgcolor="#d3d3d3"><td>Tablespace Name</td><td> Total Space</td><td>Used Space</td><td>Used%</td></tr>
';
FOR a IN table_status LOOP
P_TXT := P_TXT||'<tr><td>'||a.tablespace_name||'</td><td>'|| a.total_space||'</td><td>'|| a.used_space||'</td><td>'|| a.pct_free||'</td></tr> ';
END LOOP;
P_TXT := P_TXT||'</Table></body></html> ';
SEND_MAIL_X(
P_TXT ,
P_SUB ,
P_SENDOR ,
P_RECEIVER ,
P_SERVER ,
P_PORT ,
P_NEED_SMTP ,
P_USER ,
P_PASS ,
P_FILENAME ,
P_ENCODE );
END;
Wednesday, September 19, 2018
Tuesday, September 18, 2018
REP-1219: has no size -- length or width is zero. [Reports Builder]
REP-1219: has no size -- length or width is zero.
The best way to solve this issue is to convert the file.
File>Administration>Convert...(navigation for 6i)
Tools>File Conversions.... (navigation for 9i)
1. Select the Source and Destination(preferable take a new location)
2. Change the Destination Type to .RDF
3. Switch to Option tab
4. Change Destination Unit to "Character".
The best way to solve this issue is to convert the file.
File>Administration>Convert...(navigation for 6i)
Tools>File Conversions.... (navigation for 9i)
1. Select the Source and Destination(preferable take a new location)
2. Change the Destination Type to .RDF
3. Switch to Option tab
4. Change Destination Unit to "Character".
Monday, September 17, 2018
Execute AD_ZD Script after modify or create table in Oracle R12.2.X
AD_ZD_TABLE
1) For new table creations - after creating the table in custom schema execute below script to generate editoning view and synonym for it in APPS schema.
Begin AD_ZD_TABLE.UPGRADE('XXCUST','XX_CUST_TBL1'); End;
2) For table alteratuons - after running the DDL run below script to regenerate the editioning view for syncing any table changes.
Begin AD_ZD_TABLE.PATCH('XXCUST','XX_CUST_TBL1'); End;
3) Table grants must be done through API to avoid invalids.
Begin AD_ZD.GRANT_PRIVS('SELECT','XX_CUST_TBL1','XX_ROLE0'); End;
4) To view objects in all editions..add _ae to the data dictionary views
select * from user_objects_ae;
5) To update seed data tables in Patch edition, execute prepare command.
example:
Begin ad_zd_seed.prepare('WF_MESSAGES'); End;
The prepare will create a edition based storage for the run edition data for which the updates will be made. During cutover, the run time data will be synced using forward/reverse cross edition triggers.
1) For new table creations - after creating the table in custom schema execute below script to generate editoning view and synonym for it in APPS schema.
Begin AD_ZD_TABLE.UPGRADE('XXCUST','XX_CUST_TBL1'); End;
2) For table alteratuons - after running the DDL run below script to regenerate the editioning view for syncing any table changes.
Begin AD_ZD_TABLE.PATCH('XXCUST','XX_CUST_TBL1'); End;
3) Table grants must be done through API to avoid invalids.
Begin AD_ZD.GRANT_PRIVS('SELECT','XX_CUST_TBL1','XX_ROLE0'); End;
4) To view objects in all editions..add _ae to the data dictionary views
select * from user_objects_ae;
5) To update seed data tables in Patch edition, execute prepare command.
example:
Begin ad_zd_seed.prepare('WF_MESSAGES'); End;
The prepare will create a edition based storage for the run edition data for which the updates will be made. During cutover, the run time data will be synced using forward/reverse cross edition triggers.
Subscribe to:
Posts (Atom)