My notes on Oracle Database Administration -- Moid

MoidIITDocID-652: How to install 19c client on Windows 11? --Moid Muhammad

2025-11-19T16:28:00.001-06:00

--Moid

MoidIITDocID-651: Check for resource limits between two times? --Moid Muhammad

2025-10-13T11:05:00.003-05:00

SELECT * FROM dba_hist_resource_limit WHERE snap_id IN
(
SELECT snap_id FROM dba_hist_snapshot
WHERE begin_interval_time BETWEEN
TO_DATE('2025-10-13 07:45:00', 'YYYY-MM-DD HH24:MI:SS')
AND
TO_DATE('2025-10-13 08:15:00', 'YYYY-MM-DD HH24:MI:SS')
);

MoidIITDocID-650: How to force a snapshot to get flused for AWR report? --Moid

2025-10-13T10:55:00.006-05:00

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

set linesize 200 pages 200
col BEGIN_INTERVAL_TIME format a35
col END_INTERVAL_TIME format a35

select snap_id instance_number, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
from dba_hist_snapshot
order by instance_number, BEGIN_INTERVAL_TIME;

Click here for the document.

Moid Muhammad

MoidIITDocID-649: How to collect stats in Oracle?

2025-08-23T17:05:00.006-05:00

Few examples:

exec dbms_stats.SET_GLOBAL_PREFS('AUTOSTATS_TARGET', 'AUTO');

exec dbms_stats.SET_DATABASE_PREFS('STALE_PERCENT', '15');

exec dbms_stats.GATHER_DATABASE_STATS(degree => 4, cascade => TRUE);

exec dbms_stats.GATHER_SCHEMA_STATS(ownname => 'SCOTT', degree => 4, cascade => TRUE);

exec dbms_stats.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'EMP', degree => 4, cascade => TRUE);

Click here for the link.

Moid

MoidIITDocID-648: Data Guard in Pictures --Moid Muhammad

2025-08-02T10:07:00.004-05:00

Click here for the document.

Moid Muhammad

MoidIITDocID-647: Data Guard in Images

2025-07-20T12:41:00.001-05:00

Click here for the document.

Moid Muhammad

MoidIITDocID-646: How to create 19c database using “create database” command? --Moid

2025-02-01T11:11:00.003-06:00

Click here for the 19c document.

Moid

Dynamic

2024-11-12T13:18:00.013-06:00

select (select 'Space_GIVEN_to_DB' from dual) as Note,

round(sum(d.bytes)/1024/1024/1024 + (sum(t.bytes)/1024/1024/1024),2) as Size_in_GB

from

v$datafile d,

v$tempfile t

where

d.file#=t.file#(+)

group by 1

union

select

(select 'Space_USED_by_DB' from dual) as Note,

round(sum(size_in_bytes)/1024/1024/1024,2) Size_in_GB

from

(select

tablespace_name,

sum(bytes) size_in_bytes

from

dba_segments

group by

tablespace_name)

group by 1

order by 2 desc;

MoidIITDocID-640: Steps to get Oracle Certified (SQL Fundamentals, OCA / OCP)? --Moid Muhammad

2024-11-12T11:19:00.001-06:00

As of 11/12/2024

Oracle Database SQLExam Number: 1Z0-071

Oracle Database Administration I Exam Number: 1Z0-082
Oracle Database Administration II Exam Number: 1Z0-083

Certification fee for each of the three (Oracle SQL Fundamentals, OCA and OCP) is $245. Only 1 attempt for exam.

Format: Multiple Choice

Duration: 120 minutes

Number of Questions: 63 or 72

Passing Score: 63%

shop.oracle.com --> sign-in --> Browse Products --> Oracle University --> Exam Subscriptions --> Oracle Technology Exam Subscription --> Add to cart --> $245

Good site to practice: https://www.examtopics.com/exams/oracle/ and then select the following:

1z0-071: Oracle Datbase 12c SQL

1z0-082: Oracle Database Administration I

1z0-083: Oracle Database Administration II

Document is here.

Moid Muhammad

MoidIITDocID-634: How to start GUI tools on Linux when you sudo to other user? --Moid Muhammad

2024-08-23T22:27:00.007-05:00

Click here for the document.

Moid Muhammad

kewords: GUI, xclock, xming, dbca

MoidIITDocID-633: How to check the patch level of an oracle database? --Moid

2024-08-05T00:01:00.005-05:00

Click here for the document.

Moid Muhammad

MoidIITDocID-632: How to use tmux (instead of screen) in Linux? --Arshia Jabeen

2024-07-07T10:33:00.005-05:00

tmux list-sessions

tmux new-session -s MMpatching

tmux attach-session -t MMpatching

Click here for the full document

Thanks,

Arshia Jabeen

MoidIITDocID-629: How To Get Table Growth History Information? --Moid Muhammad

2023-12-15T11:13:00.005-06:00

Click here for the document.

Moid

table size segment size

MoidIITDocID-628: How to upgrade 12c database to 19c using Autoupgrade? --Arshia Jabeen

2023-09-27T16:08:00.002-05:00

Click here for the link.

by Arshia Jabeen.

Keywords: Moid oracle 19c autoupgrade rac data guard ASM

MoidIITDocID-627: How to upgrade 12c standalone database to 19c using DBUA? --Arshia Jabeen

2023-09-27T15:56:00.003-05:00

Click here for the document.

Arshia Jabeen

Keywords: Moid oracle 19c upgrade Arshia
migration export import data pump tesla MyTravelsPro.com MoidIIT.com

MoidIITDocID-625: How to load Scott Schema in Oracle database? --Arshia Jabeen

2023-09-16T10:19:00.002-05:00

Click here for the document,

Thanks,
Arshia Jabeen.

Keywords: create scott schema, run script

MoidIITDocID-624: How to start xclock (GUI tools) after switching user to oracle in Linux? --Moid Muhammad

2023-08-27T15:22:00.002-05:00

Click here for the document.

Moid

MoidIITDocID-623: Shell script that loop through tnsnames.ora to execute SQL script in multiple databases --Moid Muhammad

2023-08-23T17:11:00.005-05:00

Click here for the document.

Moid Muhammad

Keywords: .sql run SQL Script multiple databases servers parameter change across

MoidIITDocID-622: How to schedule sql script on Linux DB server? --Moid Muhammad

2023-08-23T14:10:00.007-05:00

Click here for the link.

Moid Muhammad

Keywords:
crontab execute schedule oracle sqlplus

MoidIITDocID-620: How to register database service to non-default LISTENER port? --Moid

2023-08-14T13:27:00.004-05:00

Click here for the document

Moid

Keywords: Oracle Listener on 1733 non-default port Register multiple ports on different listeners.

MoidIITDocID-619: How to register multiple ports to oracle LISTENER? --Moid

2023-08-14T12:48:00.005-05:00

Click here for the document.

Moid

Keywords: Oracle Listener 1733 non-default port multiple ports on different listeners.

MoidIITDocID-618: How to move oracle tables to a different tablespace? --Moid

2023-06-20T16:55:00.000-05:00

Click here for the document.

Moid

MoidIITDocID-617: Query to find the non-oracle user maintained tables in SYSTEM tablespace

2023-06-20T16:38:00.003-05:00

Click here for the document.

Moid

MoidIITDocID-616: How to check if TDE or TSE is enabled in Oracle database? --Moid

2023-03-06T07:22:00.000-06:00

Click here for the document.

Moid

MoidIITDocID-615: Create trigger to capture failed database login attempts --Moid

2023-03-01T10:57:00.000-06:00

Click here for the document.

Moid