19C RMAN CONCEPTS

rman

19c RMAN CONCEPTS:

RECOVER DATAFILE WITHOUT RMAN BACKUP
RECOVER THROUGH RESETLOGS
BLOCK CHANGE TRACKING
RECOVER LOSS OF ALL CONTROL FILE USING AUTO BACKUP
RECOVER LOSS OF ALL ONLINE REDO LOGS 
POINT IN TIME RECOVERY 
RMAN-06183: datafile or datafile copy xyz.dbf larger than MAXSETSIZE
RMAN Database Restore ASM
DATABASE INCARNATION USING RMAN
Restore Tablespace using RMAN
RESTORE SPFILE USING RMAN
RMAN backup Full Database
RMAN Backup Tablespace
RMAN Backup Particular Datafile
RMAN Backup Spfile
RMAN Backup Current Control file
RMAN Backup Archive log Until Sequence
RMAN Backup Archive log Between Sequence
RMAN Backup Archive log Between SCN
RMAN Backup Archive log Until SCN
RMAN Backup Database Plus Archive log
RMAN Backup Database Includes A Control file
RMAN Backup Archive log and All Delete Input
RMAN Backup Archive log All and Skip Inaccessible
LEVEL 0 and LEVEL 1 Backup And Recovery using RMAN
CROSSCHECK BACKUPS Using RMAN
RESTORE CONTROL FILE USING RMAN
Backup-based Cloning of a database using RMAN
RECOVERY CATALOG DATABASE IN RMAN
RMAN ORA ERRORS
DBVERIFY
END-OF-FILE ERROR
LEVEL 0 INCREMENTAL BACKUP
point-in-time recovery using RMAN in 19c
Recover a loss of all online redo log files

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

Enable DBA tasks auditing in Postgres

 

1. Create a specific database role called rds_pgaudit:
CREATE ROLE rds_pgaudit;
2. Modify the DB parameter group by setting the following parameters
pgaudit.role = rds_pgaudit
rds.restrict_password_commands=1
shared_preload_libraries=pg_stat_statements,pgaudit
pgaudit.log= role
3. Reboot the RDS instance.
Validate if the parameter group is updated otherwise reboot the RDS.
4. Confirm that pgaudit is initialized by running the following command:
show shared_preload_libraries;
show pgaudit.role;
show rds.restrict_password_commands;
show pgaudit.log;
5. Create the pgaudit extension by running the following command:
CREATE EXTENSION pgaudit;
\dx
6. Confirm that pgaudit.role is set to rds_pgaudit by running the following command:
show pgaudit.role;
7. Configure the pgaudit.log parameter to audit any of the following:
ROLE audits statements related to roles and privileges, such as GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
ALL audits the following commands.
MISC audits miscellaneous commands, such as DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
DDL audits all data description language (DDL) that is not included in the ROLE class.
ROLE audits statements related to roles and privileges, such as GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
FUNCTION audits function calls and DO blocks.
WRITE audits INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.
READ audits SELECT and COPY when the source is a relation or a query.

 

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 LINK FROM ORACLE TO MySQL

DATABASE LINK FROM ORACLE TO MySQL

1. Introduction

This document describes steps to create a database link between Oracle to MySQL (Heterogeneous Services) / Oracle Gateway, Regarding Oracle Documentation Gateways will integrate with any number of non-Oracle systems from an Oracle application.
  1. All bold statements are shell commands or configuration steps.

2. Database Link Configuration

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.
It is a connection between two physical database servers that allows a client to access them as one logical database.
2.1 Configure Database Link from ORACLE to MySQL
To start Configure Database Link we need to follow the below steps:
Step #1:-
Determine the Version for dg4odbc on the Operating system using
                              >file $ORACLE_HOME/bin/dg4odbc
This Command Explain which version of ODBC Driver.
Step #2:-
Install unixODBC and MySQL connector    (This step should be done as root user).
# yum install unixODBC
               # yum install mysql-connector-odbc
Step #4:-
(As Oracle User). Export Variables.
In Linux – .bash_profile
In Solaris -.profile
export LD_LIBRARY_PATH=/usr/lib64:/usr/lib64/libmyodbc5.so:$ORACLE_HOME/lib
export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
Step #5:-
 (As Root User).
Configure odbc.ini located in (/etc), the file should look like the below:-
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc5.so
Setup           = /usr/lib64/libodbcmyS.so
# Driver64        = /usr/lib64/libmyodbc5.so
# Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1
DATABASE = cs_dm_stage
UID = <username>
PWD = welcome123!@#
Option = 3
SERVER = nyads2hslrdb02.na.weightwatchers.net
PORT = 3306
It’s configured.
Step #6:-
As Oracle User:-
[root@server etc]# cd /usr/lib64
[root@server lib64]# ldd libmyodbc5.so
        linux-vdso.so.1 =>  (0x00007fff1afff000)
        libltdl.so.7 => /usr/lib64/libltdl.so.7 (0x00007f4b16c01000)
        libmysqlclient_r.so.16 => /usr/lib64/mysql/libmysqlclient_r.so.16 (0x00007f4b1686c000)
        libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f4b16635000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f4b1641c000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f4b16197000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f4b15f7a000)
        libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007f4b15d0d000)
        libcrypto.so.10 => /usr/lib64/libcrypto.so.10 (0x00007f4b15928000)
        libz.so.1 => /lib64/libz.so.1 (0x00007f4b15712000)
        libodbcinst.so.2 => /usr/lib64/libodbcinst.so.2 (0x00007f4b15501000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f4b1516c000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f4b14f68000)
        libfreebl3.so => /usr/lib64/libfreebl3.so (0x00007f4b14d65000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003f64c00000)
        libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f4b14b20000)
        libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f4b14839000)
        libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f4b14635000)
        libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f4b14408000)
        libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f4b141fd000)
        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f4b13ff9000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f4b13ddf000)
        libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f4b13bc0000)
 
The Most Important Thing in the above output is to check if there is nothing in the library “Not Found”.
Step #7:-
(As Oracle User).
Test connection using odbc only, Only on OS Level
# ./isql –v MySQL
The below is output:-
Connected
Until this step we didn’t do anything only configure odbc, next step will explain how to configure Oracle.
Step #8:-
Configuration for Oracle Start with initSID.ora file. Oracle already create a template located in $ORACLE_HOME/hs/admin just copy it with the new name, the default one is initdg4odbc.ora we can leave without change and edit it.
Edit the file and set the new variables like the below:-
HS_FDS_CONNECT_INFO = MySQL
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = /home/oracle/odbc_trace.txt
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so
#HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
#HS_NLS_NCHAR = AMERICAN_AMERICA.WE8ISO8859P1
set ODBCINI= /etc/odbc.ini
HS_FDS_SUPPORT_STATISTICS= FALSE
HS_FDS_SQLLEN_INTERPRETATION = 64
HS_RPC_FETCH_REBLOCKING= OFF
HS_FDS_FETCH_ROWS = 1
Step #9:-
          This Step will describe how to configure Listener.ora, You can download Listener.ora and copy/Paste the entire to make sure there’s nothing wrong or you can create your own. Open Listener.ora and add the below line depending on your Configuration:-
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ENVS = LD_LIBRARY_PATH=/usr/lib64:/usr/lib64/libmyodbc5.so:/u01/app/oracle/product/11.2.0.4/db_1/lib)
      (SID_NAME = dg4odbc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (PROGRAM = dg4odbc)
    )
    )
Some Notes about the above:-
1-SID_LIST_LISTENER à Indicate to your LISTENER NAME.
2- SID_NAME= Depend init….ora file.
3- Change ORACLE_HOME.
4- LD_LIBRARY_PATH Depend on where you installed unixODBC and MySQL connecter.
5- Program Indicate to init…ora name.
Now After editing Listener.ora Save it, this step requires restarting Listener.ora
> Reload LISTENER
lsnrctl reload
Step #10:-
Finally, add new entire to tnsnames.ora
dg4odbc  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=bosporadb01)(PORT=1622))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
  )
tnsping dg4odbc should work.
Step #11:-
This step will create a database link for MySQL.
          SQL > create public database link mysql_dblink connect to “csmigration” identified by ” welcome123!@#” using ‘dg4odbc’;
Now Test it:-
 SQL > Select * from “cs_memberdata_staging”@ mysql_dblink;
We have done the setup.
MySQL Server Details:
               Server Name: <server-name/host name>
Database: cs_dm_stage
Username: <username>
Password: <password>

 

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

𝗟𝗼𝗮𝗱 𝗕𝗮𝗹𝗮𝗻𝗰𝗲𝗿 𝘃𝘀 𝗥𝗲𝘃𝗲𝗿𝘀𝗲 𝗣𝗿𝗼𝘅𝘆 𝘃𝘀 𝗔𝗣𝗜 𝗚𝗮𝘁𝗲𝘄𝗮𝘆

 

What are the differences between a load balancer, a reverse proxy, and an API gateway?

All three are used to optimize and manage web traffic. However, they vary in their function and use cases:

A 𝗹𝗼𝗮𝗱 𝗯𝗮𝗹𝗮𝗻𝗰𝗲𝗿 is a device that distributes incoming network traffic across multiple servers. The goal is to ensure that no single server is overwhelmed with traffic, which can lead to slow response times or even downtime. Load balancers are ideal for high-traffic websites or applications that need to handle a large volume of requests.

A 𝗿𝗲𝘃𝗲𝗿𝘀𝗲 𝗽𝗿𝗼𝘅𝘆, on the other hand, is a server that sits between the client and the web server. The reverse proxy intercepts requests from clients and forwards them to the appropriate server. The reverse proxy can also cache frequently requested content, which can help improve performance and reduce server load. Reverse proxies are ideal for websites or applications that need to handle a large number of concurrent connections.

An 𝗔𝗣𝗜 𝗴𝗮𝘁𝗲𝘄𝗮𝘆 is a server that acts as an intermediary between clients and backend servers. The API gateway is responsible for managing API requests, enforcing security policies, and handling authentication and authorization. API gateways are ideal for microservices architectures, where multiple services need to be accessed through a single API.

 

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

Performance Tuning in Oracle

oracle performace check and health check during emergency situation for oarcle dba

The process of analyzing and reducing processing time is called Performance Tuning…

SQL Tuning

The iterative process of improving the performance of SQL statements to measurable and achievable goals.

Scope of Tuning

 1) Analyze and create the index
2) Rewrite SQL
3) Using Hints
4) All improvements have been done from SQL and its related Objects
5) Tuning the Server, CPU, RAM, and Network
6) Tuning the instance memory parameters
7) Optimizer setting to improve performance

Where to start with Performance Tuning

 1) Manual inspection of code ( Number of lines of code is small)
2) Execute the code with multiple log statements and analyze the statements that take more time
3) Tune the SQL code and PLSQL code separately

How to optimize code

 1) Implement the logic in SQL ( instead of PLSQL ) as far as possible
2) Use Analytical Functions as much as possible
3) Use Global Temporary Tables
4) Use Insert append hints
5) Use pass-by-reference ( NO COPY Compiler hint)
6) Consolidate the redundant code
7) Use LOB variables only if needed (Use Extended Data types from 12C)

 

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

Advantages of PL/SQL

 

1) Tight integrated with SQL
PL/SQL lets you use all SQL data manipulation, cursor control, and transaction control statements, and all SQL functions, operators, and pseudo columns…
PL/SQL fully supports SQL data types – You need not convert between PL/SQL and SQL data types.
You can give a PL/SQL data item the data type of a column or row of a database table without explicitly specifying that data type
using %TYPE Attribute and %ROWTYPE Attribute.
PL/SQL supports both static and dynamic SQL. Static SQL is SQL whose full text is known at compile time.
Dynamic SQL is SQL whose full text is not known until run time. Dynamic SQL lets you make your applications more flexible and versatile.
2) High Performance
PL/SQL lets you send a block of statements to the database, significantly reducing traffic between the application and the database.
Bind Variables
When you embed a SQL INSERT, UPDATE, DELETE, MERGE, or SELECT statement directly in your PL/SQL code,
the PL/SQL compiler turns the variables in the WHERE and VALUES clauses into bind variables.
Oracle Database can reuse these SQL statements each time the same code runs, which improves performance.
PL/SQL does not create bind variables automatically when you use dynamic SQL, but you can use them with dynamic SQL by specifying them explicitly.
Optimizer
The PL/SQL compiler has an optimizer that can rearrange code for better performance.
Subprograms
PL/SQL subprograms are stored in executable form, which can be invoked repeatedly. Because stored subprograms run in the database server,
a single invocation over the network can start a large job. This division of work reduces network traffic and improves response times.
Stored subprograms are cached and shared among users, which lowers memory requirements and invocation overhead.
3) High Productivity
PL/SQL has many features that save designing and debugging time, and it is the same in all environments.
PL/SQL lets you write compact code for manipulating data.
PL/SQL can query, transform, and update data in a database.
4) Portability
You can run PL/SQL applications on any operating system and platform where Oracle Database runs.
5) Scalability
PL/SQL stored subprograms increases scalability by centralizing application processing on the database server.
The shared memory facilities of the shared server let Oracle Database support thousands of concurrent users on a single node.
6) Manageability
PL/SQL stored subprograms increase manageability because you can maintain only one copy of a subprogram, on the database server,
rather than one copy on each client system.
Any number of applications can use the subprograms, and you can change the subprograms without affecting the applications that invoke them.

 

 

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

Diff between decode and case statement

Decode:

1) Decode is an oracle function.
2) Decode function can not be used as a parameter to a procedure or function.
3) Decode function internally uses only the equality operator.
4) Decode function is only used in SQL statements.
5) Decode function does not expect datatype consistency.
6) Decode function performance is low.

Case Statement:

1) Case statement is ANSI standard product.
2) Case statements can be used as a parameter to a procedure or function.
3) Case statement works with all SQL operators.
4) Case statements can be used in the PLSQL construct also.
5) Case statement expects datatype consistency.
6) Case statement performance is high.

 

 

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

Difference between row level trigger and statement level trigger

Row level trigger:

1) For each row clause used
2) Allow using when clause in row level trigger
3) Row level trigger has: new: old qualifiers
4) In row-level trigger, the trigger body is executed for each and every row for a DML statement
5) Performance low as compared to statement level trigger

Statement level trigger:

1) For each row clause not used
2) Not allowed using when clause in statement level trigger
3) Statement level trigger does not have: new: old qualifiers
4) in statement level trigger, the trigger body is executed only once per DML statement
5) Performance is high as compared to row-level trigger

 

 

Please find out all of our articles send us the Invitation or Following 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

 

Difference between Procedure and Function

Procedure:

1)Used mainly to execute a certain process
2)Cannot call in SELECT statement
3)Use the OUT parameter to return the value
4)It is not mandatory to return the value
5)RETURN will simply exit the control from the subprogram.
6)Return datatype will not be specified at the time of the creation

Function:

1)Used mainly to perform some calculation
2)A Function that contains no DML statements can be called in the SELECT statement
3)Use RETURN to return the value
4)It is mandatory to return the value
5)RETURN will exit the control from the subprogram and also returns the value
6)Return datatype is mandatory at the time of the creation

 

Please find out all of our articles send us the Invitation or Following 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

Sql_profiles vs Sql_baselines vs Sql_patches

 

oracle performace check and health check during emergency situation for oarcle dba

In Oracle, a SQL Profile creates extra information about a particular SQL that the optimizer can use at run time to select the optimal plan to ensure the best performance. In essence, the SQL Profile enables dynamic behavior where the optimizer has multiple plans to choose from at run time based on run time bind variables, etc. When you run the SQL Tuning Advisor for the list of recommendations you will see that the recommendation specifies whether a SQL can be improved by creating a SQL Profile or SQL Baseline. It is preferable to choose a SQL Profile simply because it allows the optimizer to pick the best execution plans at run time.

SQL Baseline on the other hand is more of a brute force method when you simply marry a particular SQL to stay with a specific SQL execution plan. So no matter what the run time bind variables are for a given SQL, the optimizer will always try to use the SQL Baseline plan. This may work fine for most cases but in instances, where data skew, is high it is preferable that one pick more efficient plans based on bind variable values passed at run time instead of always picking the same plan as generated by the SQL Baseline.

In any case knowledge of the application, and data may make you prefer one over the other, in case you are clueless about this, then I would suggest you stick with the SQL Profile.

A SQL patch is a SQL manageability object that can be generated by the SQL Repair Advisor, in order to circumvent a plan which causes a failure.  In essence, a SQL patch tells the optimizer to change the plan in some way or avoid an optimization so that the failure does not occur.  For instance, if a failure happens during index access, then generating a plan without that index (or without any index) may work around the failure

Oracle provides three mechanisms to deal with execution plans: SQL Profiles, SQL Baselines, and SQL Patches. Profiles are proposed by the Tuning Advisor and it’s mostly based on adapting the cardinalities to match reality. Baselines allow us to provide a list of the accepted execution plans for a statement.SQL Patches are part of the SQL Repair Advisor and add hints to a specific statement.

 

Please find out all of our articles send us the Invitation or Following 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 allocation space datafile wise

How to query for current allocation, and utilization of data files in Oracle DB:

 

SELECT SUBSTR (df.NAME, 1, 60) file_name, df.bytes / 1024 / 1024 "Allocated Size(MB)",
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) "Used Size (MB)",
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) "Free Size(MB)"
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_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