Posts

How to create an external table in Oracle

To create an external table in Oracle, you need to perform the following steps: Connect to the Oracle database server as a user with the CREATE TABLE privilege. You can do this using SQL*Plus or any other Oracle client. Create a directory object that points to the location of the data file on the file system. You can do this using the following SQL statement: CREATE DIRECTORY my_dir AS '/path/to/data/files'; Grant read permission on the directory object to the user who will be querying the external table. You can do this using the following SQL statement: GRANT READ ON DIRECTORY my_dir TO my_user; Create the external table using the CREATE TABLE statement. The table definition should include the ORGANIZATION EXTERNAL clause and a LOCATION clause that specifies the directory object and file name. CREATE TABLE my_table (    col1 NUMBER,     col2 VARCHAR2(20),     col3 DATE ) ORGANIZATION EXTERNAL (  TYPE ORACLE_LOADER  DEFAULT DIRECTORY my_dir   ACCESS PARAME

Shell script to monitor Oracle sessions and send an email alert

Shell scripting can be an effective tool for monitoring Oracle databases because it allows you to automate tasks and perform checks on a regular basis. Here is a Bourne shell script that you can use to monitor Oracle database sessions and send an email alert to admin@domain.com when the number of sessions is greater than 100: #!/bin/sh # Query to count the number of Oracle database sessions SESSION_COUNT=`sqlplus -S << EOF SELECT COUNT(*) FROM v\\$session WHERE type = 'USER'; EOF` # Check if the number of sessions is greater than 100 if [ $SESSION_COUNT -gt 100 ] then # Send an email alert to admin@domain.com mail -s "Oracle Session Alert" admin@domain.com << EOF The number of Oracle database sessions is currently $SESSION_COUNT, which is greater than the threshold of 100. Please check the database and take necessary action. EOF fi This script uses the sqlplus command to execute a SQL query that counts the number of user sessions in

SQL to monitor sessions in an Oracle database

Here is a SQL script that you can use to monitor Oracle database sessions: -- Query to monitor Oracle database sessions SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.program, s.osuser, s.process, s.logon_time FROM v$session s WHERE s.type = 'USER' ORDER BY s.logon_time ASC; This script queries the v$session view, which contains information about all the sessions currently connected to the database. The WHERE clause filters the rows to show only user sessions, and the ORDER BY clause sorts the results by logon time in ascending order. The columns in the v$session view include: sid : the session ID of the session serial# : the serial number of the session username : the username of the user connected to the session status : the status of the session (e.g. ACTIVE, INACTIVE) machine : the name of the machine from which the session is connected program : the name of the program connected to the session osuser : the operating system user associated with the ses

Setting up RAC in Oracle 21c

Oracle Real Application Clusters (RAC) is a feature of Oracle Database that allows multiple computers to run Oracle RDBMS software simultaneously while accessing a single database, providing high availability and scalability. Here is a simple guide on how to set up a RAC environment in Oracle 21c: Install Oracle 21c on multiple servers that will be part of the RAC cluster. Make sure to select the "RAC" option during the installation process. Configure the Oracle Grid Infrastructure on all servers in the RAC cluster. This includes setting up the Oracle Clusterware, creating a voting disk and OCR (Oracle Cluster Registry) file, and configuring the network interfaces. Create a database instance on each server in the RAC cluster. This involves creating a parameter file and starting up the instance using the Oracle Enterprise Manager. Create a database in the RAC environment using the Database Configuration Assistant (DBCA) or by manually creating the database using SQL command

How to create a PDB in Oracle (pluggable database)

To create a Pluggable Database (PDB) in Oracle, you will need to first create a Container Database (CDB). A CDB is a special type of Oracle database that can host one or more PDBs within it. Here's a simplified example of the steps involved in setting up a PDB: 1. Create a new Container Database (CDB) using the CREATE DATABASE command. For example: CREATE DATABASE cdb1  ADMIN USER admin IDENTIFIED BY password  DEFAULT TABLESPACE users  DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' SIZE 50M REUSE  SYSAUX DATAFILE '/u01/app/oracle/oradata/cdb1/sysaux01.dbf' SIZE 50M REUSE  DEFAULT TEMPORARY TABLESPACE temp UNDO TABLESPACE undotbs1  LOGFILE GROUP 1 '/u01/app/oracle/oradata/cdb1/redo01.log' SIZE 50M, '/u01/app/oracle/oradata/cdb1/redo02.log' SIZE 50M, '/u01/app/oracle/oradata/cdb1/redo03.log' SIZE 50M; 2. Once the CDB has been created, you can create a new PDB within it using the CREATE PLUGGABLE DATABASE

Oracle Pluggable Databases

Oracle Pluggable Databases (PDBs) are a feature of Oracle 12c and above that allow a single Oracle instance to host multiple, independent databases within it. This can be useful in a number of different scenarios, and can help organizations to better manage their database infrastructure and resources.  PDB for development One common use case for Oracle PDBs is for testing and development purposes. PDBs allow organizations to create multiple copies of their production databases, each with its own unique set of data and schema objects. This can be useful for testing new applications or making changes to the database schema without affecting the production environment. PDB for consolidation Another use case for Oracle PDBs is for consolidation of multiple databases onto a single server. This can help to reduce the number of servers required to support an organization's database infrastructure, and can also make it easier to manage and maintain those databases. PDBs can also be use

Are Oracle Certifications Worth the Cost?

A question every Oracle DBA eventually asks themselves in their career:  should I become Oracle certified?  Also, just how valuable is a certification from Oracle? I think the answer depends.  I don't view a certification as necessary, but it may be helpful in your circumstances. I am Oracle certified and did so early on in my career.  I took some time off in between jobs and decided that was a good way to fill my time.  Also, I was already doing DBA work, but hadn't been given the job title "Oracle DBA" (one reason I left my job at that time).  For me, getting certified allowed me to show that although I hadn't obtained the title yet, I had the work experience plus the Oracle verified skill set. I think hands-on work experience trumps certification.  I don't see many DBA job postings that say a certification is a must.  In a crowded market, it may help your resume stand out though. I also would not recommend someone who hasn't been in a DB