Oracle of Nashville

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 ...

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 ...

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...

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...

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 (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...

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 bee...

You can import specific tables with data pump by using the TABLES parameter . impdp user/pwd directory=MY_DIR dumpfile=export.dmp logfile=import.log tables=schema1.table_name1,schema2.table_name2

Here's the only method I've found for using Data Pump to extract a package from a dump file.  You don't actually import it directly to a database.  Impdp creates a sql file with the source code for re-creating the package. impdp username/password directory=MYDIR dumpfile=export.dmp sqlfile=mysql.sql This will get all the source code for every package in your dump file.

Using datapump to import one or more schemas in an Oracle database is very easy. dpimp user/password dumpfile=file.dmp schemas=SCOTT,USER2 directory=DMP_DIR

To track when a user logs on or off Oracle, enable auditing and audit CONNECT in your database.  Then you can query DBA_AUDIT_TRAIL to find logon and logoff info. alter session SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS'; select username, action,action_name,timestamp,logoff_time from dba_audit_trail where action_name = 'LOGON' and username = '&user'; Here are some more helpful links: http://itechshare.blogspot.com/2010/06/oracle-how-to-track-logon-logout-in.html http://itechshare.blogspot.com/2010/06/oracle-why-dbaaudittrail-shows-more.html

If you have Streams configured in your Oracle database environment, you probably know that RMAN is "Streams aware", meaning, it knows which archive logs Streams still needs and won't remove those from disk once backed up. There are however RMAN commands that will remove archive logs even if they're still needed by Streams. The problem I encountered the other day was with this RMAN command to backup my archive logs, which I run every 15 minutes: run { backup archivelog all delete input; } My archive logs were removed from disk after being backed up, but not if needed by Streams.  The problem was my backup files were getting cumulatively larger throughout the day, because every time it ran, RMAN backed up every archive log still on disk.  This quickly filled the disk. I only wanted to backup archive logs that had not already been backed up, so I tried this: run { backup archivelog all not backed up 1 times delete input; }  My archive backups were no...

Here's a great resource for running Oracle's AWR report manually from the SQL prompt. http://oraclefunda.wordpress.com/2009/10/29/how-to-create-awr-report-manually/

Several weeks ago we had major corruption across the file-system on our production database server.  Everything was affected--Oracle home files, database files, logs...everything. After I restored the database, I used RMAN to search for corruption in the instance. RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL; This command will validate all database and archive log files that would be backed up, checking for physical and logical corruption.  No backup is actually done with the command. When corruption is found, it can be seen in  V$DATABASE_BLOCK_CORRUPTION . My output was the following: FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE ---------- ---------- ---------- ------------------ ------------------------------------ 15 2061570 126 9709077348 NOLOGGING 15 2061442 126 9709084124 NOLOGGING Corruption was found, but the corruption type wasn't shown.  I also couldn't find the objects that were corrupted. SELECT owner, segment_...

Rsync is a useful Unix tool for keeping directories in synch between two server.  I use it to copy Oracle archive logs from a production server to a test server. This command runs on my test server from the oracle user's crontab every 5 minutes.  It copies the files via SSH.  New files from the source directory are copied to the test backup directory.  If a file is deleted from the source directory, it is NOT removed from the target directory.  I have it setup this way because I want to control that manually.  I have a separate cronjob that purges archive log files over 3 days old. rsync -rave "ssh -l oracle" sourceServer:/u06/oradata/PROD/arch  /u06/oradata/prodArchBkp Rsync also preserves the timestamp of the source file in the target directory. You can also use rsync to delete files in the target directory that do not exist in the source directory. rsync -rave "ssh -l oracle" --delete sourceServer:/u06/oradata/PROD/arch  ...

I was running Oracle DBVerify against my datafiles post-recovery to look for any corruption.  Things were going fine until I made a mistake in the syntax of my command.  I accidentally set the logfile parameter to the datafile name.  DBVerify completely erased the contents of my datafile. system.dbf before my mistake: 770M Sep 7 23:42 system01.dbf I ran this command: dbv blocksize=8192 file=system01.dbf logfile=system01.dbf After dbverify:  0 Sep 8 12:37 system01.dbf Lesson learned.  Be very careful when cutting and pasting!

Today I logged on to one of my servers I rarely use to create a crontab script to be run by the oracle user.  I ran the command crontab -l to view the contents of crontab.  I then entered crontab -e to edit it.  No dice.  The cursor hung on a new line and '447' was outputed to the screen.  I had to do CTRL+C to get out of it. oracle@server:~ > crontab -e 447 The fix was to set the EDITOR environment variable to vi.  I added this to the oracle's .profile: export EDITOR=vi

If you're having problems with your Oracle database password file, be sure the SID included as part of the file-name matches the case for the ORACLE_SID.  The password file-name is case-sensitive. For example, my ORACLE_SID equals prod, but my password file is named orapwPROD.  When I query v$pwfile_users, no rows are returned. SQL> select * from v$pwfile_users; no rows selected Recreate your password file with the SID in the correct case.  In this instance, the password file should be named orapwprod.  You'll then get the following output: SQL> select * from v$pwfile_users; USERNAME                       SYSDB SYSOP ------------------------------ ----- ----- SYS                            TRUE  TRUE

Changing the four most common Oracle system account (SYSTEM, SYS, SYSMAN, & DBSNMP) passwords is a straightforward process. You needn't worry about breaking connections between the database and other systems because they shouldn't be using these accounts. SYSTEM and SYS can be changed hassle-free from SQL*Plus. SQL> alter user system identified by new_password; SYSMAN and DBSNMP are both used by Oracle Enterprise Manager. A little bit of preparation is necessary to keep OEM functioning. 1. Stop DB Console and the Agent. emctl stop dbconsole emctl stop agent 2. You can now change the passwords for both accounts. 3. Edit $ORACLE_HOME/sid/sysman/emd/targets.xml Look for: <property encrypted="TRUE" name="password" value="####"></property> Put your password in for the value and change ENCRYPTED to FALSE. When you restart DB Control it will encrypt the password in the file. 4. Edit $ORACLE_HOME/sid/sysman/con...

I thought I had installed a newer CPU in my test Oracle home that what was in my production home, but I really couldn't remember. I needed to find what CPUs were installed in each so I could get them back in synch. There are two ways to accomplish this. You can query dba_registry_history which would yield the following output: select action_time, id, comments from dba_registry_history; ACTION_TIME                     ID      COMMENTS 25-JUL-08 08.15.19.596514000 PM 6646853 CPUJan2008 This was unreliable in my case though, because I had cloned over my test instance from my production bringing the CPU data from that instance. The second and more reliable method is to grep Opatch for the patch number of the CPU. opatch lsinventory | grep 6646853 If you get a hit, that's the latest CPU installed.