DBA Queries

Query to find out Command fired by Currently Connected Users

SELECT SUBSTR(s.username,1,15) USERNAME,
SUBSTR(s.status,1,8) STATUS,
SUBSTR(s.server,1,10) SERVER,
SUBSTR(s.type,1,10) TYPE,
SUBSTR(s.event,1,20) "WAIT EVENT",
DECODE(s.command,
       1,'Create Table',
       2,'Insert',
       3,'Select',
       6,'Update',
       7,'Delete',
       8,'Drop',
       9,'Create Index',
      10,'Drop Index',
      12,'Drop Table',
      17,'Grant',
      26,'Lock Table',
      42,'Alter Session',
      43,'Alter User',
      44,'Commit',
      45,'Rollback',
      s.command) COMMAND
FROM v_$session s, v_$session_wait w
WHERE (s.sid = w.sid)
AND s.username != 'SYS'
ORDER BY s.username;

Query to know Tablespace Usage by Users
a. Tablespace usage grouped by Owner and Tablespace.
COLUMN "Used MB" FORMAT 9,999,9999

BREAK ON REPORT ON Owner SKIP 1
COMPUTE SUM OF "Used MB" ON REPORT
COMPUTE SUM OF "Used MB" ON OWNER
SELECT ROWNUM AS Rank,
       Segments.*
FROM
       ( SELECT  Owner         ,
                Tablespace_Name,
                SUM ( Bytes ) / 1024 / 1024 "Used MB"
       FROM     DBA_SEGMENTS
       WHERE    Owner NOT IN ( 'SYS', 'SYSTEM', 'SYSAUX' )
       GROUP BY Owner,
                Tablespace_Name
       ORDER BY 3 DESC
       ) Segments
WHERE  ROWNUM <= 10;
CLEAR BREAKS
CLEAR COMPUTES


b.Space usage by each tablespace.

COMPUTE SUM OF "Used MB"ON Report
COMPUTE SUM OF "Used MB"ON Tablespace_Name
COLUMN "Used MB" FORMAT 9,999,990.99
SELECT ROWNUM AS Rank,
       Segments.*
FROM
       ( SELECT  Tablespace_Name,
                Owner           ,
                SUM ( Bytes ) / ( 1024 * 1024 ) "Used MB"
       FROM     DBA_SEGMENTS
       WHERE    Owner NOT IN ( 'SYS', 'SYSTEM', 'SYSAUX' )
       GROUP BY Tablespace_Name,
                Owner
       ORDER BY 3 DESC
       ) Segments
WHERE  ROWNUM <= 10;
CLEAR BREAKS
CLEAR COMPUTES


Basic Oracle queries/commands which might turn out extremely helpful to the beginners of Oracle database:

1. To check the name of the database:
SQL> select name from v$database;

2. To check the version of a database:
SQL> select * from v$version;

3. To check the size of the database.
SQL> select (select sum(bytes/1024/1024/1024) from dba_data_files)+(select sum(bytes/1024/1024/1024) from dba_temp_files) “Database Size in GB” from dual;

4. To check the current size of a tablespace:
SQL> select sum(bytes/1024/1024/1024) ” Size in GB” from dba_data_files where tablespace_name=’<TABLESPACE_NAME>’;

5. To check the free space of a tablespace:
SQL> select sum(bytes/1024/1024/1024) from dba_free_space where tablespace_name=’<TABLESPACE_NAME>’;

6. To check the size of a table:
SQL> Select sum(bytes/1024/1024) “size in MB” from dba_segments where segment_name=’<TABLE_NAME>’ and segment_type=’TABLE’;

7. To list all the tablespaces in the database:
SQL> select tablespace_name from dba_tablespaces;

8. To list all the users in the database:
SQL> select username from dba_users;

9. To check the default tablespace of a user:
SQL> select default_tablespace from dba_users where username=’<USERNAME>’;

10. To create a user:
SQL> create user <USERNAME> identified by <PASSWORD> default tablespace <DEFAULT_TABLESPACE_NAME> temporary tablespace <TEMP>;

11. To change the password of a user:
SQL> Alter user USERNAME identified by PASSWORD;

12. To drop a user along with its objects:
SQL> Drop user <USERNAME> cascade;

13. To list the details of the sessions currently connected to the database:
SQL> select sid,serial#,username,status from v$session;

14. To kill/terminate a user’s session:
Get the SID and SERIAL# of the session
SQL> select sid,serial#,username,status from v$session where username=’<USERNAME>’
Substitute the values for SID and SERIAL# in the below query.
SQL> Alter system kill session ‘SID,SERIAL#’;

15. To list all the objects of a user:
SQL> Select object_name,object_type from dba_objects where owner=’USERNAME’;