Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
Let us see how to create and configure ACLs in Oracle database.
ORA-24247: network access denied by access control list (ACL)
When I was trying to send mail from my Oracle Database and I got below mentioned error :-
ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at “CINP01314”, line 255 ORA-06512: at line 21
We need to create and configure ACL(access control list). In order to use PL/SQL network utility packages like UTL_SMTP, UTL_MAIL, we have to configure an ACL file that provides fine grained access to external network services.
Below Steps need to follow configure :-
1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.
###### only for UTL_MAIL package this parameter needs to be set#######
SQL> alter system set smtp_out_server= '10.10.10.10' ;
system altered
2. Create an access control list file :-
begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'utl_smtp.xml',
description => 'Enables mail to be sent',
principal => 'INDIA', -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/
PL/SQL procedure successfully completed.
5. Check that for INDIA user permission has been set properly :-
SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM
user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILEGE STATUS
------------------- ----------------- --------------- ---------- ------------
10.0.06.01 25 25 connect GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED
–Status column must have value granted.
SQL> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege
('utl_smtp.xml','INDIA','connect'),1,'GRANTED',0,'DENIED', NULL) privilege FROM dual;
PRIVILEGE
---------------------
GRANTED
SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;.
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
---------------------- ----------- -------------- ------------ ----------- --------
/sys/acls/utl_smtp.xml INDIA connect true
Once you got output like above queries. You can test you procedure to send mail, it will work.
As we have create ACL for utl_smtp.xml we can create for utl_mail.xml and utl_tcp.xml after creation of both these ACL’s output of above query will be like below :-
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
--------------------- ----------- ----------- -------- -------- -------
/sys/acls/utl_mail.xml INDIA connect true
/sys/acls/utl_smtp.xml INDIA connect true
/sys/acls/utl_tcp.xml INDIA connect true
TO enable this ACL permission for a different USER other than above user(INDIA) :-
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
Let us see how to create and configure ACLs in Oracle database.
ORA-24247: network access denied by access control list (ACL)
When I was trying to send mail from my Oracle Database and I got below mentioned error :-
ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at “CINP01314”, line 255 ORA-06512: at line 21
We need to create and configure ACL(access control list). In order to use PL/SQL network utility packages like UTL_SMTP, UTL_MAIL, we have to configure an ACL file that provides fine grained access to external network services.
Below Steps need to follow configure :-
1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.
###### only for UTL_MAIL package this parameter needs to be set#######
SQL> alter system set smtp_out_server= '10.10.10.10' ;
system altered
2. Create an access control list file :-
begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'utl_smtp.xml',
description => 'Enables mail to be sent',
principal => 'INDIA', -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/
PL/SQL procedure successfully completed.
5. Check that for INDIA user permission has been set properly :-
SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM
user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILEGE STATUS
------------------- ----------------- --------------- ---------- ------------
10.0.06.01 25 25 connect GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED
–Status column must have value granted.
SQL> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege
('utl_smtp.xml','INDIA','connect'),1,'GRANTED',0,'DENIED', NULL) privilege FROM dual;
PRIVILEGE
---------------------
GRANTED
SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;.
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
---------------------- ----------- -------------- ------------ ----------- --------
/sys/acls/utl_smtp.xml INDIA connect true
Once you got output like above queries. You can test you procedure to send mail, it will work.
As we have create ACL for utl_smtp.xml we can create for utl_mail.xml and utl_tcp.xml after creation of both these ACL’s output of above query will be like below :-
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
--------------------- ----------- ----------- -------- -------- -------
/sys/acls/utl_mail.xml INDIA connect true
/sys/acls/utl_smtp.xml INDIA connect true
/sys/acls/utl_tcp.xml INDIA connect true
TO enable this ACL permission for a different USER other than above user(INDIA) :-
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
UTL_SMTP is a Oracle PL/SQL package which is used to send e-mails from Oracle Database over SMTP(simple mail transfer protocol).
We can send two types of mails using UTL_SMTP:-
Mails without attachment.
Mails with attachment.
Mails without attachment :-
SQL> DECLARE c UTL_SMTP.CONNECTION;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;
BEGIN
c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
UTL_SMTP.HELO(c, '10.10.1.10');
UTL_SMTP.MAIL(c, '[email protected]');
UTL_SMTP.RCPT(c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL;
-- When the SMTP server is down or unavailable, we don't have-- a connection to the server. The QUIT call will raise an-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/
DECLARE
*
ERROR at line 1: ORA-20000: Failed to send mail: Error code -24247: ORA-24247: network access denied by access control list (ACL) ORA-06512: at line 25
If you get above mentioned error then its because your user is not added in Oracle Access control list. In Oracle Database we have to configure ACL in order to use PL/SQL packages.
Once we configure ACL then we are able to successfully send mails using above mentioned code.
Please follow below steps :-
SQL> DECLARE
c UTL_SMTP.CONNECTION;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;
BEGIN
c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
UTL_SMTP.HELO(c, '10.10.1.10');
UTL_SMTP.MAIL(c, '[email protected]');
UTL_SMTP.RCPT(c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL;
-- When the SMTP server is down or unavailable, we don't have-- a connection to the server. The QUIT call will raise an-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/
PL/SQL procedure successfully completed.
Mail has been send now.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
UTL_SMTP is a Oracle PL/SQL package which is used to send e-mails from Oracle Database over SMTP(simple mail transfer protocol).
We can send two types of mails using UTL_SMTP:-
Mails without attachment.
Mails with attachment.
Mails without attachment :-
SQL> DECLARE c UTL_SMTP.CONNECTION;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;
BEGIN
c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
UTL_SMTP.HELO(c, '10.10.1.10');
UTL_SMTP.MAIL(c, '[email protected]');
UTL_SMTP.RCPT(c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL;
-- When the SMTP server is down or unavailable, we don't have-- a connection to the server. The QUIT call will raise an-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/
DECLARE
*
ERROR at line 1: ORA-20000: Failed to send mail: Error code -24247: ORA-24247: network access denied by access control list (ACL) ORA-06512: at line 25
If you get above mentioned error then its because your user is not added in Oracle Access control list. In Oracle Database we have to configure ACL in order to use PL/SQL packages.
Once we configure ACL then we are able to successfully send mails using above mentioned code.
Please follow below steps :-
SQL> DECLARE
c UTL_SMTP.CONNECTION;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;
BEGIN
c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
UTL_SMTP.HELO(c, '10.10.1.10');
UTL_SMTP.MAIL(c, '[email protected]');
UTL_SMTP.RCPT(c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL;
-- When the SMTP server is down or unavailable, we don't have-- a connection to the server. The QUIT call will raise an-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/
PL/SQL procedure successfully completed.
Mail has been send now.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
UTL_SMTP is a Oracle PL/SQL package which is used to send e-mails from Oracle Database over SMTP(simple mail transfer protocol).
We can send two types of mails using UTL_SMTP:-
Mails without attachment.
Mails with attachment.
Sending Mails with attachment :-
1. First check UTL_FILE_DIR parameter in Oracle Database :-
SQL> show parameter utl_file
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
utl_file_dir string /america/report
/america/report has to be physically present in Files and attachment need to be mailed has to be present in this directory.
2. Create a package to send mail with attachment :-
CREATE OR REPLACE PACKAGE AMERICA_ATTACH IS
smtp_port NUMBER := 25;
boundary CONSTANT VARCHAR2(256) := 'CES.Boundary.DACA587499938898';
crlf VARCHAR2(2):= CHR(13) || CHR(10);
my_code NUMBER;
my_errm VARCHAR2(32767);
PROCEDURE AMERICA_ATTACH_EMAIL(smtp VARCHAR2,
from_name VARCHAR2,to_names VARCHAR2,subject VARCHAR2 DEFAULT '',
message VARCHAR2 DEFAULT NULL,cc_names VARCHAR2 DEFAULT NULL,
bcc_names VARCHAR2 DEFAULT NULL,filename VARCHAR2 DEFAULT NULL,
filetype VARCHAR2 DEFAULT 'text/plain');
FUNCTION AMERICA_ATTACH_ADDRESS(smtp_host IN VARCHAR2,addr_list IN OUT VARCHAR2) RETURN VARCHAR2 ;
PROCEDURE AMERICA_ATTACH_SPLITPATH(file_path IN VARCHAR2, directory_path OUT VARCHAR2,file_name OUT VARCHAR2);
PROCEDURE AMERICA_ATTACH_APPEND(directory_path IN VARCHAR2, file_name IN VARCHAR2,file_type IN VARCHAR2,
conn IN OUT UTL_SMTP.CONNECTION);
END AMERICA_ATTACH;
/
CREATE OR REPLACE PACKAGE BODY AMERICA_ATTACH AS
FUNCTION AMERICA_ATTACH_ADDRESS (smtp_host IN VARCHAR2, addr_list IN OUT VARCHAR2)
RETURN VARCHAR2
IS
addr VARCHAR2 (256);
i PLS_INTEGER;
FUNCTION lookup_unquoted_char (str IN VARCHAR2, chrs IN VARCHAR2)
RETURN PLS_INTEGER
IS
c VARCHAR2 (5);
i PLS_INTEGER;
len PLS_INTEGER;
inside_quote BOOLEAN;
BEGIN
inside_quote := FALSE;
i := 1;
len := LENGTH (str);
WHILE (i <= len)
LOOP
c := SUBSTR (str, i, 1);
IF (inside_quote)
THEN
IF (c = '"')
THEN
inside_quote := FALSE;
ELSIF (c = '\')
THEN
i := i + 1; --
END IF;
GOTO next_char;
END IF;
IF (c = '"')
THEN
inside_quote := TRUE;
GOTO next_char;
END IF;
IF (INSTR (chrs, c) >= 1)
THEN
RETURN i;
END IF;
<<next_char>>
i := i + 1;
END LOOP;
RETURN 0;
END;
BEGIN
addr_list := LTRIM (addr_list);
i := lookup_unquoted_char (addr_list, ',;');
IF (i >= 1)
THEN
addr := SUBSTR (addr_list, 1, i - 1);
addr_list := SUBSTR (addr_list, i + 1);
ELSE
addr := addr_list;
addr_list := '';
END IF;
i := lookup_unquoted_char (addr, '<');
IF (i >= 1)
THEN
addr := SUBSTR (addr, i + 1);
i := INSTR (addr, '>');
IF (i >= 1)
THEN
addr := SUBSTR (addr, 1, i - 1);
END IF;
END IF;
i := lookup_unquoted_char (addr, '@');
IF (i = 0 AND smtp_host != 'localhost')
THEN
i := INSTR (smtp_host, '.', -1, 2);
addr := addr || '@' || SUBSTR (smtp_host, i + 1);
END IF;
addr := '<' || addr || '>';
RETURN addr;
END;
PROCEDURE AMERICA_ATTACH_SPLITPATH (
file_path IN VARCHAR2,
directory_path OUT VARCHAR2,
file_name OUT VARCHAR2
)
IS
pos NUMBER;
BEGIN
pos := INSTR (file_path, '/', -1);
IF pos = 0
THEN
pos := INSTR (file_path, '\', -1);
END IF;
IF pos = 0
THEN
directory_path := NULL;
ELSE
directory_path := SUBSTR (file_path, 1, pos - 1);
END IF;
file_name := SUBSTR (file_path, pos + 1);
END;
PROCEDURE AMERICA_ATTACH_APPEND (
directory_path IN VARCHAR2,
file_name IN VARCHAR2,
file_type IN VARCHAR2,
conn IN OUT UTL_SMTP.connection
)
IS
file_handle UTL_FILE.file_type;
bfile_handle BFILE;
bfile_len NUMBER;
pos NUMBER;
read_bytes NUMBER;
line VARCHAR2 (1000);
DATA RAW (200);
my_code NUMBER;
my_errm VARCHAR2 (32767);
directory_name VARCHAR2 (30);
BEGIN
BEGIN
BEGIN
line := directory_path;
SELECT dd.directory_name
INTO directory_name
FROM SYS.all_directories dd
WHERE dd.directory_path = line AND ROWNUM =1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (line || 'does not have valid directorty');
IF SUBSTR (file_type, 1, 4) != 'text'
THEN
bfile_handle := BFILENAME (directory_name, file_name);
bfile_len := DBMS_LOB.getlength (bfile_handle);
pos := 1;
DBMS_LOB.OPEN (bfile_handle, DBMS_LOB.lob_readonly);
ELSE
file_handle := UTL_FILE.fopen (directory_name, file_name, 'r');
END IF;
LOOP
IF SUBSTR (file_type, 1, 4) != 'text'
THEN
IF pos + 57 - 1 > bfile_len
THEN
read_bytes := bfile_len - pos + 1;
ELSE
read_bytes := 57;
END IF;
DBMS_LOB.READ (bfile_handle, read_bytes, pos, DATA);
UTL_SMTP.write_raw_data (conn, UTL_ENCODE.base64_encode (DATA));
pos := pos + 57;
IF pos > bfile_len
THEN
EXIT;
END IF;
ELSE
UTL_FILE.get_line (file_handle, line);
UTL_SMTP.write_data (conn, line || crlf);
END IF;
END LOOP;
-- Output any errors, except at end when no more data is found
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
my_code := SQLCODE;
my_errm := SQLERRM;
DBMS_OUTPUT.put_line ('Error code ' || my_code || ': ' || my_errm);
END;
-- Close the file (binary or text)
IF SUBSTR (file_type, 1, 4) != 'text'
THEN
DBMS_LOB.CLOSE (bfile_handle);
ELSE
UTL_FILE.fclose (file_handle);
END IF;
END;
PROCEDURE AMERICA_ATTACH_EMAIL (
smtp VARCHAR2,
from_name VARCHAR2,
to_names VARCHAR2,
subject VARCHAR2 DEFAULT '',
MESSAGE VARCHAR2 DEFAULT NULL,
cc_names VARCHAR2 DEFAULT NULL,
bcc_names VARCHAR2 DEFAULT NULL,
filename VARCHAR2 DEFAULT NULL,
filetype VARCHAR2 DEFAULT 'text/plain'
)
IS
smtp_host VARCHAR2 (256) := smtp;
recipients VARCHAR2 (32767);
directory_path VARCHAR2 (256);
file_name VARCHAR2 (256);
mesg VARCHAR2 (32767);
conn UTL_SMTP.connection;
i BINARY_INTEGER;
BEGIN
conn := UTL_SMTP.open_connection (smtp_host, smtp_port);
UTL_SMTP.helo (conn, smtp_host);
recipients := from_name;
UTL_SMTP.mail (conn, AMERICA_ATTACH_ADDRESS (smtp_host, recipients));
recipients := to_names;
WHILE recipients IS NOT NULL
LOOP
UTL_SMTP.rcpt (conn, AMERICA_ATTACH_ADDRESS (smtp_host, recipients));
END LOOP;
recipients := cc_names;
WHILE recipients IS NOT NULL
LOOP
UTL_SMTP.rcpt (conn, AMERICA_ATTACH_ADDRESS (smtp_host, recipients));
END LOOP;
recipients := bcc_names;
WHILE recipients IS NOT NULL
LOOP
UTL_SMTP.rcpt (conn, AMERICA_ATTACH_ADDRESS (smtp_host, recipients));
END LOOP;
UTL_SMTP.open_data (conn);
mesg :=
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
|| crlf
|| 'From: '
|| from_name
|| crlf
|| 'Subject: '
|| subject
|| crlf
|| 'To: '
|| to_names
|| crlf;
IF cc_names IS NOT NULL
THEN
mesg := mesg || 'Cc: ' || cc_names || crlf;
END IF;
IF bcc_names IS NOT NULL
THEN
mesg := mesg || 'Bcc: ' || bcc_names || crlf;
END IF;
mesg :=
mesg
|| 'Mime-Version: 1.0'
|| crlf
|| 'Content-Type: multipart/mixed; boundary="'
|| boundary
|| '"'
|| crlf
|| crlf
|| 'This is a Mime message.'
|| crlf
|| crlf;
UTL_SMTP.write_data (conn, mesg);
IF MESSAGE IS NOT NULL
THEN
mesg :=
'--'
|| boundary
|| crlf
|| 'Content-Type: text/plain'
|| crlf
|| 'Content-Transfer-Encoding: 7bit'
|| crlf
|| crlf;
UTL_SMTP.write_data (conn, mesg);
UTL_SMTP.write_data (conn, MESSAGE || crlf);
END IF;
IF filename IS NOT NULL
THEN
AMERICA_ATTACH_SPLITPATH (filename, directory_path, file_name);
mesg := crlf || '--' || boundary || crlf;
IF SUBSTR (filetype, 1, 4) != 'text'
THEN
mesg :=
mesg
|| 'Content-Type: '
|| filetype
|| '; name="'
|| file_name
|| '"'
|| crlf
|| 'Content-Disposition: attachment; filename="'
|| file_name
|| '"'
|| crlf
|| 'Content-Transfer-Encoding: base64'
|| crlf
|| crlf;
ELSE
mesg :=
mesg
|| 'Content-Type: application/octet-stream; name="'
|| file_name
|| '"'
|| crlf
|| 'Content-Disposition: attachment; filename="'
|| file_name
|| '"'
|| crlf
|| 'Content-Transfer-Encoding: 7bit'
|| crlf
|| crlf;
END IF;
UTL_SMTP.write_data (conn, mesg);
AMERICA_ATTACH_APPEND (directory_path, file_name, filetype, conn);
UTL_SMTP.write_data (conn, crlf);
END IF;
mesg := crlf || '--' || boundary || '--' || crlf;
UTL_SMTP.write_data (conn, mesg);
UTL_SMTP.close_data (conn);
UTL_SMTP.quit (conn);
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
my_code := SQLCODE;
my_errm := SQLERRM;
BEGIN
UTL_SMTP.quit (conn);
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
NULL;
END;
raise_application_error
(-20000,
'Failed to send mail - SMTP server down or unavailable: Error code '
|| my_code
|| ': '
|| my_errm
);
WHEN OTHERS
THEN
my_code := SQLCODE;
my_errm := SQLERRM;
raise_application_error (-20000,
'Failed to send mail: Error code '
|| my_code
|| ': '
|| my_errm
);
END;
END AMERICA_ATTACH;
/
3. Call Email procedure to send america.xls file to desired user :-
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
Step:1 Check the database size in source. Step:2 check which tablespace holds the schema objects. Step:3 compile invalid objects in source. step:4 check the count of invalid dba_objects in the source. Step:5 create a directory for export purpose both in OS level and database level. Step:6 Estimate the size of dumpfile, so that we can know when will the export get completed. Step:7 export the database(TANSTAL). Step:8 create a fresh database for import. Step:9 Now create a directory in both OS level and database level for import purpose in the newly created database. Step:10 import the database.(ZHIGOMA) Step:11 Post upgrade steps in target database. 11.1 compile invalid objects in target database. 11.2 check whether any invalid objects present. 11.3 Run the query to check for currently installed database components.
Step:12 Verify the timezone of the upgraded database.
Step:13 Check the CONSTRAINTS count in both source & target.it is used to display the constraints that are defined in the database.
Step:1 Check the database size in source .
SQL> col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
Database Size Used space Free space
-------------------- -------------------- --------------------
2 GB 2 GB 0 GB
SQL>
Step:2 execute the following query to check which tablespace holds the schema objects.
set pagesize 130
break on Tablespace on Owner
column Objects format A20
select Tablespace_Name,Owner,COUNT(*)||’ tables’ Objects
from DBA_TABLES
group by Tablespace_Name,Owner
union
select Tablespace_Name, Owner, COUNT(*)||’ indexes’ Objects
from DBA_INDEXES
group by Tablespace_Name, Owner;
Step:3 compile invalid objects in source to reduce dependencies, so that we can find the best way to recompile it.
SQL> @?/rdbms/admin/utlrp.sql
COMP_TIMESTAMP UTLRP_BGN 2021-01-31 20:19:36
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
COMP_TIMESTAMP UTLRP_END 2021-01-31 20:19:39
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
step:4 check the count of invalid dba_objects in the source.
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
----------
0
Step:5 create a directory for export purpose both in OS level and database level.
mkdir /u01/export
create directory export as '/u01/export';
Directory created
[oracle@orcldbs ~]$ mkdir export
[oracle@orcldbs ~]$ ls
12c.env Documents export oraprod.env Templates
19c.env Downloads Music Pictures utlrp.out
Desktop em13400_linux64-3.zip oradiag_oracle Public Videos
Create a table employee in the user : rahul.
Step:6 Estimate the size of dumpfile by this we are able to know the completion time of export activity.
Once the import is over, you can also review the log file.
13.1 compile invalid objects in target database.
SQL> @?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*) --------
0
SQL> SELECT version FROM v$timezone_file;
VERSION ----------------- 32
13.3 Run the query to check for currently installed database components.
SQL> col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
13.4 Check the CONSTRAINTS count,it is used to display the constraints that are defined in the database.
SQL> SELECT constraint_type, count(*) AS num_constraints
FROM dba_constraints
GROUP BY constraint_type;
C NUM_CONSTRAINTS
C 5784
F 12
O 181
R 327
P 864
V 11
U 250
7 rows selected.
SQL>
yes now we can login to the zhigoma database and we can verify our user(rahul) and the table(employee) in that user is imported into this database or not.
We have successfully upgraded our database from 12 c to 19c using datapump !!!!
It is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored, or as a diagnostic aid when you have encountered data corruption problems.
Overview:
Verifying the table and tablespaces
Checking the header block for the table
Stimulating a corruption
Recovering the block using RMAN
Verifying using DATABASE BLOCK CORRUPTION
Step 1:
Verifying the TABLESPACE_NAME and its associated SEGMENT_NAME.
SQL> select SEGMENT_NAME,TABLESPACE_NAME from dba_segments where OWNER=’SCOTT’;
Command to recover the block and datafile using RMAN, The datafile and corrupted block we can check it in the previous step ‘DBVERIFY-DBV’ command
recover datafile 7 block 347;
Verification’s:
After recovering there are no rows found in database block corruption , And after issuing dbverify command ,there we can see the marked corruption is ‘0’
Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
DDL ‘s of Objects in a Schema :-select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME') from dual;
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME') from dual;
Last DDL Date:-
select to_char(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') from dba_objects where owner='&user' and object_name='&table_name';
Generating DDL of table:-
set long 1000
select dbms_metadata.get_ddl('TABLE','EMP','SCHEMA')||'/' from dual;
set lines 1000 long 2000 pages 9999
select dbms_metadata.get_ddl('TABLE', table_name) from user_tables;
Generate DDL of DB_LINK:-
SET LONG 1000
SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) from dba_db_links db;
SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '
||L.PASSWORD||' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L, sys.user$ U
WHERE L.OWNER# = U.USER#;
Generate Sequence DDL:-
select 'CREATE SEQUENCE '||SEQUENCE_NAME||chr(10)||
' INCREMENT BY '||INCREMENT_BY||chr(10)||
' START WITH '||LAST_NUMBER||chr(10)||
' MINVALUE '||MIN_VALUE||chr(10)||
' MAXVALUE '||MAX_VALUE||chr(10)||
decode(CYCLE_FLAG,'N',' NOCYCLE','CICLE')||chr(10)||
decode(ORDER_FLAG,'N',' NOORDER','ORDER')||chr(10)||
' CACHE '||CACHE_SIZE|| ';'
from DBA_SEQUENCES where SEQUENCE_OWNER='&OWNER_NAME';
set long 2000
select (case
when ((select count(*)
from dba_users
where username = 'ODB') > 0)
then dbms_metadata.get_ddl ('USER', 'ODB')
else to_clob (' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = 'ODB') > 0)
then dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'ODB')
else to_clob (' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = 'ODB') > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', 'ODB')
else to_clob (' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = 'ODB') > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', 'ODB')
else to_clob (' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = 'ODB') > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', 'ODB')
else to_clob (' -- Note: No Object Privileges found!')
end ) from dual
/spool Riskusercreate.sql
set pagesize 0
set escape on
select 'create user ' || U.username || ' identified ' ||
DECODE(password,
NULL, 'EXTERNALLY',
' by values ' || '''' || password || ''''
)
|| chr(10) ||
'default tablespace ' || default_tablespace || chr(10) ||
'temporary tablespace ' || temporary_Tablespace || chr(10) ||
' profile ' || profile || chr(10) ||
'quota ' ||
decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||
' on ' || default_tablespace ||
decode (account_status,'LOCKED', ' account lock',
'EXPIRED', ' password expire',
'EXPIRED and LOCKED', ' account lock password expire',
null)
||
';'
from dba_users U, dba_ts_quotas Q
-- Comment this clause out to include system and default users
where U.username not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;
SELECT 'ALTER USER '||username||' QUOTA '||
DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
||' ON '||tablespace_name||';' lne
FROM DBA_TS_QUOTAS
ORDER BY USERNAME;
select 'GRANT '||granted_role||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where grantee like upper('%&&uname%')
UNION
select 'GRANT '||privilege||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
where grantee like upper('%&&uname%')
UNION
select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' ||TABLE_NAME || ' to ' || GRANTEE || ';'
from dba_tab_privs
where grantee like upper('%&&uname%');
set pagesize 100
set escape off
spool offset long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/variable v_username VARCHAR2(30);
exec:v_username := upper('&1');
select dbms_metadata.get_ddl('USER', u.username) AS ddl
from dba_users u
where u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from dba_ts_quotas tq
where tq.username = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rp.default_role = 'YES'
and rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> 'DEFAULT'
and rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from dba_users u
where u.username = :v_username
and u.profile <> 'DEFAULT'
and rownum = 1
/set linesize 80 pagesize 14 feedback on trimspool on verify on
select dbms_metadata.get_ddl('USER', 'KPHU000') || '/' usercreate from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','KPHU000') || '/' FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','KPHU000') || '/' FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','KPHU000') || '/' FROM DUAL;
select DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', 'KPHU000') '/' from dual;
Roles,system privs,other privs granted to user:-
select dbms_metadata.get_ddl('USER', '&USER') || '/' usercreate from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&USER') || '/' FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&USER') || '/' FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&USER') || '/' FROM DUAL;
select DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', '&USER') '/' from dual;
Single Objects Grants any type of Object :-
select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' ||TABLE_NAME || ' to ' || GRANTEE || ';' from dba_tab_privs where table_name='&Any_type_of_object_name' ;
Password DDL:-
select 'alter user ' || NAME ||' identified by values ''' || password || ''';' from user$;
Cannot reuse the password:-
declare
userNm varchar2(100);
userpswd varchar2(100);
begin
userNm := upper('&TypeUserNameHere');
select password into userpswd from sys.user$ where name = userNm;
execute immediate ('ALTER PROFILE "FUNCTIONAL_USER" LIMIT
PASSWORD_VERIFY_FUNCTION null
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED');
execute immediate ('alter user '||userNm||' identified by oct152014oct');
execute immediate ('alter user '||userNm||' identified by values '''||userpswd||'''');
execute immediate ('ALTER PROFILE "FUNCTIONAL_USER" LIMIT
PASSWORD_VERIFY_FUNCTION PASSWDCOMPLEXVERIFICATION');
end;
/ORA-28003: password verification for the specified password failedORA-20009: Error: You cannot change passwordSQL> ALTER PROFILE FUNCTIONAL_USER LIMIT PASSWORD_VERIFY_FUNCTION NULL;
Profile altered.
SQL> alter user trial identified by test;
User altered.
SQL> conn trial/test;
ALTER PROFILE "FUNCTIONAL_USER" LIMIT PASSWORD_VERIFY_FUNCTION PASSWDCOMPLEXVERIFICATION;
Profile DDL:-
select ' alter user '||username||' profile '||PROFILE||';' from dba_users;
select 'grant ' || GRANTED_ROLE || ' to ' || ROLE || ';' from role_role_privs where role='&ROLE'
union
select 'grant ' || PRIVILEGE || ' to ' || ROLE || ';' from role_sys_privs where role='&&ROLE'
union
select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' ||TABLE_NAME || ' to ' || GRANTEE || ';' from dba_tab_privs where GRANTEE='&&ROLE';
Roles DDL:-
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/variable v_role VARCHAR2(30);
exec :v_role := upper('&1');
select dbms_metadata.get_ddl('ROLE', r.role) AS ddl
from dba_roles r
where r.role = :v_role
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_role
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = :v_role
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = :v_role
and rownum = 1
/set long 100000
set longchunksize 200
set heading off
set feedback off
set echo off
set verify off
undefine role
select (case when ((select count(*) from dba_roles
where role = '&&role') > 0)
then dbms_metadata.get_ddl ('ROLE', '&&role')
else to_clob ('Role does not exist')
end ) Extracted_DDL from dual UNION ALL select (case when ((select count(*)
from dba_role_privs where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&role')
end ) from dual UNION ALL select (case when ((select count(*)
from dba_role_privs where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('DEFAULT_ROLE', '&&role')
end ) from dual UNION ALL select (case when ((select count(*)
from dba_sys_privs where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&role')
end ) from dual UNION ALL select (case when ((select count(*)
from dba_tab_privs where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&role')
end ) from dual;
create a role and assign all privileges to the role:-
create role L1_SUPPORT;
create role L2_SUPPORT;
create role L3_SUPPORT;
set pagesize 0
set echo off
set trimspool on
set linesize 120
set feedback off
spool grant.sql
select 'grant select,insert,update,delete on ' || a.owner || '."' ||
a.table_name || '"' || CHR(10) ||
' to L2_SUPPORT;' || CHR(10) ||
'grant select on ' || a.owner || '."' ||
a.table_name || '"' || CHR(10) ||
' to L1_SUPPORT;' || CHR(10) ||
'grant select on ' || a.owner || '."' ||
a.table_name || '"' || CHR(10) ||
' to L3_SUPPORT;'
from dba_tables a
where owner in ('SCHEMA_NAME')
order by owner, table_name;
spool off
set feedback on
Roles comparison between databases:-
DB link : dev to prod CREATE DATABASE LINK "COMPARE" CONNECT TO DBSNMP IDENTIFIED BY mypwd USING 'destonation-db-name';
select * from DBA_TAB_PRIVS@compare T1 where not exists (select 1 from dba_tab_privs T2 where t1.TABLE_NAME=t2.TABLE_NAME and t1.PRIVILEGE=t2.PRIVILEGE and t1.GRANTEE=t2.GRANTEE ) and t1.grantee='&ROLE_NAME';
No of users have a particular role:-
select GRANTEE as users from dba_role_privs where GRANTED_ROLE=UPPER('&GRANTED_ROLE');
select * from DBA_TAB_PRIVS where grantee like upper('%&enter_username%');
select * from DBA_ROLE_PRIVS where grantee like upper('%&enter_username%');
select * from DBA_SYS_PRIVS where grantee like upper('%&enter_username%');
select * from DBA_COL_PRIVS where grantee like upper('%&enter_username%');
ALL Privileges for a SINGLE user:-
select 'GRANT '||granted_role||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where grantee like upper('%&&uname%')
UNION
select 'GRANT '||privilege||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
where grantee like upper('%&&uname%')
UNION
select 'grant ' || PRIVILEGE || ' on ' || OWNER || '.' ||TABLE_NAME || ' to ' || GRANTEE || ';'
from dba_tab_privs
where grantee like upper('%&&uname%');
List of Users/Roles having privilege on TABLE:-
SELECT grantee || ' Through role ' || granted_role ge, 'SELECT' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'SELECT')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'UPDATE' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'UPDATE')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'INSERT' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'INSERT')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'DELETE' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'DELETE')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'INDEX' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'INDEX')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'ALTER' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'ALTER')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'REFERENCES' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'REFERENCES')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Through role ' || granted_role ge, 'EXECUTE' priv
FROM dba_role_privs
START WITH granted_role IN (SELECT grantee
FROM dba_tab_privs
WHERE PRIVILEGE = 'EXECUTE')
CONNECT BY PRIOR grantee = granted_role
UNION
SELECT grantee || ' Direct' ge, PRIVILEGE priv
FROM SYS.dba_tab_privs
WHERE table_name = UPPER ('&TABLE_NAME')
ORDER BY 1, 2;select
case when level = 1 then own || '.' || obj || ' (' || typ || ')' else
lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
end from (
/* THE OBJECTS */
select null p1,null p2,object_name obj,owner own,object_type typ from
dba_objects where owner like upper('%&enter_username%')
/* THE OBJECT TO PRIVILEGE RELATIONS */
union select table_name p1, owner p2, grantee, grantee, privilege
from dba_tab_privs
/* THE ROLES TO ROLES/USERS RELATIONS */
union select granted_role p1, granted_role p2, grantee,grantee,null
from dba_role_privs ) start with p1 is null and p2 is null
connect by p1 = prior obj and p2 = prior own;
Privileges granted by you to others:-
LISTING INFORMATION ABOUT PRIVILEGES —————————————————————————— To see which table privileges are granted by you to other users. SELECT * FROM USER_TAB_PRIVS_MADE To see which table privileges are granted to you by other users SELECT * FROM USER_TAB_PRIVS_RECD; To see which column level privileges are granted by you to other users. SELECT * FROM USER_COL_PRIVS_MADE To see which column level privileges are granted to you by other users SELECT * FROM USER_COL_PRIVS_RECD; To see which privileges are granted to roles SELECT * FROM USER_ROLE_PRIVS;
System privileges to roles and users:-
select lpad(' ', 2*level) || c "Privilege, Roles and Users" from (
/* THE PRIVILEGES */
select null p,name c from system_privilege_map
where name like upper('%&enter_privliege%')
/* THE ROLES TO ROLES RELATIONS */
union select granted_role p,grantee c from dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union select privilege p,grantee c from dba_sys_privs) start with p is null
connect by p = prior c;
Granted role permissions:-
select * from ROLE_ROLE_PRIVS where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME USER);
select * from ROLE_TAB_PRIVS where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME=USER);
select * from ROLE_SYS_PRIVS where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME=USER);
Roles & privs for a user:-
Granted Roles:SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USER'; Privileges Granted Directly To User:
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'USER'; Privileges Granted to Role Granted to User:
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN (SELECT granted_role
FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USER'); Granted System Privileges:
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'USER';
when was the password was changed for a user?
SELECT NAME, TO_CHAR (ptime, ‘DD-MON-YYYY HH24:MI:SS’) AS “LAST TIME CHANGED”, ctime “CREATION TIME”, ltime “LOCKED” FROM USER$ WHERE ptime IS NOT NULL ORDER BY ptime DESC;
NON Default database users:-
select username from dba_users where username not in ( 'ANONYMOUS','AURORA','$JIS','$UTILITY','$AURORA','$ORB','$UNAUTHENTICATED','CTXSYS',
'DBSNMP','DMSYS','DSSYS','EXFSYS','MDSYS','ODM','ODM_MTR','OLAPSYS','ORDPLUGINS',
'ORDSYS','OSE$HTTP$ADMIN','OUTLN','PERFSTAT','PUBLIC','REPADMIN','SYS','SYSMAN',
'SYSTEM','TRACESVR','WKPROXY','WKSYS','WMSYS','XDB','APEX_PUBLIC_USER' ,'APEX_030200',
'APPQOSSYS','BI' ,'DIP' ,'FLOWS_XXXXXX' ,'HR','IX' ,'LBACSYS' ,'MDDATA' ,'MGMT_VIEW' ,
'OE' ,'ORACLE_OCM' ,'ORDDATA' ,'PM' ,'SCOTT' ,'SH' ,'SI_INFORMTN_SCHEMA' ,
'SPATIAL_CSW_ADMIN_USR' ,'SPATIAL_WFS_ADMIN_USR','MTSSYS','OASPUBLIC','OLAPSYS',
'OWBSYS','OWBSYS_AUDIT','WEBSYS','WK_PROXY','WK_TEST','AURORA$JIS$UTILITY$',
'SECURE','AURORA$ORB$UNAUTHENTICATED','XS$NULL','FLOWS_FILES');
DDL of V$ Tables:-
set long 10000 select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where view_name=’GV$SQL_MONITOR’;
Script to Generate DDL ‘s of Various Objects of database :-
Script for DDL ‘s of All Indexes of database:-
SQL> select ‘select dbms_metadata.get_ddl(”INDEX”,”’ || index_name|| ”’ ) from dual;’ from user_indexes;SQL> select ‘select dbms_metadata.get_ddl(”INDEX”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’INDEX’;SQL> select ‘select dbms_metadata.get_ddl(”INDEX”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’INDEX’ and owner not in (‘SYS’,’SYSTEM’);Script for DDL’s of all Tables of database:- SQL> select ‘select dbms_metadata.get_ddl(”TABLE”,”’ || table_name|| ”’ ) from dual;’ from user_tables;SQL> select ‘select dbms_metadata.get_ddl(”TABLE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’TABLE’;SQL> select ‘select dbms_metadata.get_ddl(”TABLE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’TABLE’ and owner not in (‘SYS’,’SYSTEM’);Script for DDL’s of All Procedures of database:-SQL> select ‘select dbms_metadata.get_ddl(”PROCEDURE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’PROCEDURE’;SQL> select ‘select dbms_metadata.get_ddl(”PROCEDURE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’PROCEDURE’ and owner not in (‘SYS’,’SYSTEM’);SQL> select ‘select dbms_metadata.get_ddl(”PROCEDURE”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’PROCEDURE’ and owner=’OWNER_NAME’;Script for DDL’s of All Functions of database :-SQL> select ‘select dbms_metadata.get_ddl(”FUNCTION”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’FUNCTION’;SQL> select ‘select dbms_metadata.get_ddl(”FUNCTION”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’FUNCTION’ and owner not in (‘SYS’,’SYSTEM’);SQL> select ‘select dbms_metadata.get_ddl(”FUNCTION”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’FUNCTION’ and owner=’OWNER_NAME’;Script for DDL’s of All Triggers of database:-SQL> select ‘select dbms_metadata.get_ddl(”TRIGGER”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’TRIGGER’;SQL> select ‘select dbms_metadata.get_ddl(”TRIGGER”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’TRIGGER’ and owner not in (‘SYS’,’SYSTEM’);SQL> select ‘select dbms_metadata.get_ddl(”TRIGGER”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’TRIGGER’ and owner=’OWNER_NAME’;Script for DDL’s of All Views of database:-SQL> select ‘select dbms_metadata.get_ddl(”VIEW”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’VIEW’;SQL> select ‘select dbms_metadata.get_ddl(”VIEW”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’VIEW’ and owner not in (‘SYS’,’SYSTEM’);SQL> select ‘select dbms_metadata.get_ddl(”VIEW”,”’ || OBJECT_name|| ”’,”’ || owner|| ”’) from dual;’ from dba_OBJECTS where object_type=’VIEW’ and owner=’OWNER_NAME’; Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
Here I am going to upgrade steps of Oracle Database Home 19c from 19.3 to 19.9 within Oracle Linux 8.2 The patch is 31771877 – Database Release Update 19.9.0.0.201020Prerequisites: “You must use the OPatch utility version 12.2.0.1.19 or later to apply this patch.” from READ ME of the Patch 31771877 Checking current ORACLE_HOME – OPATCH Version:
[oracle@db101:USA] ./opatch version
OPatch Version: 12.2.0.1.17
OPatch succeeded.
Hope everyone is aware of how to upgrade the opatch utility. We need to take a backup of the existing patch and replace our new downloaded opatch in the ORACLE_HOME location.
Step 1: Install OPatch utility “6880880”:
Backup the Old OPatch utility
[oracle@db101:USA] cd [oracle@db101:USA]ORACLE_HOME
[oracle@db101:USA] mv OPatch/ OPatch_backup
Now Its upgraded to Oracle Database Home 19.9 version. Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
