Database administration queries

Database administration queries

Database version information
Returns the Oracle database version.

SELECT * FROM v$version;


Database default information
Some system default information.

SELECT username,
profile,
default_tablespace,
temporary_tablespace
FROM dba_users;


Database Character Set information
Display the character set information of database.

SELECT * FROM nls_database_parameters;

Get Oracle version

SELECT VALUE
FROM v$system_parameter
WHERE name = 'compatible';


Store data case sensitive but to index it case insensitive
Now this ones tricky. Sometime you might querying database on some value independent of case. In your query you might do UPPER(..) = UPPER(..) on both sides to make it case insensitive. Now in such cases, you might want to make your index case insensitive so that they don’t occupy more space. Feel free to experiment with this one.

CREATE TABLE tab (col1 VARCHAR2 (10));

CREATE INDEX idx1
ON tab (UPPER (col1));

ANALYZE TABLE a COMPUTE STATISTICS;


Resizing Tablespace without adding datafile
Yet another DDL query to resize table space.

ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;


Checking autoextend on/off for Tablespaces
Query to check if autoextend is on or off for a given tablespace.

SELECT SUBSTR (file_name, 1, 50), AUTOEXTENSIBLE FROM dba_data_files;

(OR)

SELECT tablespace_name, AUTOEXTENSIBLE FROM dba_data_files;


Adding datafile to a tablespace
Query to add datafile in a tablespace.

ALTER TABLESPACE data01 ADD DATAFILE '/work/oradata/STARTST/data01.dbf'
SIZE 1000M AUTOEXTEND OFF;


Increasing datafile size
Yet another query to increase the datafile size of a given datafile.

ALTER DATABASE DATAFILE '/u01/app/Test_data_01.dbf' RESIZE 2G;


Find the Actual size of a Database
Gives the actual database size in GB.

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;


Find the size occupied by Data in a Database or Database usage details
Gives the size occupied by data in this database.

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;


Find the size of the SCHEMA/USER
Give the size of user in MBs.

SELECT SUM (bytes / 1024 / 1024) "size"
FROM dba_segments
WHERE owner = '&owner';

Last SQL fired by the User on Database
This query will display last SQL query fired by each user in this database. Notice how this query display last SQL per each session.

SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME,
s.program || '-' || s.terminal || '(' || s.machine || ')' PROG,
s.sid || '/' || s.serial# sid,
s.status "Status",
p.spid,
sql_text sqltext
FROM v$sqltext_with_newlines t, V$SESSION s, v$process p
WHERE t.address = s.sql_address
AND p.addr = s.paddr(+)
AND t.hash_value = s.sql_hash_value
ORDER BY s.sid, t.piece;

Performance related queries
CPU usage of the USER
Displays CPU usage for each User. Useful to understand database load by user.

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.STATISTIC# = sn.STATISTIC#
AND NAME LIKE '%CPU used by this session%'
AND se.SID = ss.SID
AND ss.status = 'ACTIVE'
AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

Long Query progress in database
Show the progress of long running queries.

SELECT a.sid,
a.serial#,
b.username,
opname OPERATION,
target OBJECT,
TRUNC (elapsed_seconds, 5) "ET (s)",
TO_CHAR (start_time, 'HH24:MI:SS') start_time,
ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
FROM v$session_longops a, v$session b
WHERE a.sid = b.sid
AND b.username NOT IN ('SYS', 'SYSTEM')
AND totalwork > 0
ORDER BY elapsed_seconds;


Get current session id, process id, client process id?
This is for those who wants to do some voodoo magic using process ids and session ids.


SELECT b.sid,
b.serial#,
a.spid processid,
b.process clientpid
FROM v$process a, v$session b
WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');

V$SESSION.SID AND V$SESSION.SERIAL# is database process id
V$PROCESS.SPID is shadow process id on this database server
V$SESSION.PROCESS is client PROCESS ID, ON windows it IS : separated THE FIRST # IS THE PROCESS ID ON THE client AND 2nd one IS THE THREAD id.

Last SQL Fired from particular Schema or Table:
SELECT CREATED, TIMESTAMP, last_ddl_time
FROM all_objects
WHERE OWNER = 'MYSCHEMA'
AND OBJECT_TYPE = 'TABLE'
AND OBJECT_NAME = 'EMPLOYEE_TABLE';


Find Top 10 SQL by reads per execution

SELECT *
FROM ( SELECT ROWNUM,
SUBSTR (a.sql_text, 1, 200) sql_text,
TRUNC (
a.disk_reads / DECODE (a.executions, 0, 1, a.executions))
reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 3 DESC)
WHERE ROWNUM < 10;


Oracle SQL query over the view that shows actual Oracle connections.

SELECT osuser,
username,
machine,
program
FROM v$session
ORDER BY osuser;

Oracle SQL query that show the opened connections group by the program that opens the connection.

SELECT program application, COUNT (program) Numero_Sesiones
FROM v$session
GROUP BY program
ORDER BY Numero_Sesiones DESC;


Oracle SQL query that shows Oracle users connected and the sessions number for user

SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones
FROM v$session
GROUP BY username
ORDER BY Numero_Sesiones DESC;


Get number of objects per owner

SELECT owner, COUNT (owner) number_of_objects
FROM dba_objects
GROUP BY owner
ORDER BY number_of_objects DESC;

Read More

Most Important Data dictionary Queries..

Data dictionary queries

Check if a table exists in the current database schema
A simple query that can be used to check if a table exists before you create it. This way you can make your create table script rerunnable. Just replace table_name with actual table you want to check. This query will check if table exists for current user (from where the query is executed).

SELECT table_name
FROM user_tables
WHERE table_name = 'TABLE_NAME';


Check if a column exists in a table
Simple query to check if a particular column exists in table. Useful when you tries to add new column in table using ALTER TABLE statement, you might wanna check if column already exists before adding one.

SELECT column_name AS FOUND
FROM user_tab_cols
WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';


Showing the table structure
This query gives you the DDL statement for any table. Notice we have pass ‘TABLE’ as first parameter. This query can be generalized to get DDL statement of any database object. For example to get DDL for a view just replace first argument with ‘VIEW’ and second with your view name and so.

SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;


Getting current schema
Yet another query to get current schema name.

SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;


Changing current schema
Yet another query to change the current schema. Useful when your script is expected to run under certain user but is actually executed by other user. It is always safe to set the current user to what your script expects.

ALTER SESSION SET CURRENT_SCHEMA = new_schema;

Read More

Oracle date and time function related queries

Date / Time related queries

Get the first day of the month
Quickly returns the first day of current month. Instead of current month you want to find first day of month where a date falls, replace SYSDATE with any date column/value.

SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"
FROM DUAL;


Get the last day of the month
This query is similar to above but returns last day of current month. One thing worth noting is that it automatically takes care of leap year. So if you have 29 days in Feb, it will return 29/2. Also similar to above query replace SYSDATE with any other date column/value to find last day of that particular month.

SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"
FROM DUAL;


Get the first day of the Year
First day of year is always 1-Jan. This query can be use in stored procedure where you quickly want first day of year for some calculation.

SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;


Get the last day of the year
Similar to above query. Instead of first day this query returns last day of current year.

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL


Get number of days in current month
Now this is useful. This query returns number of days in current month. You can change SYSDATE with any date/value to know number of days in that month.

SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days
FROM DUAL;


Get number of days left in current month
Below query calculates number of days left in current month.

SELECT SYSDATE,
LAST_DAY (SYSDATE) "Last",
LAST_DAY (SYSDATE) - SYSDATE "Days left"
FROM DUAL;


Get number of days between two dates
Use this query to get difference between two dates in number of days.

SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0)
num_of_days
FROM DUAL;

OR

SELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees;
Use second query if you need to find number of days since some specific date. In this example number of days since any employee is hired.

Display each months start and end date upto last month of the year
This clever query displays start date and end date of each month in current year. You might want to use this for certain types of calculations.

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date,
TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date
FROM XMLTABLE (
'for $i in 0 to xs:int(D) return $i'
PASSING XMLELEMENT (
d,
FLOOR (
MONTHS_BETWEEN (
ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12),
SYSDATE)))
COLUMNS i INTEGER PATH '.');


Get number of seconds passed since today (since 00:00 hr)

SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning
FROM DUAL;


Get number of seconds left today (till 23:59:59 hr)

SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left
FROM DUAL;

Read More

Normalization In SQL

Normalization In SQL,MY SQL and Oracle

What is normalization ?

Defination : Normalization is the process of efficiently organizing data in a database.

There are two goals of the normalization process: 

1. eliminating redundant data (for example, storing the same data in more than one table) and 

2. ensuring data dependencies make sense (only storing related data in a table).

 Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. There are several benefits for using Normalization in Database.

Benefits :
Eliminate data redundancy
Improve performance
Query optimization
Faster update due to less number of columns in one table
Index improvement

There are diff. - diff. types of Normalizations form available in the Database. Lets see one by one.

1. First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:
Eliminate duplicative columns from the same table.
Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
Remove repetative groups
Create Primary Key



Name    State    Country    Phone1              Phone2                  Phone3
John       101           1     488-511-3258      781-896-9897      425-983-9812
Bob       102            1     861-856-6987
Rob       201            2     587-963-8425      425-698-9684
PK [ Phone Nos ]
? ?
ID Name   State   Country          Phone
1      John   101        1        488-511-3258
2      John   101        1        781-896-9897
3      John   101        1        425-983-9812
4      Bob   102         1       861-856-6987
5      Rob   201         2       587-963-8425
6      Rob   201         2       425-698-9684



2. Second Normal Form (2NF)Second normal form (2NF) further addresses the concept of removing duplicative data:

· Meet all the requirements of the first normal form.

· Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

· Create relationships between these new tables and their predecessors through the use of foreign keys.

Remove columns which create duplicate data in a table and related a new table with Primary Key – Foreign Key relationship


ID Name State Country Phone
1     John   101       1       488-511-3258
2     John   101       1       781-896-9897
3     John   101       1       425-983-9812
4     Bob   102        1       861-856-6987
5     Rob   201        2       587-963-8425
6     Rob   201        2      425-698-9684


ID Name State Country                                        PhoneID        ID         Phone
1     John   101      1                                                    1             1          488-511-3258
2     Bob    102     2                                                    2             1         781-896-9897
3     Rob    201     3                                                    3             1         425-983-9812
                                                                                  4             2          587-963-8425
                                                                                  5             3          587-963-8425
                                                                                  6             3          425-698-9684




3. Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

· Meet all the requirements of the second normal form.

· Remove columns that are not dependent upon the primary key.

Country can be derived from State also… so removing country
ID Name State Country
1    John   101        1
2    Bob   102        1
3    Rob    201        2




4. Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

· Meet all the requirements of the third normal form.

· A relation is in 4NF if it has no multi-valued dependencies.



If PK is composed of multiple columns then all non-key attributes should be derived from FULL PK only. If some non-key attribute can be derived from partial PK then remove it



The 4NF also known as BCNF NF




TeacherID StudentID SubjectID StudentName
101              1001            1               John
101              1002            2               Rob
201              1002            3               Bob
201              1001            2               Rob


TeacherID StudentID SubjectID StudentName
101               1001            1                X
101               1002            2                X
201               1001            3                X
201               1002            2                X

Read More

SQL Functions(ddl,dml,dcl & tcl) with Examples

There are 4 types of sql functions
1.DML
2.DDL
3.DCL
4.TCL

Now lets see one by one

1.DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.


Examples: SELECT, UPDATE, INSERT statements


Select :- This Sql Statement is used to extract the data from one or combination of tables
Update:- This Sql Statement is used to update the data in a database table.
Delete:- This Sql Statement is used to delete data from the database table.
Insert Into :- This Sql Statement is used to insert data into a database(table)


2.DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.


Examples: CREATE, ALTER, DROP statements


Create Table:- This Sql Statements is used to create a Table
Alter Table:- This Sql Statement is used to Alter the table definition like adding any columns or deleting any table column.
Drop table:- This Sql Statement is used to Drop the table.
Create Index:- This Sql Statement is used to Create a Index on a table
Drop Index:- This Sql Statement is used to drop a table from the table


3.DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.


Examples: GRANT, REVOKE statements
Grant :- This Sql Statement is used to give access rights to the user for the database
Revoke:- This Sql Statement is used to revoke or delete the access rights of some of the users for a given database.


4.TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.


Examples: COMMIT, ROLLBACK statements


Commit:- This command is used to save the work done by the user.
Rollback:- This command is used to delete the data till the last committed state of the database.

Read More