Dictionary (catálogo)

(1)

(2)

Catálogo

Esquema

: un conjunto de estructuras de datos lógicas (objetos del esquema), propiedad de un usuario

Un esquema contiene, entre otros, los objetos siguientes:



tablas



vistas



índices



secuencias



disparadores



sinónimos



clusters

Referenciar un nombre de un objeto del esquema:

user1.tabla1

El catálogo de Oracle: diccionario de datos (tablas base y vistas)

Los usuarios acceden a las vistas del catálogo:



USER

Lo que el usuario ha creado, o sea, lo que está en su esquema.



ALL

A lo que el usuario tiene acceso, esto es, lo que ha creado y a lo que le han otorgado acceso.



DBA

A lo que el DBA puede acceder, (a lo que todos los usuarios pueden acceder).

(3)

Dictionary (catálogo)



Vista DICTIONARY (sinónimo DICT)

Nombre ¿Nulo? Tipo

--- --- ---

TABLE_NAME

VARCHAR2(30)

(4)

Objects

Column Datatype Description

OWNER VARCHAR2(128) Owner of the OBJECTS definition.

OBJECT_NAME VARCHAR2(128) Name associated with the OBJECTS definition.

OBJECT_TYPE VARCHAR2(128) Type of the object: TABLE, VIEW, INDEX, SEQUENCE, SYNONYM.

CREATED DATE Timestamp for the creation of the OBJECTS. STATUS VARCHAR2(128) Status of the OBJECTS: VALID, INVALID, or N/A

(always valid).

(5)

Catalog

Column

Datatype Description

OWNER VARCHAR2(30)

Owner of the INDEX

,

TABLE

,

CLUSTER

,

VIEW

,

SYNONYM

,

SEQUENCE

TABLE_NAME VARCHAR2(30)

Name of the INDEX

,

TABLE

,

CLUSTER

,

VIEW

,

SYNONYM

,

SEQUENCE

TABLE_TYPE VARCHAR2(11)

Type of the INDEX

,

TABLE

,

CLUSTER

,

VIEW

,

SYNONYM

,

SEQUENCE

(6)

Tables

{DBA|ALL|USER}_TABLES (vista) Selección de algunas columnas

OWNER VARCHAR2(128) User name of the owner of the table.

TABLE_NAME VARCHAR2(128) Name of the table.

TABLESPACE_NAME VARCHAR2(128) Name of the tablespace.

CLUSTER_NAME* VARCHAR2(128) Name of the cluster, if any, to which the table belongs. PCT_FREE* NUMBER(10) Minimum percentage of free space in a block.

PCT_USED* NUMBER(10) Minimum percentage of used space in a block. INI_TRANS* NUMBER(10) Initial number of transactions.

MAX_TRANS* NUMBER(10) Maximum number of transactions. INITIAL_EXTENT* NUMBER(10) Size of the initial extent in bytes. NEXT_EXTENT* NUMBER(10) Size of secondary extents in bytes.

MIN_EXTENTS* NUMBER(10) Minimum number of extents allowed in the segment. MAX_EXTENTS* NUMBER(10) Maximum number of extents allowed in the segment. PCT_INCREASE* NUMBER(10) Percentage increase in extent size.

BACKED_UP* VARCHAR2(1) If the table was backed up since last change. NUM_ROWS* NUMBER(10) Number of rows in the table.

BLOCKS* NUMBER(10) Number of data blocks allocated to the table.

EMPTY_BLOCKS* NUMBER(10) Number of data blocks allocated to the table that contain no data. AVG_SPACE* NUMBER(10) Average amount of free space (in bytes) in a data block allocated to the

table.

(7)

Columnas

{DBA|ALL|USER}_TAB_COLUMNS (vista) Selección de algunas columnas

OWNER VARCHAR2(30) Owner of the table, view, or cluster

TABLE_NAME VARCHAR2(30) Name of the table, view, or cluster

COLUMN_NAME VARCHAR2(30) Column name

DATA_TYPE VARCHAR2(106) Datatype of the column

DATA_TYPE_OWNER VARCHAR2(30) Owner of the datatype of the column

DATA_LENGTH NUMBER Length of the column (in bytes)

DATA_PRECISION NUMBER Decimal precision for NUMBER datatype; binary precision for FLOAT datatype, null for all other datatypes

DATA_SCALE NUMBER Digits to right of decimal point in a number

NULLABLE VARCHAR2(1) Specifies whether a column allows NULLs. Value is N if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY KEY. The constraint should be in an ENABLE VALIDATE state.

(8)

Tables



TABS

sinónimo de USER_TABLES

select object_name, object_type from all_objects where

object_name='TABS';

OBJECT_NAME

OBJECT_TYPE

--- ---

TABS

SYNONYM



TAB

vista (con nuestras tablas también)

TNAME

VARCHAR2(30)

TABTYPE

VARCHAR2(7)

(9)

Views

_{{DBA|ALL|USER}_VIEWS (vista) Selección de algunas columnas}

OWNER VARCHAR2(30) _{Owner of the view} VIEW_NAME VARCHAR2(30) _{Name of the view} TEXT_LENGTH NUMBER _{Length of the view text}

TEXT

LONG View text

TYPE_TEXT_LENGTH NUMBER _{Length of the type clause of the typed view} TYPE_TEXT VARCHAR2(4000) _{Type clause of the typed view}

OID_TEXT_LENGTH NUMBER _{Length of the}WITH OID clause of the typed view

OID_TEXT VARCHAR2(4000) WITH OID clause of the typed view

VIEW_TYPE_OWNER VARCHAR2(30) _{Owner of the type of the view if the view is a typed view}

VIEW_TYPE VARCHAR2(30) _{Type of the view if the view is a typed view} SUPERVIEW_NAME VARCHAR2(30) _{Name of the superview}

El SQLPLUS sólo deja ver unos pocos caracteres de la columna tipo LONG, para ver más:

SET LONG 300 (por ejemplo)

(10)

Índices

{DBA|ALL|USER}_INDEXES (vista) Selección de columnas

OWNER VARCHAR2(30) _{Owner of the index} INDEX_NAME VARCHAR2(30) _{Name of the index} INDEX_TYPE VARCHAR2(27) _{Type of the index:}

TABLE_OWNER VARCHAR2(30) _{Owner of the indexed object} TABLE_NAME VARCHAR2(30) Name of the indexed object

TABLE_TYPE CHAR(5) _{Type of the indexed object (for}

example, TABLE, CLUSTER)

UNIQUENESS VARCHAR2(9) _{Indicates whether the index is}UNIQUE

or NONUNIQUE

COMPRESSION VARCHAR2(8) _{Indicates whether index compression is}

enabled (ENABLED) or not (DISABLED)

(11)

Restricciones

{DBA|ALL|USER}_CONSTRAINTS (vista) Selección de columnas

OWNER VARCHAR2(30) Owner of the constraint definition CONSTRAINT_NAME VARCHAR2(30) Name of the constraint definition CONSTRAINT_TYPE VARCHAR2(1) Type of constraint definition:

• C (check constraint on a table)

• P (primary key)

• U (unique key)

• R (referential integrity)

• V (with check option, on a view)

• O (with read only, on a view)

TABLE_NAME VARCHAR2(30) Name associated with the table (or view) with constraint definition SEARCH_CONDITION LONG Text of search condition for a check constraint

DEFERRABLE VARCHAR2(14) Indicates whether the constraint is deferrable (DEFERRABLE) or not (NOT DEFERRABLE)

DEFERRED VARCHAR2(9) Indicates whether the constraint was initially deferred (DEFERRED) or not (IMMEDIATE)

STATUS VARCHAR2(9) Enforcement status of constraint (ENABLED or DISABLED)

(12)

Restricciones

Recordemos que Oracle permite activar y desactivar las

restricciones, lo que aparece en las columnas STATUS y

VALIDATED:



ENABLE VALIDATE Se comprueba para todas las filas existentes y

nuevas.



ENABLE NOVALIDATE Se comprueba sólo para las filas nuevas.



DISABLE No se comprueba.

El cambio de status:



ALTER TABLE dept1 DISABLE CONSTRAINT NN_DEPT_CTD;



ALTER TABLE dept1 ENABLE NOVALIDATE CONSTRAINT NN_DEPT_CTD;

(13)

Restricciones

Column Datatype NULL Description

OWNER VARCHAR2(30) NOT NULL _{Owner of the constraint definition} CONSTRAINT_NAME VARCHAR2(30) NOT NULL _{Name of the constraint definition}

TABLE_NAME VARCHAR2(30) NOT NULL _{Name of the table with the constraint definition} COLUMN_NAME VARCHAR2(4000) _{Name of the column or attribute of the object type}

column specified in the constraint definition

POSITION NUMBER _{Original position of the column or attribute in the}

definition of the object

(14)

Sinónimos

{DBA|ALL|USER}_SYNONYMS (vista) Selección de columnas

Column

Datatype

Description

OWNER VARCHAR2(30)

_{Owner of the synonym}

SYNONYM_NAME VARCHAR2(30)

_{Name of the synonym}

TABLE_OWNER VARCHAR2(30)

_{Owner of the object referenced by the synonym. Although the}

column is called

TABLE_OWNER

, the object owned is not

necessarily a table. It can be any general object such as a

view, sequence, stored procedure, synonym, and so on

TABLE_NAME VARCHAR2(30)

_{Name of the object referenced by the synonym. Although the}

column is called

TABLE_NAME

, the object does not necessarily

have to be a table. It can be any general object such as a view,

sequence, stored procedure, synonym, and so on.

(15)

Sinónimos

(16)

Usuarios

Column

Datatype

Description

USERNAME VARCHAR2(30)

Name of the user

USER_ID NUMBER

ID number of the user

CREATED DATE

User creation date

{DBA|ALL|USER}_USERS (vista)

¿Quién soy?

SELECT USER

FROM DUAL

(17)

Parámetros de configuración SGBD

NUM NUMBER _{Parameter number}

NAME VARCHAR2(80) _{Name of the parameter}

TYPE NUMBER _{Parameter type:}

• 1 - Boolean • 2 - String • 3 - Integer • 4 - Parameter file • 5 - Reserved • 6 - Big integer

VALUE VARCHAR2(4000) _{Parameter value for the session (if}

modified within the session); otherwise, the instance-wide parameter value

DESCRIPTION VARCHAR2(255) _{Description of the parameter}

… … …

(18)

Datos de la instancia

INSTANCE_NUMBER NUMBER _{Instance number used for}

instance registration

INSTANCE_NAME VARCHAR2(16) _{Name of the instance} HOST_NAME VARCHAR2(64) _{Name of the host machine}

VERSION VARCHAR2(17) _{Database version}

STARTUP_TIME DATE _{Time when the instance was}

started

STATUS VARCHAR2(12) _{Status of the instance}

PARALLEL VARCHAR2(3) _{Indicates whether the instance}

is mounted in cluster database mode (YES) or not (NO)

ARCHIVER VARCHAR2(7) _{Automatic archiving status} DATABASE_STATUS VARCHAR2(17) _{Status of the database:}

• ACTIVE • SUSPENDED

• INSTANCE RECOVERY

… …. ….

(19)

Datos de las sesiones

V$SESSION (Vista) (selección de columnas)

SID NUMBER _{Session identifier}

AUDSID NUMBER _{Auditing session ID}

USERNAME VARCHAR2(30) Oracle username

OSUSER VARCHAR2(30) _{Operating system client user}

name

PROGRAM VARCHAR2(48) _{Operating system program}

name

MODULE VARCHAR2(48) _{Name of the currently}

executing module as set by calling the

DBMS_APPLICATION_INFO.SET _MODULE procedure