DEV Community

Abhilash Kumar Bhattaram
Abhilash Kumar Bhattaram

Posted on

The info.sql for Oracle Developer's / DBA's

For Oracle Developers / DBA's who work on multiple Environments , here is a script for you to quickly check which environment you are working on , it's extremely important to be aware of the Environment you are working on in SQL*Plus

My script info.sql helps you achieve the same
https://github.com/abhilash-8/ora-tools/blob/master/info.sql

The script shows you the following

  • Database Name and Database Unique you are working on
  • Role ( Primary / Standby ) of Database you are working on
  • Role of Database you are working on
  • Current FRA Usage
  • Current Sessions/Processes Connected
  • Alert Log File Location
  • Database Edition
  • Database Version

The above should give you a quick summary of the database you are connected to help you work further. The script also sets some formatting for you if you are a hard core SQL*Plus user


# sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 30 06:00:11 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @info

Session altered.

06:00:16 SQL> select instance_name, status, host_name, version, startup_time from gv$instance;
INSTANCE_NAME   |STATUS         |HOST_NAME                                                       |VERSION          |STARTUP_TIME
----------------|---------------|----------------------------------------------------------------|-----------------|-----------------------------
ORCL            |OPEN           |oralinux01                                                      |12.2.0.1.0       |29-JUN-2020 08:03:07

06:00:16 SQL> select dbid, name, db_unique_name,flashback_on,force_logging,log_mode, open_mode, database_role, sysdate from gv$database;
      DBID|NAME     |DB_UNIQUE_NAME                |FLASHBACK_ON   |FORCE_LOGGING  |LOG_MODE       |OPEN_MODE           |DATABASE_ROLE       |SYSDATE
----------|---------|------------------------------|---------------|---------------|---------------|--------------------|--------------------|-----------------------------
1483155577|ORCL       |ORCL                        |NO             |NO             |ARCHIVELOG     |READ WRITE          |PRIMARY             |30-JUN-2020 06:00:16

06:00:16 SQL> show parameter control_management_pack_access
NAME                                |TYPE                                        |VALUE
------------------------------------|--------------------------------------------|------------------------------
control_management_pack_access      |string                                      |DIAGNOSTIC+TUNING
06:00:16 SQL> show parameter diag
NAME                                |TYPE                                        |VALUE
------------------------------------|--------------------------------------------|------------------------------
diagnostic_dest                     |string                                      |/u01/app/oracle
06:00:16 SQL> show parameter background
NAME                                |TYPE                                        |VALUE
------------------------------------|--------------------------------------------|------------------------------
background_core_dump                |string                                      |partial
background_dump_dest                |string                                      |/u01/app/oracle/product/12.2.0.1/d
                                    |                                            |b_1/rdbms/log
06:00:16 SQL> select * from v$flash_Recovery_area_usage;
FILE_TYPE           |PERCENT_SPACE_USED|PERCENT_SPACE_RECLAIMABLE|NUMBER_OF_FILES|    CON_ID
--------------------|------------------|-------------------------|---------------|----------
CONTROL FILE        |                 0|                        0|              0|         0
REDO LOG            |                 0|                        0|              0|         0
ARCHIVED LOG        |              9.92|                        0|             35|         0
BACKUP PIECE        |               .01|                      .01|              8|         0
IMAGE COPY          |                 0|                        0|              0|         0
FLASHBACK LOG       |                 0|                        0|              0|         0
FOREIGN ARCHIVED LOG|                 0|                        0|              0|         0
AUXILIARY DATAFILE C|                 0|                        0|              0|         0
OPY                 |                  |                         |               |


8 rows selected.

06:00:16 SQL> show parameter reco
NAME                                |TYPE                                        |VALUE
------------------------------------|--------------------------------------------|------------------------------
control_file_record_keep_time       |integer                                     |7
db_recovery_file_dest               |string                                      |+RECODG
db_recovery_file_dest_size          |big integer                                 |3T
db_unrecoverable_scn_tracking       |boolean                                     |TRUE
recovery_parallelism                |integer                                     |0
remote_recovery_file_dest           |string                                      |
06:00:16 SQL> set lines 200 pages 200
06:00:16 SQL> col db_link format a40
06:00:16 SQL> select inst_id,resource_name, current_utilization, max_utilization from gv$resource_limit where resource_name in ('processes','sessions');
   INST_ID|RESOURCE_N|CURRENT_UTILIZATION|MAX_UTILIZATION
----------|----------|-------------------|---------------
         1|processes |                240|            445
         1|sessions  |                208|            444

06:00:16 SQL> select vd.value||'/alert_'||vi.instance_name||'.log' "alert_log" from v$diag_info vd ,gv$instance vi where vd.name like 'Diag Trace';
alert_log
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ORCL/ORCL/trace/alert_ORCL.log

06:00:16 SQL> select * from v$version;
BANNER                                                                          |    CON_ID
--------------------------------------------------------------------------------|----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production    |         0
PL/SQL Release 12.2.0.1.0 - Production                                          |         0
CORE    12.2.0.1.0      Production                                                      |         0
TNS for Linux: Version 12.2.0.1.0 - Production                                  |         0
NLSRTL Version 12.2.0.1.0 - Production                                          |         0

06:00:16 SQL> set time off
SQL> set echo off
ORCL>

Enter fullscreen mode Exit fullscreen mode

Top comments (0)