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 .

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.

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.

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.

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.