Script – Database structure and file location

-

 

Script – Database structure and file location

set pagesize 500
set linesize 130
Prompt
Prompt Control Files Location >>>>
col name  format a60 heading "Control Files"

select name
from   sys.v_$controlfile
/

Prompt
Prompt Redo Log File Locations >>>>
Prompt

col Grp    format 9999
col member format a50 heading "Online REDO Logs"
col File#  format 9999
col name   format a50 heading "Online REDO Logs"
break on Grp
select group#,member
from   sys.v_$logfile
/


Prompt Data Files Locations >>>>

col Tspace    format a25
col status    format a3  heading Sta
col Id        format 9999
col Mbyte     format 999999999
col name      format a50 heading "Database Data Files"
col Reads     format 99,999,999
col Writes    format 99,999,999

break on report
compute sum label 'Total(MB)'  of Mbyte  on report

select F.file_id Id,
       F.file_name name,
       F.bytes/(1024*1024) Mbyte,
       decode(F.status,'AVAILABLE','OK',F.status) status,
       F.tablespace_name Tspace
from   sys.dba_data_files F
order by tablespace_name
 
Sample output
==============
Control Files Location >>>>

Control Files
------------------------------------------------------------
/u03/oradata/rcatp/control01.ctl
/u05/oradata/rcatp/control02.ctl


Redo Log File Locations >>>>


    GROUP# Online REDO Logs
---------- --------------------------------------------------
         1 /u03/oradata/rcatp/redo01.log
         2 /u05/oradata/rcatp/redo02.log
         3 /u03/oradata/rcatp/redo03.log
         3 /u05/oradata/rcatp/redo03b.log
         1 /u05/oradata/rcatp/redo01b.log
         2 /u03/oradata/rcatp/redo02b.log

6 rows selected.

Data Files Locations >>>>

   ID Database Data Files                                     MBYTE Sta TSPACE
----- -------------------------------------------------- ---------- --- -------------------------
    9 /u03/oradata/rcatp/patrol01.dbf                            20 OK  PATROL
    7 /u03/oradata/rcatp/rman10p01.dbf                          466 OK  RMAN10P
    5 /u03/oradata/rcatp/rman11p01.dbf                          200 OK  RMAN11P
    8 /u03/oradata/rcatp/rman9p01.dbf                           106 OK  RMAN9P
    3 /u03/oradata/rcatp/sysaux01.dbf                           540 OK  SYSAUX
    1 /u03/oradata/rcatp/system01.dbf                           700 OK  SYSTEM
    2 /u03/oradata/rcatp/undotbs01.dbf                          220 OK  UNDOTBS1
    4 /u03/oradata/rcatp/users01.dbf                              6 OK  USERS
                                                         ----------
sum                                                            2258

8 rows selected.
 

Comments

Post a Comment

Popular posts from this blog

Oracle Cloud Infrastructure(OCI) - Part2

-
Image
In my earlier post (OCI -Part1 ), we have gone through OCI basic details, let's dive deep more into it. Before that, we need to u nderstand the following concepts and terminology to help you get started with Oracle Cloud Infrastructure.  Note: OCI is re-branding of Bare Metal Cloud Service (BMCS) BARE METAL HOST Oracle Cloud Infrastructure provides you control of the physical host (“bare metal”) machine. Bare metal compute instances run directly on bare metal servers without a hypervisor. When you provision a bare metal compute instance, you maintain sole control of the physical CPU, memory, and network interface card (NIC). You can configure and utilize the full capabilities of each physical machine as if it were hardware running in your own data center. You do not share the physical machine with any other tenants. REGIONS AND AVAILABILITY DOMAINS  Oracle Cloud Infrastructure is physically hosted in regions and availability domains. A region is a localized geographic area, an...
Read more

Script to display the SQL text for a specific SQL_ID

-
--> Purpose:    Script to display the SQL text  for a specific SQL_ID. -->             It returns the SQL_TEXT. --> Parameters: sql_id --> To Run:     @sqltext.sql sql_id --> Example:    @sqltext.sql 27b3qfm5x89xn --> --> Copyright 2020@The Ultimate SQL Tuning Formula set echo off set define '&' set verify off define _sql_id=&1 set verify off set feedback off set linesize 200 set heading on set termout on col sqltext format a100 word_wrapped select  sql_text as sqltext from gv$sqltext where sql_id = '&_sql_id' order by piece; **************************************************************************************** --> Purpose:    Script to display the SQL text  for a specific SQL_ID. -->             It returns the SQL_TEXT. --> Caution:  ...
Read more

How to Change Your EnterpriseOne Environment Color

-
Image
Sometimes users start working and then forget which EnterpriseOne environment they are logged into. Can you tell which environment you are logged into? Luckily, there is a new UDC that allows you to change your EnterpriseOne environment color. You can now set up environments to be color-coded for when you need a clear visual distinction as to which environment you are logged into. This enables you to quickly identify the non-production environments by color. The new UDC is 98/CO and is available in EnterpriseOne Tools Release 9.2.4 with Applications Release 9.1 and 9.2. The image below shows the default EnterpriseOne environment color. However, you can easily change the color to reflect different environments. For example, DV could be red, PY could be green, and PD could be yellow, as shown below. To do so, just add a new row in the User Defined Codes section, enter the EnterpriseOne environment name and the HEX value of the color you want the environment to be, and ...
Read more