Oracle Db Basic Commands

Oracle Database Commands and Queries: 1. To view all the table from dictionary : SQL> select table_name from dictionary; 2. To identify the database name : SQL> select name from v$database; 3. To identify the instance name : SQL> select instance from v$thread; 4.

To know the size of the database blocks : SQL> select value from v$parameter where name =’db_block_size’;

5.

List the name of the data files : SQL> select name from v$datafile;

6.

Identify the datafile that makes up the system tablespace : SQL> select file_name from dba_data_files where tablespace_name = ‘SYSTEM’;

7.

To check how much free space is available in database and how much is used: SQL>select sum(bytes)/1024 “free space in KB” from dba_free_space; SQL>select sum(bytes)/1024 “used space in KB” from dba_segments”;

8.

List the name and creation date of database users : SQL>select username, created from dba_users;

9.

Where is the existing Control file located and what is the name? SQL> select * from v$controlfile; OR SQL> show parameter control SQL> select name from v$controlfile;

10. What is the initial sizing of the datafile section in your control file? SQL>select records_total from v$controlfile_record_section where type = “DATAFILE”; 11.

List the number and location of existing log files? SQL> select member from v$logfile;

12.

Display the number of redo log file groups and members your database has ? SQL>select group#, members, status from v$log;

13.

In which database mode is your database configured? SQL> select log_mode from v$database;

14.

Is archiving enabled? SQL>select archiver from v$instance;

15.

To view all the tablespaces name? SQL>select tablespace_name from dba_tablespaces;

16.

Identify the different types of segments in the database. SQL>select DISTINCT segment_type from dba_segments;