real life scenarios – new dba sql knowledge





OK. You are a new dba and you want to know all the real time scenarios where you can use your sql knowledge. If you know all the real time scenarios then your confidence levels increase and you also can then know how you can practice and where you can concentrate.

after  reading this article you can also see this link for more real time sql statements and exercises that as a new dba you should know .

sql exercises

Real time Scenario 1:

Our company does not use enterprise manager. We want to know when our oracle tablespaces are getting full. So we wrote some sql scripts to read oracle views and to find out the current tablespace sizes and then if it is almost full then it will create a log file and that log file is read by our monitoring system called e-health.
Below is part of the sql script that we created. This will tell you that you need to know how to formulate sql queries using the sum function , group by function , union etc.

I have tried to explain the select statement after each line

/* Formatted on 11/06/10 14:43:51 (QP5 v5.139.911.3011) */
SELECT C.TABLESPACE_NAME NAME,
–the tablespace_name was given an alias NAME above
SUM (C.DTOT) TOTAL,
–we gave the alias TOTAL above. This is how you specify column aliases. Aliases are nothing but some meaningful name that you want to call your columns.
SUM (C.DTOT) – SUM (C.FTOT) USED,
SUM (C.FTOT) FREE,
(SUM (C.DTOT) – SUM (C.FTOT)) / SUM (C.DTOT) * 100 PCENT
–below you will see that after the FROM clause you don’t have a table but you see another sql statement with open ( and close) brackets. This form of a query is called an inline view.
–That means you are dynamically creating a memory table using another sql query. Here you are creating a memory table and calling it by the name C (see the last but one line of the query)
FROM ( SELECT TABLESPACE_NAME, SUM (BYTES) DTOT, 0 FTOT
FROM DBA_DATA_FILES
WHERE AUTOEXTENSIBLE = ‘NO’
GROUP BY TABLESPACE_NAME
UNION
SELECT A.TABLESPACE_NAME,
0,
SUM (A.BYTES) / COUNT (DISTINCT B.FILE_ID) FTOT
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND B.AUTOEXTENSIBLE = ‘NO’
GROUP BY A.TABLESPACE_NAME
UNION
SELECT A.TABLESPACE_NAME,
SUM (A.BYTES) / COUNT (DISTINCT B.FILE_ID) DTOT,
0
FROM DBA_TEMP_FILES A, DBA_DATA_FILES B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND B.AUTOEXTENSIBLE = ‘NO’
GROUP BY A.TABLESPACE_NAME) C
GROUP BY C.TABLESPACE_NAME

If you don’t understand the query then try to divide it into small parts and try to understand what each statement is doing.

Real Time Scenario 2:

In our company we use the tool TOAD to connect to our databases. Our databases are on unix servers. Sometimes after logging into TOAD I just want to make a quick check whether the database is running correctly or whether I connected to the correct database etc. so I use the below various sql queries to find out about the database.
If you have enterprise manager then you don’t have to execute these sqls to find out information about the database. But all companies do not have enterprise manager and moreover no dba can survive without the basic sql knowledge and the statements that I am giving below.

Select * from v$database;

Desc v$database

Select * from v$instance;
Select * from dba_data_files;
Select * from dba_tablespaces;
Select * from dba_temp_files;
Select * from v$sql;
Select * from v$sql_plan;
Select * from v$session;
Select * from dba_users;
Select * from v$process;
Select * from dba_free_space;
Select * from v$tempseg_usage;
Select * from dba_jobs;
Select * from dba_scheduler_jobs;
Select * from dba_scheduler_job_run_details
Select * from dba_jobs_running
Select * from v$active_session_history
select * from dba_hist_active_sess_history
select * from dba_hist_sqlstat
select * from dba_hist_sql_plan

These are the various tables that you would need to use regularly to verify some or the other information and depending on your need you might need to use the group by functions , max , sum , count , min , avg , to_date , to_char functions.

For example we use TOAD version 9 and this doesn’t recognize timestamps ..so when we try to select from a table that has timestamp columns toad throws some errors.so I use the to_char function to convert the timestamp column into a normally date and in char format. You can see the sql statement below.

select OWNER, JOB_NAME, JOB_SUBNAME, JOB_CREATOR, CLIENT_ID, GLOBAL_UID, PROGRAM_OWNER, PROGRAM_NAME, JOB_TYPE, JOB_ACTION, NUMBER_OF_ARGUMENTS,
SCHEDULE_OWNER, SCHEDULE_NAME, SCHEDULE_TYPE, to_char(START_DATE,’dd-mm-yyyy Hh24:MI:SS’), REPEAT_INTERVAL, EVENT_QUEUE_OWNER, EVENT_QUEUE_NAME, EVENT_QUEUE_AGENT, EVENT_CONDITION,
EVENT_RULE, to_char(END_DATE,’dd-mm-yyyy Hh24:MI:SS’), JOB_CLASS, ENABLED, AUTO_DROP, RESTARTABLE, STATE, JOB_PRIORITY, RUN_COUNT, MAX_RUNS, FAILURE_COUNT, MAX_FAILURES, RETRY_COUNT,
to_char(LAST_START_DATE,’dd-mm-yyyy Hh24:MI:SS’), LAST_RUN_DURATION, to_char(NEXT_RUN_DATE,’dd-mm-yyyy Hh24:MI:SS’), SCHEDULE_LIMIT, MAX_RUN_DURATION,
LOGGING_LEVEL, STOP_ON_WINDOW_CLOSE, INSTANCE_STICKINESS, RAISE_EVENTS,
SYSTEM, JOB_WEIGHT, NLS_ENV, SOURCE, DESTINATION, COMMENTS, FLAGS
from dba_scheduler_jobs;

select LOG_ID, to_char(LOG_DATE,’dd-mm-yyyy Hh24:MI:SS’), OWNER, JOB_NAME, JOB_SUBNAME, STATUS,
ERROR#, to_char(REQ_START_DATE,’dd-mm-yyyy Hh24:MI:SS’), to_char(ACTUAL_START_DATE,’dd-mm-yyyy Hh24:MI:SS’),
RUN_DURATION, INSTANCE_ID, SESSION_ID,
SLAVE_PID, CPU_USED, ADDITIONAL_INFO from dba_scheduler_job_run_details where job_name like ‘%INACTIVE_PURGING%’;

Real time Scenario 3:

Most of the sql scripts that are in our environment were written by some earlier dba’s who left the company. So sometimes I had to change the scripts to add some additional functionality. so you could face the same situation which means you should be able to look at a sql statement and understand what exactly it is doing.

Recently I had to deploy one application script on our production database.
The script was written by an application oracle pl/sql developer.

All I need to do was to execute it. But in case of any errors I should be able to troubleshoot the script .so it means I should understand what the script contains. sometimes the application developer comes to my room and we both will deploy the script together but this doesn’t happen always. So you should be able to understand any sql script. I have given a sample script that I deployed recently. The names have been changed for privacy reasons. See if you can understand the below script.all the best.

— XYZ_STATS_v32.sql 14/10/2008 Gunther schatz SHELL

prompt Connect as ISC (that shoud be locked afterwards)
connect ISC

— VIEW V_I_TRUTH ——————————————-
CREATE OR REPLACE VIEW V_I_TRUTH AS
SELECT cb_from, cat_id, start_date, denom_id, value, period_type_id
FROM i_truth t
WHERE cat_id IN (13,35,10,32,50,56,51,55,11,33,12)

UNION ALL

— Logistical Stocks
— The calculation of the sum of categories (2, 3, 6, 7)
— v32, 14/10/2008 GSc: removed duplicates between paragraphs 2 and 4 by amending
— paragraph 2.
SELECT 1000 AS cb_from, cat_id, f.start_date, f.denom_id, value, f.period_type_id
FROM i_truth f, i_denom d
WHERE f.cat_idIN (2,3,6,7)
AND f.denom_ID = d.denom_ID
AND NOT (d.BANKNOTE_APPLICABILITY_IND = ‘Y’)

UNION ALL

— Strategic Stocks
— The calculation of the sum of categories (8, 9)
SELECT 1000 AS cb_from, cat_id, start_date, 0 AS denom_id, SUM(value) AS value, period_type_id
FROM i_truth t
WHERE cat_idIN (8,9)
GROUP BY cat_id, start_date, period_type_id

UNION ALL

— Euro Banknotes in Circulation
— The calculation of the value is category 1 – sum of categories (2, 3, 4, 5, 6, 7, 8, 9, 14)
SELECT 1000 AS cb_from, cat_id, f.start_date, f.denom_id,
value, f.period_type_id
FROM i_truth f, i_denom d
WHERE f.cat_id IN (1,2,3,4,5,6,7,8,9,14)
AND d.BANKNOTE_APPLICABILITY_IND = ‘Y’
AND f.denom_ID = d.denom_ID

UNION ALL

— Euro Coins in Circulation (number of coins)
— The calculation of the sum of category 50)
SELECT t.cb_from, cat_id, start_date, t.denom_id, value, period_type_id
FROM i_truth t, i_denom d
WHERE t.cat_id= 50
AND d.coin_applicability_ind = ‘Y’
AND t.denom_ID = d.denom_ID
;

GRANT SELECT ON CIS.V_I_truth TO STATS;

Real time Scenario 4

Recently I deployed a sql script on production and the sql script has been given to me by a application developer. We follow ITIL concepts. So that means the application developer raised a change request and then it was approved by the change management team after the application team provided their test results that they tested the script on their test environment and that the script is free without any errors etc.

Below is the log file once I deployed the script. Unfortunately there was a error and the error happened because of some sequence problem on the production table. This error did not happen in the test environment as the sequences were fine in the test environment. So this tells you that sometimes errors can happen unexpectedly.now read the log and my comments below to understand why you should know different oracle concepts and the sql concepts.

$ vi db_deployment_log.log

"db_deployment_log.log" 128 lines, 1704 characters

–so here i started to execute the sql file. as you know that in sqlplus you use the @ operator and then the filename to execute a script.

SQL> @db_deployment.sql;

Trigger altered.

0 rows deleted.

Trigger altered.

Trigger altered.

0 rows deleted.

0 rows deleted.

Commit complete.

Trigger altered.

no rows selected

Trigger altered.

0 rows deleted.

Trigger altered.

no rows selected

Trigger altered.

0 rows deleted.

Trigger altered.

1 row created.

–here i get some error. the insert statement failed but i dont know what this application is doing. so even though i know that the below unique key constraint value is because of some duplicate data
i still need to discuss with the application oracle pl/sql developer to understand his logic . nevertheless from the below code i understood that it is using the sequence
A_BUSINESS_PRIVILEGE_SEQ.nextval@curr_dbl.world and he is accessing the sequence across a db link (see the @ sign) . but sequences always generate unique values so why do we have a duplicate error .could be because the table had already data with the same sequence value.so in this way you try to understand about the error. in the end it turned out that due to some problem when importing the database the sequences on this table have not be refreshed correctly so it was generating pretty old values.i think now you can understand why you have to understand the sql scripts and why you need to know what db links are and what sequences are etc. If you face a similar situation in the future dont panic. if you dont know what unique key constraint errors are then search in google. if you dont know what sequencea re then search in google.once you know then use this knowledge to find out what could be the issue.

INSERT INTO A_BUSINESS_PRIVILEGES (BUSINESS_PRIVILEGE_ID,NAME,ASSET,ACTION,DESCRIPTION,OWNED_ITEM,BN_MO,COMMON_CLASSES_ACCESS
,OWN_CENTRE_IND,SENSITIVE,DB_TYPE,DELETED_IND,CREATOR,CREATED_BY,CREATION_DATE,MODIFIER,MODIFIED_BY,MODIF_DATE,TL_VER_NO,CREATOR_ROLE,MODIF
IER_ROLE) values (A_BUSINESS_PRIVILEGE_SEQ.nextval@curr_dbl.world,’currencytrade’,’currency
persist.dollar’,’read’,’Read-only access to
Attachments’,’N’,’I’,’N’,’N’,’N’,’B’,’N’,0,’dms_se’,to_date(‘25.10.06′,’DD.MM.RR’),0,’dmsseuser’,to_date(‘25.10.06′,’DD.MM.RR’),0,0,0)
*
ERROR at line 1:
ORA-00001: unique constraint (ams.A_BUSINESS_PRIVILEGE_PK) violated

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

Commit complete.

The Name of the Business-Privileges or Group of Business-Privileges cannot be
found. Please, setup the new Security-Constraints manually by the
CFS-Application!

PL/SQL procedure successfully completed.

Trigger altered.

0 rows deleted.

Commit complete.

Trigger altered.

no rows selected

Trigger altered.

0 rows deleted.

Trigger altered.

PL/SQL procedure successfully completed.

SQL> exit

Real Time Scenario 5

Recently I deployed one application. The application team sent me detailed instructions on how to execute the sql files. Below are the instructions that I got.

As you see one should be able to understand those instructions and know how to execute those scripts using sqlplus.

OurApp BSE Database

1) Login as TN
2) Execute 01_OurApp_1.3.0_DB_CR_BSE_NSE_Grants.sql
3) Login as OurApp
4) Execute 02_OurApp_1.3.0_DB_CR_BSE_Queries.sql
4) Execute 04_OurApp_1.3.0_DB_CR_NSE_BSE_Queries.sql
5) Execute 05_OurApp_1.3.0_DB_CR_BSE_STC.sql
6) Create a Link to the OurApp NSE Database
create public database link OurAppNSE connect to OurApp identified by
using ‘OurAppNSE’;
7) Copy the table OurApp_STC_DSI to OurApp
8) Drop the database link
9) Log off

OurApp NSE Database

1) Login as TN
2) Execute 01_OurApp_1.3.0_DB_CR_BSE_NSE_Grants.sql
3) Login as OurApp
4) Execute 02_OurApp_1.3.0_DB_CR_NSE_BSE_Queries.sql
4) Execute 03_OurApp_1.3.0_DB_CR_NSE_Queries.sql
5) Create a database link to the OurApp BSE database
create public database link OurApp connect to OurApp identified by
using ‘OurApp’;
6) Execute 06_OurApp_1.3.0_DB_CR_NSE_STC.sql
7) Log off

sometimes you might have to search for some log records from an application table to troubleshoot an issue.so you will quickly write a sql as below

select * from application_log ap
where ap.CREATE_TIMESTAMP > to_date(‘15.07.2008′,’dd.MM.YYYY’);

so  you can see why it is important for you to know to write fast simple select statements using the to_date , to_char and other functions as mentioned in one of the above scenarios.

Real Time Scenario 6

 

Recently one job was found to be running very slow.

I wanted to put a trace on the job.

First I used the below statement to find out the sid and other info about the job because I need these values if I want to enable trace on this session.

 

select sid,job,last_date,to_char(this_date,’dd-mm-yyyy HH24:MI:SS’) from dba_jobs_running;

       SID        JOB LAST_DATE TO_CHAR(THIS_DATE,’

———- ———- ——— ——————-

       315         18           04-06-2009 23:38:22

so once I got the SID value 315 then I read the v$session to find out the serial#

select serial# from v$session where sid = 315.

Now I used the below trace command to enable tracing for this session

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 315, serial_num =>19, waits => TRUE, binds => FALSE);

This trace file will be automatically created in the udump directory for oracle 10g.

For oracle 11g this will be created in the trace directory.

Author: admin