NLS_DATE_FORMAT – RRRR or YYYY

This question has been asked recently and here is my input. RRRR and YYYY essentially perform the same function.  Both accept a 4 digit year. The exception is with RRRR.  It will also accept a 2 digit year.  This allows you to store 20th century dates in the 21st century by using the last 2 […]

RMAN – ALLOCATE CHANNEL FOR MAINTENANCE (RMAN-01009)

Performing maintenance using RMAN is part of any dba’s admin duties.  Issuing the commands in RMAN manually is not as common as it used to be.  Most dba’s have scripts that perform the normal RMAN tasks.  Because of this automation dba’s tend to forget the actual commands or structure of the commands. This tutorial is […]

Difference between DDL and DML

DDL statements (Data Definition Language) are used to define the structure of a table or database as well as the schema of the database. DML statements (Data Manipulation Language) are used to manage the data of the objects within a schema, table or database. Below are some examples of DDL and DML statements: DDL CREATE […]

How to get sql_id and sql_text for a concurrent request

A colleague asked for a quick video and text example of how to get sql_id and sql_text for a concurrent program that is running. The text is below: col action format a20 col module form a30 select sid,sql_id,action,module from v$session where action like ‘%Conc%’; select sql_text from v$sqlarea where sql_id=’SQL_ID’; I hope this is helpful. […]

Querying indexes on table and build script

Often times we are asked to rebuild indexes on tables, but are unaware of what all indexes are on the table. We can query dba_ind_columns to get indexes  on the columns or for general indexes against the table we can query dba_indexes. A general query would look like this: select owner,table_name,index_name from dba_indexes where table […]

How to view database parameters

I have started a youtube channel which I will slowly be adding new video content too. You can click on the video below.  Here is the text that you can use as well. sqlplus ‘/as sysdba’show parameter processes; exit; sqlplus apps/$MYPASS desc v$parameter col name format a20 col value format a30 select name, value from […]

Creating ‘LIKE’ users in the Oracle Database

This post I’m providing allows you to create a new database user modeled after an existing database user. We recently hired numerous dbas and wanted to provide the same access as existing dbas here. Using this script is permitted. As with any script on my blog or others please take caution. This has been verified […]