oracle 19c - ORACLEAGENT BLOG

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

DATABASE INFORMATION SCRIPTS

1)Get ACL details in the database.

2)How to Find the optimal undo retention size in the database.

3)Find who locked your account.

4)Find active transactions in db.

5)Archive generation per hour.

6)Find duplicate rows in the table.

7)Database growth per month.

8)Generate resize datafile script.

9)Get database uptime.

10)Scn to Timestamp and Timestamp to Scn.

11)Get Database incarnation information in the database.

12)How to get CPU memory info in the server.

13)Get redo log member info.

14)Get DDL of all tablespaces.

15)How to purge AWR old snapshot details in database.

16)Generate Bulk AWR reports in the database.

17)How to find current redo log size in database.

18)View hidden parameters.

19)Get database link details in the database.

20)How to Modify window_size/ window baseline size.

21)Get DDL of privileges granted to user.

22)Get timezone information in the database.

23)How to View / Modify AWR retention period.

24)Check the Character set info of the database.

25)Check Installed RDBMS components.

26)How to Get Alert log location in db.

27)Get installed SQL patches in the database.

28)GET INDEX USAGE.

29)Get sql query to html format.

30)Row count of all the tables of a schema.

31)Disable or enable all triggers of schema.

32)Get the size of the database.

33)How to clean up orphaned data pump jobs.

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:

Get ACL details in database

set lines 200
COL ACL_OWNER FOR A12
COL ACL FOR A67
COL HOST FOR A34
col PRINCIPAL for a20
col PRIVILEGE for a13
select ACL_OWNER,ACL,HOST,LOWER_PORT,UPPER_PORT FROM dba_network_acls;
select ACL_OWNER,ACL,PRINCIPAL,PRIVILEGE from dba_network_acl_privileges;

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

How to Find the optimal undo retention size in database

SELECT d.undo_size / (1024 * 1024) “ACTUAL UNDO SIZE [MByte]”,
SUBSTR(e.value, 1, 25) “UNDO RETENTION [Sec]”,
(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) /
(1024 * 1024) “NEEDED UNDO SIZE [MByte]”
FROM (SELECT SUM(a.bytes) undo_size
FROM gv$datafile a, gv$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,
gv$parameter e,
gv$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’;

Find who locked your account

— Return code 1017 ( INVALID LOGIN ATTEMPT)
— Return code 28000 ( ACCOUNT LOCKED)
set pagesize 1299
set lines 299
col username for a15
col userhost for a13
col timestamp for a39
col terminal for a23
SELECT username,userhost,terminal,timestamp,returncode
FROM dba_audit_session
WHERE username=’&USER_NAME’ and returncode in (1017,28000);

Find active transactions in db

col name format a10
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti ‘Active transactions’

select s.sid,username,t.start_time, r.name, t.used_ublk “USED BLKS”,
decode(t.space, ‘YES’, ‘SPACE TX’,
decode(t.recursive, ‘YES’, ‘RECURSIVE TX’,
decode(t.noundo, ‘YES’, ‘NO UNDO TX’, t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/

Archive generation per hour

set lines 299
SELECT TO_CHAR(TRUNC(FIRST_TIME),’Mon DD’) “DG Date”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’00’,1,0)),’9999′) “12AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’01’,1,0)),’9999′) “01AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’02’,1,0)),’9999′) “02AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’03’,1,0)),’9999′) “03AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’04’,1,0)),’9999′) “04AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’05’,1,0)),’9999′) “05AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’06’,1,0)),’9999′) “06AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’07’,1,0)),’9999′) “07AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’08’,1,0)),’9999′) “08AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’09’,1,0)),’9999′) “09AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’10’,1,0)),’9999′) “10AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’11’,1,0)),’9999′) “11AM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’12’,1,0)),’9999′) “12PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’13’,1,0)),’9999′) “1PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’14’,1,0)),’9999′) “2PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’15’,1,0)),’9999′) “3PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’16’,1,0)),’9999′) “4PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’17’,1,0)),’9999′) “5PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’18’,1,0)),’9999′) “6PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’19’,1,0)),’9999′) “7PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’20’,1,0)),’9999′) “8PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’21’,1,0)),’9999′) “9PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’22’,1,0)),’9999′) “10PM”,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,’HH24′),’23’,1,0)),’9999′) “11PM”
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/

Archive generation per hour

Find duplicate rows in table

— Reference metalink id – 332494.1
— Save as duplicate.sql and run as @duplicate.sql
REM This is an example SQL*Plus Script to detect duplicate rows from
REM a table.
REM
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
accept t prompt ‘Table: ‘
prompt
select ‘Table ‘||upper(‘&&t’) from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be unique. If more than
prompt one column is specified, you MUST separate with commas.
prompt
accept c prompt ‘Column(s): ‘
prompt
select &&c from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/

Database growth per month

select to_char(creation_time, ‘MM-RRRR’) “Month”, sum(bytes)/1024/1024/1024 “Growth in GB
from sys.v_$datafile
where to_char(creation_time,’RRRR’)=’&YEAR_IN_YYYY_FORMAT’
group by to_char(creation_time, ‘MM-RRRR’)
order by to_char(creation_time, ‘MM-RRRR’);

Tag Archives: oracle 19c