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_sectionwhere 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;
