Consultas de Administración (DBA Query's) Oracle 11g

CONSULTAS DBA

VERSIÓN 11g

1. Introducción

2. Consultas SQL

1. Introducción

En estos días eh  tenido muchas experiencias como dba sin un entorno gráfico solo sqlplus*  y eh realizado un pequeño diccionario de query's que permiten la rápida administración de una base de datos, cada uno de estos pueden ser optimizado a las necesidades requeridas.

2. Consultas SQL

Nota: Para poder visualizar correctamente se puede dar formato a las columnas de salida en el sqlplus* con

SQL> SET LINESIZE 200

SQL> SET PAGESIZE 200

• Instancia: Instancia a la que se esta conectado.

SQL> select host_name,instance_name,status from v$instance;

HOST_NAME INSTANCE_NAME  STATUS

----------------------------------------------------------------

rfcg1.oracle.com prod  OPEN

• Directorios registrados:  Directorios registrados en la base de datos

SQL> select * from dba_directories;

OWNER       DIRECTORY_NAME                    DIRECTORY_PATH

------------------------------ ------------------------------  ----------------------       

SYS ORACLE_OCM_CONFIG_DIR      /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

SYS       DATA_PUMP_DIR      /u01/app/oracle/admin/prod/dpdump/

SYS       XMLDIR               /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml 

• Registro de usuarios: Listado de usuarios que se conectaron con su numero de veces por día

SQL > SELECT USERNAME,LOGON_TIME,COUNT(*) AS NUMERO 

 FROM V$SESSION 

 WHERE USERNAME IS NOT NULL

--AND LOGON_TIME=SYSDATE

 GROUP BY LOGON_TIME,USERNAME 

 ORDER BY 3 DESC;

USERNAME        LOGON_TIM     NUMERO

------------------------------ --------- ----------

SYSMAN        30-APR-14   3

SYSMAN        01-MAY-14   1

DBSNMP        29-APR-14   1

DBSNMP        29-APR-14   1

SYS                07-MAY-14   1

SYSMAN        30-APR-14   1

SYSMAN        30-APR-14   1

SYSMAN        07-MAY-14   1

DBSNMP        29-APR-14   1

• Tablespace: 

- Espacio disponible en tablespace

SQL> SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024,0) as "MB free" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME  MB free

------------------------------ ----------

SYSAUX       32

UNDOTBS1       22

USERS 3

SYSTEM 3

- Espacio total de tablespace

SQL> SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024,0) as "MB free" FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME  MB free

------------------------------ ----------

UNDOTBS1       30

SYSAUX      530

USERS 5

SYSTEM      700

• Datafile

SQL> SELECT FILE_NAME,BYTES/1024/1024  AS MB FROM DBA_DATA_FILES;

FILE_NAME                                                             MB  

-----------------------------                                     ----------

/u01/app/oracle/oradata/prod/users01.dbf                  5

/u01/app/oracle/oradata/prod/undotbs01.dbf             30

/u01/app/oracle/oradata/prod/sysaux01.dbf              530

/u01/app/oracle/oradata/prod/system01.dbf              700

• Modo de la base de datos: Identificar si la base esta corriendo sobre archivelog o noarchivelog

SQL> SELECT NAME,LOG_MODE FROM V$DATABASE;

NAME  LOG_MODE

--------- ------------

PROD  ARCHIVELOG

• Auditoria: Identificar si la base esta activada la auditoria

SQL> SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME LIKE 'audit_trail';

NAME                          VALUE

----------------------    ----------------------------

audit_trail                       DB

• Controlfile: Identificar la ruta donde se encuentran nuestros controlfile

SQL> show parameter control_files;

NAME       TYPE          VALUE

----------------    -----------    ------------------

control_files         string            /u01/app/oracle/oradata/prod/control01.ctl, 

                                               /u01/app/oracle/fast_recovery_area/prod/control02.ctl

• Fast recovery Area (FRA): Tamaño de la FRA

SQL>SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE;

NAME        TYPE            VALUE

----------------------------    -----------      ----------------------

db_recovery_file_dest_size big integer     4122M

• Tamaño actual de la base de datos

SQL> SELECT SUM(BYTES)/1024/1024 MB FROM DBA_SEGMENTS;

MB

----------

    1201.5

• Esquemas creados

SQL> SELECT USERNAME FROM DBA_USERS;

USERNAME

------------------------------

SYS

SYSTEM

DBVISIT7

OUTLN

MGMT_VIEW

FLOWS_FILES

MDSYS

ORDSYS

EXFSYS

DBSNMP

WMSYS

APPQOSSYS

APEX_030200

OWBSYS_AUDIT

ORDDATA

CTXSYS

ANONYMOUS

SYSMAN

XDB

ORDPLUGINS

OWBSYS

SI_INFORMTN_SCHEMA

OLAPSYS

SCOTT

ORACLE_OCM

XS$NULL

MDDATA

DIP

APEX_PUBLIC_USER

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

• Tablas con propietario especifico

SQL> SELECT OWNER,OBJECT_NAME FROM DBA_OBJECTS WHERE ROWNUM < 11;

OWNER                                             OBJECT_NAME
------------------------------      --------------------------------------------------
SYS                                          ICOL$
SYS                                          I_USER1
SYS                                          CON$
SYS                                          UNDO$
SYS                                          C_COBJ#
SYS                                          I_OBJ#
SYS                                          PROXY_ROLE_DATA$
SYS                                          I_IND1
SYS                                          I_CDEF2
SYS                                          I_OBJ5