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
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
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
set lines 750 pages 9999
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report
col username for a50
select DECODE(username,NULL,'INTERNAL',USERNAME) Username, count(*) TOT,
COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE, COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE from gv$session where status in ('ACTIVE','INACTIVE') group by username;
Users Details Session:
For Cluster:
set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30
column program format a30
column os_user format a20
col LOGON_TIME for a20
select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,substr(b.username,1,30) username,
substr(b.osuser,1,20) os_user,substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from gv$session b,gv$process a where b.paddr = a.addr
and a.inst_id = b.inst_id and type='USER' order by logon_time;
For Standalone:
set pages 500
set linesize 750
column box format a30
column spid format a10
column username format a30
column program format a30
column os_user format a20
select b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,b.logon_time logon_date , to_char (b.logon_time, 'hh24:mi:ss') logon_time, substr(b.username,1,30) username, substr(b.osuser,1,20) os_user, substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from v$session b,v$process a where b.paddr = a.addr
and type='USER' order by b.sid;
(or)
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /+ CHOOSE/ 'Session Id.............................................: '||s.sid, 'Serial Num..............................................: '||s.serial#, 'User Name ..............................................: '||s.username, 'Session Status .........................................: '||s.status, 'Client Process Id on Client Machine ....................: '||''||s.process||'' Client, 'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address, 'Sql_hash_value .........................................: '||s.sql_hash_value, 'Schema Name ..... ......................................: '||s.SCHEMANAME, 'Program ...............................................: '||s.program, 'Module .................................................: '|| s.module, 'Action .................................................: '||s.action, 'Terminal ...............................................: '||s.terminal, 'Client Machine .........................................: '||s.machine, 'LAST_CALL_ET ...........................................: '||s.last_call_et, 'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600 from v$session s, v$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and p.spid=nvl('&spid',p.spid) and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
To Find Session Information Details based on SID or SPID or CLIENTPID :
col program for a15
col machine for a15
col terminal for a15
set lines 152
select s.sid,s.serial#,''||s.process||'' Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,s.action, s.program || s.module,s.terminal,s.machine, s.status,s.last_call_et,s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
Timing details, Client PID of associated oracle SID:
undefine spid
set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999
select p.INST_ID,p.spid,s.sid, s.serial#, s.status, s.username, s.action,
to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time,
s.module,s.program,s.last_call_et/3600 last_call_et ,s.process cli_process,s.machine
cli_mach,s.sql_hash_value
from gv$session s, gv$process p
where p.addr=s.paddr and p.spid in(&SPID);
Checking Timing Details of SID and waiting event:
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME, to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a, v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid;
Checking for active transactions SID
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and s.sid in(&SIDs);
Checking what is the Last SQL
undefine sid
col "Last SQL" for a70
select s.username, s.sid, s.serial#,t.sql_text "Last SQL"
from gv$session s, gv$sqlarea t
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.sid in (&SIDs);
All Active and Inactive connections
col program for a15F
col machine for a15
col terminal for a15
set lines 152
select s.sid,s.serial#,''||s.process||'' Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,s.action,
s.program || s.module,s.terminal,s.machine, s.status,s.last_call_et,s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.type != 'BACKGROUND';
Active sessions
select p.spid "Thread", s.sid "SID-Top Sessions",
substr(s.osuser,1,15) "OS User", substr(s.program,1,25) "Program Running"
from v$process p, v$session s where p.addr=s.paddr order by substr(s.osuser,1,15);
Session details from Session long ops
select SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME,username from v$session_longops where sid=&SID and serial#=&SERIAL;
To list the nodes
set head off
set verify off
set echo off
set pages 1500
set linesize 70
prompt
prompt Environment sketch
prompt ==================================
select /+ CHOOSE/
'NODE_NAME.................: '||NODE_NAME,
'CREATION_DATE.............: '||CREATION_DATE,
'CREATED_BY ...............: '||CREATED_BY,
'SUPPORT_CP ...............: '||SUPPORT_CP,
'SUPPORT_FORMS ............: '||SUPPORT_FORMS,
'SUPPORT_WEB ..............: '||SUPPORT_WEB,
'SUPPORT_ADMIN ............: '||SUPPORT_ADMIN,
'STATUS ...................: '||STATUS,
'HOST.DOMAIN ..... ........: '||HOST||'.'||DOMAIN,
'SUPPORT_DB ..............: '||SUPPORT_DB
from apps.fnd_nodes;
Session details through SPID:
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '18924');
To list count of connections from other machines:
select count(1),machine from gv$session where inst_id=3 group by machine;
To get total count of sessions and processes:
select count(*) from v$session;
select count(*) from v$process;
select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;
To find sqltext through sqladdress:
select sql_address from v$session where sid=8794;
select sql_text from v$sqltext where ADDRESS='A00000089A00FA0' order by PIECE;
To find sqltext for different sql hashvalue:
select hash_value,sql_text from v$sql where hash_value in (193756789,156412346,
456812142,8971248686,2358702945);
To list long running forms user sessions:
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;
To list inactive Sessions respective username:
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;
SELECT count() FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE'; SELECT count() FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';
To find session id with set of SPIDs:
select sid from v$session, v$process where addr=paddr and spid in ('11214','12948','90088');
To find Sql Text given SQLHASH & SQLADDR:
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece; select piece,sql_text from v$sqltext where ADDRESS ='&addr' order by piece;
To find Undo Generated For a given session:
select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid='&sessionid';
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
col sid_serial for a20
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial; (or) set lines 1500 pages 9999
column sid format 9999
column username format a15
column SQL_EXEC_START for a21
column sql_text format a50
column module format a35
column sql_text format a50SELECT a.inst_id,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_USED,a.sql_id,a.sql_child_number child,c.plan_hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,c.rows_processed,a.status,
-- c.sql_text
substr(c.sql_text,1,50) sql_text
FROM gv$session a, gv$tempseg_usage b, gv$sqlarea c
,(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
-- AND c.hash_value = a.sql_hash_value
AND a.inst_id=b.inst_id
ORDER BY 6 desc; (or) set lines 1500 pages 9999
column sid format 9999
column username format a15
column SQL_EXEC_START for a21
column sql_text format a50
column module format a35
column sql_text format a50
break on report
compute SUM of MB_USED on report
SELECT a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_USED,a.sql_id,a.sql_child_number child,c.plan_hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,c.rows_processed,a.status,
-- c.sql_text
substr(c.sql_text,1,50) sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, 5 desc; (or) select ROUND(SUM(tempseg_size)/1048576) temp_mb from gv$sql_workarea_active WHERE sid=&sid;
History of Temp tablespace usage:
select sql_id,SQL_PLAN_HASH_VALUE,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig
from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time > sysdate-10 and TEMP_SPACE_ALLOCATED > (10*1024*1024*1024) group by sql_id,SQL_PLAN_HASH_VALUE order by sql_id;
Shrink Temp file:
ALTER TABLESPACE temp SHRINK TEMPFILE '+ORA_DATA/JAPAN/tempfile/temp.313.868020865' KEEP 500M; 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
select inst_id,name,value from gv$parameter where name like '%undo%';
UNDO tablespaces Free As per expired segments:
SELECT d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+ u.exp_space),2)
as max_free_mb, round(((a.bytes - (NVL(f.bytes,0)+ (10241024u.exp_space)))100/a.maxbytes),2)
used_pct FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes,
sum(greatest(maxbytes,bytes)) maxbytes from sys.dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f , (select tablespace_name , sum(blocks)8/(1024) exp_space from
dba_undo_extents where status NOT IN ('ACTIVE','UNEXPIRED') group by tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name=u.tablespace_name AND d.contents = 'UNDO' AND u.tablespace_name = (select UPPER(value) from v$parameter where name = 'undo_tablespace');
does undo have expired segments?
select status, count(1) from dba_undo_extents group by status;
User Generated UNDO
col sql_text format a40
set lines 130
select sq.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t,
v$session s,
v$sql sq
where t.addr = s.taddr
and s.sql_id = sq.sql_id
and s.username = '<user>'
/
UNDO tablespace usage by USER
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'; (or) select username, t.used_ublk ,t.used_urec from gv$transaction t,gv$session s
where t.addr=s.taddr and s.sid='&sid';
Recreating the undo tablespace or resolving to undo issue
select a.name,b.status , d.username , d.sid , d.serial#
from v$rollname a,v$rollstat b, v$transaction c , v$session d
where a.name IN ( select segment_name
from dba_segments where tablespace_name = 'UNDOTBS1')
and a.usn = b.usn
and a.usn = c.xidusn
and c.ses_addr = d.saddr;
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=TRUE scope=both;
If TRUE means it will not show any undo errors, So some times job may complete successfully.
Find active, expired, unexpired undo
select status, round(sum_bytes / (1024*1024), 0) as MB, round((sum_bytes / undo_size) * 100, 0) as PERC from ( select status, sum(bytes) sum_bytes from dba_undo_extents group by status ), ( select sum(a.bytes) undo_size from dba_tablespaces c join v$tablespace b on b.name = c.tablespace_name join v$datafile a on a.ts# = b.ts# where c.contents = 'UNDO' and c.status = 'ONLINE' );select count(status) from dba_undo_extents where status = 'EXPIRED';
select count(status) from dba_undo_extents where status = 'UNEXPIRED';
select count(status) from dba_undo_extents where status = 'ACTIVE';
Find active, expired, and unexpired undo by Size in MB
select status,TABLESPACE_NAME,sum(bytes) / 1024 / 1024 || 'MB' from DBA_UNDO_EXTENTS group by status,TABLESPACE_NAME;select status, sum(bytes) / 1024 / 1024 || 'MB' from DBA_UNDO_EXTENTS group by status;
Undo advisor:
SELECT d.undo_size/(10241024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a,
v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'
AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f,
( SELECT MAX(undoblks/((end_time-begin_time)3600*24)) undo_block_per_sec
FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size'; (or) SELECT dbms_undo_adv.required_undo_size(1800, SYSDATE-30, SYSDATE) FROM dual;
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
set lines 1000 pages 9999
col name for a30
col task_exec_name for a16
col category for a10
col created for a30
col sql_text for a150
col signature for 9999999999999999999999999
select sql.sql_id,sql.child_number as child , prof.name, prof.category, prof.created, prof.task_exec_name,prof.FORCE_MATCHING, prof.status, prof.SIGNATURE,prof.SQL_TEXT
from
dba_sql_profiles prof,
gv$sql sql
where sql.sql_id in ('&sql_id')
order by
created;
SQL Baselines:
Check the current baseline:
select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED ,sql_text from dba_sql_plan_baselines;select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SQL_PLAN_b7pnq6yp8da6a29d0d9b7'));
Drop the baseline:
set serveroutput on
DECLARE
i NATURAL;
BEGIN
i := dbms_spm.drop_sql_plan_baseline('SQL_b3d69637aa86a8ca');
dbms_output.put_line(i);
END;
/
SQL_HANDLE PLAN_NAME ENA ——————————————————————————————————————————– ——————————————————————————————————————————– — SQL_d3318f33dfac7bc2 SQL_PLAN_d6ccg6ggusyy2ee7f0c12 YES variable sqlid number;
exec :sqlid :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( SQL_HANDLE=> 'SQL_d3318f33dfac7bc2', PLAN_NAME => 'SQL_PLAN_d6ccg6ggusyy2ee7f0c12', ATTRIBUTE_NAME=> 'enabled', ATTRIBUTE_VALUE => 'NO'); Now fix the plan of 31pux6bymf1d4 to 5qbbnv0abm2vx 5qbbnv0abm2vx variable sqlid number; execute :sqlid :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>’5qbbnv0abm2vx’, PLAN_HASH_VALUE=> 4197102931, SQL_HANDLE => ‘SQL_d3318f33dfac7bc2’); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5qbbnv0abm2vx',&childnumber,'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +OUTLINE +PREDICATE +COST +BYTES')); 4197102931
Extract hint from all profile/baseline/patch :
All in one script:
set pagesize 60
set linesize 180
set trimspool on
column plan_name format a32
column signature format 999,999,999,999,999,999,999
column category format a10
column hint format a70 wrap word
break on plan_name skip 1 on signature skip 1 on opt_type skip 1 on category skip 1 on plan_id skip 1
spool sql_profile_baseline_11g
select prf.plan_name,prf.signature,decode(obj_type,1,'Profile',2,'Baseline',3,'Patch',
'Other') opt_type,prf.category,prf.plan_id,extractvalue(value(hnt),'.') hint from
( select /*+ no_eliminate_oby */ so.name plan_name, so.signature,so.category,
so.obj_type,so.plan_id,sod.comp_data from
sqlobj$ so,sqlobj$data sod where so.name = '&m_plan_name'
and sod.signature = so.signature and sod.category = so.category
and sod.obj_type = so.obj_type and sod.plan_id = so.plan_id
order by signature, obj_type, plan_id ) prf, table ( select
xmlsequence( extract(xmltype(prf.comp_data),'/outline_data/hint')
)from dual ) hnt;
Taking SQL HINTS from memory:
SELECT chr(9)||chr(9)||''''||regexp_replace(extractvalue(value(d), '/hint'),'''','''''')||''','
from
xmltable('/*/outline_data/hint'
passing ( select xmltype(other_xml) as xmlval
from v$sql_plan where
sql_id like nvl('&sql_id',sql_id)
and child_number = &child_no
and other_xml is not null ) ) d;
Taking SQL HINTS from AWR:
SELECT chr(9)||chr(9)||''''||regexp_replace(extractvalue(value(d), '/hint'),'''','''''')||''','
from xmltable('/*/outline_data/hint'
passing ( select xmltype(other_xml) as xmlval
from
-- v$sql_plan
dba_hist_sql_plan where sql_id like nvl('&sql_id',sql_id) and other_xml is not null )) d;
Taking SQL HINTS from PROFILES
select hint as outline_hints
from (select p.name, p.signature, p.category, row_number()
over (partition by sd.signature, sd.category order by sd.signature) row_num,
extractValue(value(t), '/hint') hint
from sqlobj$data sd, dba_sql_profiles p,
table(xmlsequence(extract(xmltype(sd.comp_data),
'/outline_data/hint'))) t
where sd.obj_type = 1
and p.signature = sd.signature
and p.category = sd.category
and p.name like ('&profile_name'))
order by row_num;
Taking SQL HINTS from PLAN_TABLE:
SELECT regexp_replace(extractvalue(value(d), '/hint'),'''','''''') plan_hint
from xmltable('/*/outline_data/hint'
passing ( select xmltype(other_xml) as xmlval
from plan_table where other_xml is not null
and plan_id = (select max(plan_id) from plan_table)
and rownum=1 ) ) d;
Fixing the PLAN HASH VALUE:
Resolution normally is to fix the execution plan in 11g by running
variable x number
begin
:x :=
dbms_spm.load_plans_from_cursor_cache(
sql_id=>'&sql_id',
plan_hash_value=>&plan_hash,
fixed=>'YES');
end;
/
To see the hints from sql_profile
select hint from (
select s.sql_id, sd.obj_type,
row_number() over (partition by sd.signature, sd.category order by sd.signature) row_num,
extractValue(value(t), '/hint') hint
from sqlobj$data sd, v$sql s,
table(xmlsequence(extract(xmltype(sd.comp_data), '/outline_data/hint'))) t
where sd.obj_type = 1
and s.exact_matching_signature = sd.signature
and s.sql_id = '&sql_id')
order by row_num
/
Purge the old plan hash value from memory:
BEGIN
FOR i IN (SELECT address, hash_value
FROM gv$sqlarea WHERE sql_id = '&sql_id.')
LOOP
SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');
END LOOP;
END;
/
All in one script
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
Its like a doctor script. My suggestion is to don’t directly run all the scripts in prod it will make some performance issue. Use the specified query and execute step by step. Its easy to figure out your issues.
"
------------------------------------------------------------------------------------------------------------------------------------------------------
---
--- one script to Identify all performace issue related to sql query
------
--
--
------------------------------------------------------------------------------------------------------------------------------------------------------
Set echo off
set trimspool on
set define on
column filename new_val filename
select to_char(sysdate, 'yyyymmdd-hh-mi-ss' ) filename from dual;
column dbname new_value dbname noprint
select name dbname from v$pdbs;
spool &dbname-&filename..txt
PROMPT ==================
PROMPT DB INFO
PROMPT ==================
set lines 750 pages 9999
select name CDB_NAME,(select name from v$pdbs) PDB_NAME,database_role from v$database;
select INSTANCE_NAME,HOST_NAME,logins,VERSION from v$instance;
PROMPT ==================
PROMPT TOTAL Connections
PROMPT ==================
set lines 750 pages 9999
break on report
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report
col username for a50
select DECODE(username,NULL,'INTERNAL',USERNAME) Username,
count(*) TOT,
COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE,
COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE
from gv$session
where status in ('ACTIVE','INACTIVE')
group by username;
PROMPT ================
PROMPT Session Details
PROMPT ================
set linesize 750 pages 9999
column box format a30
col serial# for 999999
column spid format a10
column username format a30
column program format a30
column os_user format a20
col LOGON_TIME for a20
select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
substr(b.username,1,30) username,
substr(b.osuser,1,20) os_user,
substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id
from gv$session b,gv$process a
where b.paddr = a.addr
and a.inst_id = b.inst_id
and type='USER'
order by b.inst_id,b.sid;
PROMPT ================
PROMPT Sql Details
PROMPT ================
column sid format 9999
column username format a15
column PARSING_SCHEMA_NAME format a15
column sql_text format a50
column module format a35
select a.inst_id,a.sid,a.username,b.PARSING_SCHEMA_NAME,a.module,a.sql_id,a.sql_child_number child,b.hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,(sysdate-sql_exec_start)*24*60*60 SECS,b.rows_processed,a.status,substr(b.sql_text,1,50) sql_text
from gv$session a,gv$sqlarea b
where a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.module not like '%emagent%'
and a.module not like '%oraagent.bin%'
and a.username is not null
order by a.status;
PROMPT =========================
PROMPT Sql Monitor - REPORT
PROMPT =========================
column text_line format a1000
set lines 750 pages 9999
set long 20000 longchunksize 20000
select dbms_sqltune.report_sql_monitor_list() text_line from dual;
PROMPT =========================
PROMPT Sql Monitor - Executing
PROMPT =========================
set lines 1000 pages 9999
column sid format 9999
column serial for 999999
column status format a15
column username format a10
column sql_text format a80
column module format a30
col program for a30
col SQL_EXEC_START for a20
SELECT * FROM
(SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE,
program,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000) AS "CPU (s)",
substr(sql_text,1,30) sql_text
FROM gv$sql_monitor where status='EXECUTING' and module not like '%emagent%'
ORDER BY sql_exec_start desc
);
PROMPT ================
PROMPT Blocking Session
PROMPT ================
set lines 750 pages 9999
col blocking_status for a100
select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s1.inst_id;
PROMPT ==============================================================================
PROMPT More Details
PROMPT ==============================================================================
accept sid default '' -
prompt 'Please provide the sid: '
accept inst_id default '' -
prompt 'Please provide the inst_id: '
PROMPT ================
PROMPT SID Details
PROMPT ================
column box format a30
column spid format a10
column username format a20
column program format a30
column os_user format a20
col LOGON_TIME for a20
select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
substr(b.username,1,20) username,
substr(b.osuser,1,20) os_user,
substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id
from gv$session b,gv$process a
where b.paddr = a.addr
and a.inst_id = b.inst_id
and type='USER' and b.sid=&sid
and b.inst_id=&inst_id
order by logon_time;
column sid format 9999
column username format a15
column PARSING_SCHEMA_NAME format a15
column sql_text format a50
column module format a35
select a.inst_id,a.sid,a.username,b.PARSING_SCHEMA_NAME,a.module,a.sql_id,a.sql_child_number child,b.hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,(sysdate-sql_exec_start)*24*60*60 SECS,b.rows_processed,a.status,substr(b.sql_text,1,50) sql_text
from gv$session a,gv$sqlarea b
where a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.sid=&sid
and a.inst_id=&inst_id
and a.module not like '%emagent%'
and a.module not like '%oraagent.bin%'
and a.username is not null
order by a.status;
accept sql_id default '' -
prompt 'Please provide the sql_id: '
PROMPT ================
PROMPT SID Waiting on
PROMPT ================
COLUMN username FORMAT A20
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 999999
COLUMN event FORMAT A40
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
se.event,
se.total_waits,
se.total_timeouts,
se.time_waited,
se.average_wait,
se.max_wait,
se.time_waited_micro
FROM gv$session_event se,
gv$session s
WHERE s.sid = se.sid
AND s.sid = &sid
and s.inst_id=se.inst_id
and s.inst_id=&inst_id
ORDER BY se.time_waited DESC
/
col WAIT_CLASS for a10
SELECT sw.inst_id,NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM gv$session_wait sw,
gv$session s
WHERE s.sid = sw.sid and s.inst_id=sw.inst_id and s.sid=&sid
and s.inst_id=&inst_id
ORDER BY sw.seconds_in_wait DESC;
PROMPT ================
PROMPT Session LongOps
PROMPT ================
SET VERIFY OFF
SELECT a.sid,RPAD(a.opname,30),a.sofar,a.totalwork,a.ELAPSED_SECONDS,ROUND(((a.sofar)*100)/a.totalwork,3) "%_COMPLETED",time_remaining,
RPAD(a.username,10) username,a.SQL_HASH_VALUE,B.STATUS
FROM GV$SESSION_LONGOPS a, gv$session b
WHERE a.sid=&sid
and b.inst_id=&inst_id
AND a.sofar<> a.totalwork
/
PROMPT ==============================
PROMPT RealTime Monitoring For Sid
PROMPT ===============================
SELECT *
FROM
(SELECT status,
--username,
sql_id,
sql_exec_id,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000) AS "CPU (s)",
buffer_gets,
ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)",
ROUND(physical_write_bytes/(1024*1024)) AS "Phys writes (MB)"
FROM gv$sql_monitor where sid=&sid and inst_id=&inst_id
ORDER BY elapsed_time DESC
)
WHERE rownum<=20;
SELECT ROUND(elapsed_time /1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000,3) AS "CPU (s)",
ROUND(queuing_time /1000000,3) AS "Queuing (s)",
ROUND(user_io_wait_time /1000000,3) AS "I/O wait (s)",
ROUND(application_wait_time/1000000,3) AS "Appli wait (s)",
ROUND(concurrency_wait_time/1000000,3) AS "Concurrency wait (s)",
ROUND(cluster_wait_time /1000000,3) AS "Cluster wait (s)",
ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)",
ROUND(physical_write_bytes /(1024*1024)) AS "Phys writes (MB)",
buffer_gets AS "Buffer gets",
ROUND(plsql_exec_time/1000000,3) AS "Plsql exec (s)",
ROUND(java_exec_time /1000000,3) AS "Java exec (s)"
FROM gv$sql_monitor
WHERE sid=&sid and inst_id=&inst_id;
PROMPT ================================
PROMPT SQL_MONITOR REPORT
PROMPT ================================
set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000
select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id=>'&sql_id',
report_level=>'ALL',
type=>'TEXT')
from dual;
PROMPT ================================
PROMPT Full sql statement
PROMPT ================================
set lines 1000 pages 9999
set long 20000
col sql_text for a500
select sql_text from dba_hist_sqltext where sql_id = '&sql_id';
PROMPT ================
PROMPT Bind Variables
PROMPT ================
col VALUE_STRING for a50
SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='&sql_id' and inst_id=&inst_id;
PROMPT ================
PROMPT SQL History
PROMPT ================
set lines 1000 pages 9999
COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';
SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,
TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions_total,
TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE h.sql_id = '&sql_id'
AND h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
UNION ALL
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions,
TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,
TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,
TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,
TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,
TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,
TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,
TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,
TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM gv$sqlarea_plan_hash h
WHERE h.sql_id = '&sql_id'
and h.inst_id=&inst_id
AND h.executions > 0
order by source ;
PROMPT ================================
PROMPT Sql_id waiting on ?
PROMPT ================================
select
sql_id,event,
time_waited "time_waited(s)",
case when time_waited = 0 then
0
else
round(time_waited*100 / sum(time_waited) Over(), 2)
end "percentage"
from
(
select sql_id,event, sum(time_waited) time_waited
from gv$active_session_history
where sql_id = '&sql_id' and
inst_id=&inst_id
group by sql_id,event
)
order by
time_waited desc;
PROMPT ================================
PROMPT Object Statistics
PROMPT ================================
col table_name for a40
col owner for a30
select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked
from dba_tab_statistics
where (owner, table_name) in
(select distinct owner, table_name
from dba_tables
where ( table_name)
in ( select object_name
from gv$sql_plan
where upper(sql_id) = upper('&sql_id') and inst_id=&inst_id and object_name is not null))
--and STALE_STATS='YES'
/
col index_name for a50
SELECT owner, index_name, table_name,last_analyzed, sample_size, num_rows, partitioned, global_stats
FROM dba_indexes
WHERE index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
ORDER BY owner, table_name, index_name
/
PROMPT ================================
PROMPT Explain Plan from Memory
PROMPT ================================
col PLAN FOR a150
SELECT
RPAD('(' || p.plan_line_ID || ' ' || NVL(p.plan_parent_id,'0') || ')',8) || '|' ||
RPAD(LPAD (' ', 2*p.plan_DEPTH) || p.plan_operation || ' ' || p.plan_options,60,'.') ||
NVL2(p.plan_object_owner||p.plan_object_name, '(' || p.plan_object_owner|| '.' || p.plan_object_name || ') ', '') ||
NVL2(p.plan_COST,'Cost:' || p.plan_COST,'') || ' ' ||
NVL2(p.plan_bytes||p.plan_CARDINALITY,'(' || p.plan_bytes || ' bytes, ' || p.plan_CARDINALITY || ' rows)','') || ' ' ||
NVL2(p.plan_partition_start || p.plan_partition_stop,' PStart:' || p.plan_partition_start || ' PStop:' || p.plan_partition_stop,'') ||
NVL2(p.plan_time, p.plan_time || '(s)','') AS PLAN
FROM gv$sql_plan_monitor p
WHERE sid=&sid
and p.inst_id=&inst_id
ORDER BY p.plan_line_id, p.plan_parent_id;
select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALLSTATS LAST'));
PROMPT ================================
PROMPT Explain Plan from AWR
PROMPT ================================
select * from table(dbms_xplan.display_awr('&sql_id', NULL, null, 'ALLSTATS LAST'));
PROMPT ================================
PROMPT Sql Profiles
PROMPT ================================
set lines 1000 pages 9999
col name for a30
col task_exec_name for a16
col category for a10
col created for a30
col sql_text for a150
col signature for 9999999999999999999999999
select sql.sql_id,sql.child_number as child , prof.name, prof.category, prof.created,prof.task_exec_name,prof.FORCE_MATCHING,prof.status,prof.SIGNATURE
from
dba_sql_profiles prof,
gv$sql sql
where sql.sql_id in ('&sql_id')
-- and sql.child_number=child_number
-- and sql.force_matching_signature=prof.SIGNATURE
order by
created;
PROMPT ================
PROMPT Sql Baselines
PROMPT ================
col SQL_HANDLE for a30
col origin for a16
col last_modified for a30
col last_verified for a30
select sql_handle, plan_name, origin, created, last_modified, last_verified,ENABLED,ACCEPTED,FIXED,REPRODUCED
from dba_sql_plan_baselines
where signature in (select force_matching_signature
from gv$sql
where sql_id='&sql_id'
and inst_id=&inst_id);
undef sid
undef sql_id
undef inst_id
spool off;
"
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
Oracle RAC Administration and Monitoring Scripts -2
There are lots of options used with SRVCTL command, i mentioned them below.
-d Database Name
-i Instance Name
-s Service Name
-n Node Name
-r Preferred list
-a Available list
-p TAF (Transparent application failover policy)
-v Verbose
Status and Config Database
We can check all instance status of database as follows.
srvctl status database -d DB_NAME
[oracle@RAMdbrac01 ~]$ srvctl status database -d RAM
Instance RAM1 is running on node RAMdbrac01
Instance RAM2 is running on node RAMdbadm02
Instance RAM3 is running on node RAMdbadm03
Instance RAM4 is running on node RAMdbadm04
[oracle@RAMdbrac01 ~]$
We can display the configurations (instances name, nodes and oracle home) of any RAC Database as follows.
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
