How to Parse the Filename from the Whole Path in Oracle

-

Parsing the Filename from the Whole Path

Let’s look at a real example from the life of a DBA. Suppose you want to extract only the filename from dba_data_files without the path name; you could use the following SQL.

Here the INSTR function is nested inside a SUBSTR function. Single-row functions can be nested to any level. When functions are nested, the innermost function is evaluated first.

The INSTR function is used to find the character position where the last \ appears in the filename string (looking for the first occurrence from the end). The position ahead of that is passed into the SUBSTR function as the start position (hence the +1).


SELECT file_name,
SUBSTR(file_name, INSTR(file_name,’\’, -1,1)+1) Name
FROM dba_data_files;

FILE_NAME                                       NAME
----------------------------------------        -------------
C:\ORACLE\ORADATA1\MYNEWDB\USERS01.DBF          USERS01.DBF
C:\ORACLE\ORADATA1\MYNEWDB\UNDOTBS01.DBF        UNDOTBS01.DBF
C:\ORACLE\ORADATA2\MYNEWDB\SYSAUX01.DBF         SYSAUX01.DBF
C:\ORACLE\ORADATA3\MYNEWDB\SYSTEM01.DBF         SYSTEM01.DBF
C:\ORACLE\ORADATA3\MYNEWDB\EXAMPLE01.DBF        EXAMPLE01.DBF

To perform the same operation on Unix or Linux databases, replace \ in the INSTR function with /; because / is used on Linux/Unix to separate directories.

Let’s review another example using the Linux or Unix platform. Suppose you want to find out all the file systems (mount points) used by your database for storing data files; you could use the following SQL:

SELECT DISTINCT
SUBSTR(file_name, 1, INSTR(file_name,’/’, 1,2)-1) Mount_Point
FROM dba_data_files;
MOUNT_POINT
-----------------------------
/oradata1
/oradata2
/oradata3

In this example, you started looking for the second occurrence of / using the INSTR function and used SUBSTR to extract only the characters from 1 through the location before the second occurrence of / in the filename
(hence the –1).

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cer...
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0 ...
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can...
Read more