My Youtube Channel

Please Subscribe

Flag of Nepal

Built in OpenGL

Word Cloud in Python

With masked image

Thursday, May 29, 2025

List of important Data Dictionary queries in Oracle 19c

 Here's a comprehensive list of important Data Dictionary queries in Oracle 19c and the useful information you can extract from each. These are helpful for DBAs and developers when managing, troubleshooting, or analyzing an Oracle database.


🔷 1. USER_TABLES

Query:

SELECT * FROM USER_TABLES;

Use: Lists all tables owned by the current user.
Info you get:

  • Table name

  • Tablespace name

  • Row count estimate

  • Last analyzed date

  • Compression info


🔷 2. ALL_TABLES / DBA_TABLES

Query:

SELECT * FROM ALL_TABLES;       -- Tables accessible to user  
SELECT * FROM DBA_TABLES;       -- All tables in the DB (DBA only)

Use: Get metadata about all tables in the schema or entire database.


🔷 3. USER_TAB_COLUMNS

Query:

SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'EMPLOYEES';

Use: List all columns and data types of a specific table.
Info:

  • Column names

  • Data types and lengths

  • Null constraints

  • Default values


🔷 4. ALL_CONSTRAINTS / USER_CONSTRAINTS

Query:

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMPLOYEES';

Use: Get all constraints (PK, FK, Unique, Check) on a table.
Info:

  • Constraint type

  • Status (enabled/disabled)

  • Related table (for FKs)


🔷 5. USER_CONS_COLUMNS

Query:

SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 'EMPLOYEES';

Use: Shows columns involved in constraints.


🔷 6. USER_INDEXES / USER_IND_COLUMNS

Query:

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'EMPLOYEES';
SELECT * FROM USER_IND_COLUMNS WHERE INDEX_NAME = 'EMP_NAME_IDX';

Use: List indexes on a table and the columns used in them.


🔷 7. USER_SEQUENCES

Query:

SELECT * FROM USER_SEQUENCES;

Use: Lists all sequences (used for generating unique values).


🔷 8. USER_VIEWS / DBA_VIEWS

Query:

SELECT VIEW_NAME, TEXT FROM USER_VIEWS;

Use: Get view definitions and list of views in your schema.


🔷 9. DBA_TAB_PRIVS / DBA_COL_PRIVS

Query:

SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'HR';

Use: Find object privileges granted to or by a user.


🔷 10. ROLE_TAB_PRIVS

Query:

SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'DBA';

Use: See privileges granted through a role.


🔷 11. DBA_USERS

Query:

SELECT * FROM DBA_USERS;

Use: List all users, their account status, lock status, default tablespaces.


🔷 12. DBA_SYS_PRIVS / DBA_ROLE_PRIVS

Query:

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'HR';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'HR';

Use: Shows system privileges and roles granted to users.


🔷 13. V$SESSION

Query:

SELECT SID, SERIAL#, USERNAME, STATUS, OSUSER, MACHINE FROM V$SESSION;

Use: Check current sessions, active users, and their client machine info.


🔷 14. V$PROCESS

Query:

SELECT * FROM V$PROCESS;

Use: View background and user processes connected to Oracle.


🔷 15. DBA_DATA_FILES

Query:

SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 AS SIZE_MB FROM DBA_DATA_FILES;

Use: Get details about data files in tablespaces.


🔷 16. DBA_TABLESPACES

Query:

SELECT * FROM DBA_TABLESPACES;

Use: List of all tablespaces, status, type (permanent/temp/undo).


🔷 17. DBA_FREE_SPACE

Query:

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS FREE_MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

Use: Shows free space in each tablespace.


🔷 18. DBA_EXTENTS

Query:

SELECT * FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'EMPLOYEES';

Use: Details of extents allocated to objects (storage usage).


🔷 19. V$SGA / V$SGAINFO / V$PGA_TARGET_ADVICE

Use: Memory usage and tuning information.
Examples:

SELECT * FROM V$SGAINFO;
SELECT * FROM V$PGA_TARGET_ADVICE;

🔷 20. DBA_HIST_SQLSTAT / V$SQL

Use: Get SQL performance history and currently executing queries.


Bonus: Data Dictionary Structure Tables

  • DICT / DICTIONARY – list of all data dictionary views.

SELECT * FROM DICTIONARY WHERE TABLE_NAME LIKE '%USER%';


ERD of Oracle HR Schema

 


Process to import SAKILA Schema in Oracle database

 #####Create a directory for sakila files

mkdir /u01/app/oracle/sakila

cd /u01/app/oracle/sakila

------------------------------------------------------------------------------------------------------------------

#####Download the schema file

wget https://raw.githubusercontent.com/DataGrip/dumps/master/oracle-sakila-db/oracle-sakila-schema.sql


#####Download the data file

wget https://raw.githubusercontent.com/DataGrip/dumps/master/oracle-sakila-db/oracle-sakila-insert-data.sql


#####verify downloaded files

ls -la /home/oracle/sakila/


-------------------------------


OR,

download the file named (oracle-sakila-schema.sql and oracle-sakila-insert-data.sql) from the link below:

https://github.com/DataGrip/dumps/tree/master/oracle-sakila-db


and move the file to location /u01/app/oracle/sakila


---------------------------------------------------------------------------------------------------------------------


#####Connect to Oracle as SYS


sqlplus sys as sysdba 


#####Create Sakila User and Tablespace


sql-- Create tablespace for Sakila (using ASM)


CREATE TABLESPACE sakila_tbs

DATAFILE '+DATA' SIZE 200M

AUTOEXTEND ON NEXT 20M MAXSIZE 2G;


#####Find default temporary tablespace

SELECT property_value 

FROM database_properties 

WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';


-- Create the sakila user

CREATE USER sakila IDENTIFIED BY sakila

DEFAULT TABLESPACE sakila_data

TEMPORARY TABLESPACE temp_new;


-- Grant necessary privileges

GRANT CONNECT, RESOURCE TO sakila;

GRANT CREATE VIEW TO sakila;

GRANT UNLIMITED TABLESPACE TO sakila;


-- Exit from SYS

EXIT;

------------------------------------------------------------------------------------------------------------------------------------

#####Connect as Sakila User


sqlplus sakila/sakila


#####Run the Schema Creation Script


IN SQL PROMPT-- Set the directory path where your files are located


@/u01/app/oracle/sakila/oracle-sakila-schema.sql


Wait for this to complete - you'll see tables, indexes, and constraints being created.


#####Run the Data Insertion Script


IN SQL PROMPT-- Insert the sample data


@/u01/app/oracle/sakila/oracle-sakila-insert-data.sql


This will take a few minutes - you'll see INSERT statements executing.


--------------------------------------------------------------------------------------------------------------------------------------

#####Verify the Installation


-- Check if all tables were created

SELECT table_name FROM user_tables ORDER BY table_name;


-- Check row counts for major tables

SELECT 'ACTOR' as table_name, COUNT(*) as row_count FROM actor

UNION ALL

SELECT 'FILM', COUNT(*) FROM film

UNION ALL

SELECT 'CUSTOMER', COUNT(*) FROM customer

UNION ALL

SELECT 'RENTAL', COUNT(*) FROM rental;


------------------------------------------------------------------------------------------------------------------------------------------


# Export the sakila schema to dump file

expdp schemas=SAKILA DIRECTORY=DUMPDIR DUMPFILE=sakila_20250529_%U.dmp LOGFILE=sakila_20250529.log compression=ALL;


# Import dump file of sakila schema

impdp schemas=SAKILA remap_schema=SAKILA:VIEWERS remap_tablespace=SAKILA_TBS:TBS_VIEWERS directory=DUMPDIR dumpfile=sakila_20250529_01.dmp logfile=SAKILA_import_20250527.log;



Monday, May 26, 2025

Explanation of Parameters of Control file used while creation in Oracle Database

 CREATE CONTROLFILE REUSE DATABASE "ORCLTRN" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 (

    '+DATADG/ORCLTRN/ONLINELOG/group_1.262.1201513375',

    '+RECODG/ORCLTRN/ONLINELOG/group_1.257.1201513377'

  ) SIZE 200M BLOCKSIZE 512,

  GROUP 2 (

    '+DATADG/ORCLTRN/ONLINELOG/group_2.263.1201513375',

    '+RECODG/ORCLTRN/ONLINELOG/group_2.258.1201513379'

  ) SIZE 200M BLOCKSIZE 512,

  GROUP 3 (

    '+DATADG/ORCLTRN/ONLINELOG/group_3.264.1201513379',

    '+RECODG/ORCLTRN/ONLINELOG/group_3.259.1201513381'

  ) SIZE 200M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '+DATADG/ORCLTRN/DATAFILE/system.257.1201512975',

  '+DATADG/ORCLTRN/DATAFILE/hr2.268.1201691573',

  '+DATADG/ORCLTRN/DATAFILE/sysaux.258.1201513239',

  '+DATADG/ORCLTRN/DATAFILE/tbs_training.270.1201772629',

  '+DATADG/ORCLTRN/DATAFILE/users.267.1201689687',

  '+DATADG/ORCLTRN/DATAFILE/users.260.1201513275',

  '+DATADG/ORCLTRN/DATAFILE/undotbs2.269.1201693891',

  '+DATADG/ORCLTRN/DATAFILE/tbs_auditors.271.1201779319',

  '+DATADG/ORCLTRN/DATAFILE/tbs_viewers.272.1201779353'

CHARACTER SET AL32UTF8

;


Explanation:

It used to recreate the control file for a database named ORCLTRN. This is typically done during database recovery, duplication, or migration. Let's break down and explain each part:


🔧 CREATE CONTROLFILE REUSE DATABASE "ORCLTRN" RESETLOGS ARCHIVELOG

  • CREATE CONTROLFILE: Begins the process of creating a new control file, which is critical for Oracle to keep track of database structure.

  • REUSE: Tells Oracle to reuse existing control files if they exist—avoiding errors due to file name conflicts.

  • DATABASE "ORCLTRN": Specifies the database name.

  • RESETLOGS: Resets the redo log sequence numbers. Used after incomplete recovery or restoring a backup.

  • ARCHIVELOG: Puts the database into ARCHIVELOG mode, meaning redo logs will be archived for recovery purposes.


🔢 Control File Parameters

  • MAXLOGFILES 16: Maximum number of redo log groups.

  • MAXLOGMEMBERS 3: Max members (multiplexed copies) per redo log group.

  • MAXDATAFILES 100: Max number of database datafiles.

  • MAXINSTANCES 8: Max RAC (Real Application Clusters) instances.

  • MAXLOGHISTORY 292: Max number of archived log entries Oracle can track.


🔁 LOGFILE Section

Defines the redo log groups and their multiplexed members:

GROUP 1 (
  '+DATADG/ORCLTRN/ONLINELOG/group_1.262.1201513375',
  '+RECODG/ORCLTRN/ONLINELOG/group_1.257.1201513377'
) SIZE 200M BLOCKSIZE 512,

Each GROUP:

  • Has 2 members for redundancy.

  • Located on different ASM disk groups: +DATADG and +RECODG.

  • SIZE 200M: Size of each log file.

  • BLOCKSIZE 512: Block size (default for redo logs).

There are 3 such redo log groups (Group 1, 2, and 3).


🗃 DATAFILE Section

Lists all datafiles in the database. These files are located on the +DATADG ASM disk group.

Examples:

  • system, sysaux, undotbs2 → core system files

  • users → default tablespace for users

  • tbs_training, tbs_auditors, tbs_viewers, hr2 → user-defined tablespaces


📘 CHARACTER SET AL32UTF8

Defines the database character set, AL32UTF8, which is Unicode-compliant and recommended for international applications.


🚫 Missing or Commented Out

-- STANDBY LOGFILE

This line is commented out. It indicates that standby log files (used for Data Guard configuration) are not included in this control file creation.


🧠 Summary

This command:

  • Recreates the control file for the Oracle database ORCLTRN.

  • Uses existing redo log and datafile paths.

  • Enables ARCHIVELOG mode.

  • Uses multiplexed redo logs on separate disk groups.

  • Is likely run after restoring or migrating a database and requires RESETLOGS.

⚠️ Caution: Creating control files is a critical operation. Running this incorrectly or without proper backup can lead to data loss. Always verify file paths, database state, and intended recovery scenario.