15 Basic SQL Queries For A Beginner DBA

SQL Queries for Dummies! Yes – Some of the 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’;

Article By: Anju