Howto listener de Oracle

Para que desde fuera del servidor donde está instalada la base de datos se pueda acceder a la misma el servicio denominado listener ha de estar activado, o como se suele decir, ha de estar escuchando.
Puede pasar que la base de datos esté correctamente abierta y no se pueda conectar desde otros servidores o PCs, que también están correctamente configurados (TNSNAMES correcto, etc.).

En estos casos puede ser que el listener tenga algún problema, o simplemente que no haya sido iniciado. En ese caso tan sólo habría que arrancar el listener.

Consultar el estado del Listener, arrancarlo o pararlo es sencillo. Sólo hay que abrir una sesión de línea de comandos (consola, terminal, etc.) con el usuario con el que se ha instalado la base de datos, y ejecutar el comando lsnrctl con los siguientes parámetros para cada caso:

Comprobar su estado:  > lsnrctl status
Parar el listener:           > lsnrctl stop
Iniciar el listener:         > lsnrctl start

NOTA:
Hay que tener en cuenta que cuando se para el listener no se cierran las conexiones que ya están dentro de la base de datos, por lo que una parada momentánea no suele ser muy traumática, sólo se rechazarán las conexiones que intenten entrar mientras el listener esté parado, no ha de afectar a nadie que tenga ya una sesión abierta.

Consultas útiles para Oracle Database Recopilación de Scripts

 Consultas útiles para Oracle Database

Recopilación de Scripts

•• Consulta Oracle SQL sobre la vista que muestra el estado de la base de datos:

select * from v$instance;

•• Consulta Oracle SQL que muestra si la base de datos está abierta:

select status from v$instance;

•• Consulta Oracle SQL sobre la vista que muestra los parámetros generales de Oracle:

select * from v$system_parameter

•• Consulta Oracle SQL para conocer la Versión de Oracle:

select value from v$system_parameter where name = 'compatible';

•• Consulta Oracle SQL para conocer la Ubicación y nombre del fichero spfile:

select value from v$system_parameter where name = 'spfile';

•• Consulta Oracle SQL para conocer la Ubicación y número de ficheros de control:

select value from v$system_parameter where name = 'control_files';

•• Consulta Oracle SQL para conocer el Nombre de la base de datos:

select value from v$system_parameter where name = 'db_name';

•• Consulta Oracle SQL sobre la vista que muestra las conexiones actuales a Oracle

   Para visualizarla es necesario entrar con privilegios de administrador:

select osuser, username, machine, program

from v$session

order by osuser;

•• Consulta Oracle SQL que muestra el número de conexiones actuales a Oracle

   agrupado por aplicación que realiza la conexión:

select program Aplicacion, count(program) Numero_Sesiones

from v$session

group by program

order by Numero_Sesiones desc;

•• Consulta Oracle SQL que muestra los usuarios de Oracle conectados y el número de sesiones por usuario:

select username Usuario_Oracle, count(username) Numero_Sesiones

from v$session

group by username

order by Numero_Sesiones desc;

•• Consulta Oracle SQL que muestra propietarios de objetos y número de objetos por propietario:

select owner, count(owner) Numero

from dba_objects

group by owner;

•• Consulta Oracle SQL sobre el Diccionario de datos (incluye todas las vistas y tablas de la Base de Datos):

select * from dictionary;

•• Consulta Oracle SQL que muestra los datos de una tabla especificada (en este caso todas las tablas que lleven la cadena "XXX"):

select * from ALL_ALL_TABLES where upper(table_name) like '%XXX%';

•• Consulta Oracle SQL que muestra las descripciones de los campos de una tabla especificada

   (en este caso todas las tablas que lleven la cadena "XXX"):

select * from ALL_COL_COMMENTS where upper(table_name) like '%XXX%';

•• Consulta Oracle SQL para conocer las tablas propiedad del usuario actual:

select * from user_tables;

•• Consulta Oracle SQL para conocer todos los objetos propiedad del usuario conectado a Oracle:

select * from user_catalog;

•• Consulta Oracle SQL para el DBA de Oracle que muestra los tablespaces, el espacio utilizado, el espacio libre y los ficheros de datos de los mismos:

Select t.tablespace_name "Tablespace", t.status "Estado",

ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamaño",

ROUND((MAX(d.bytes)/1024/1024) -

(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",

ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres",

t.pct_increase "% incremento",

SUBSTR(d.file_name,1,80) "Fichero de datos"

FROM DBA_FREE_SPACE f, DBA_DATA_FILES d, DBA_TABLESPACES t

WHERE t.tablespace_name = d.tablespace_name AND

f.tablespace_name(+) = d.tablespace_name

AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,

d.file_name, t.pct_increase, t.status ORDER BY 1,3 DESC:

•• Consulta Oracle SQL para conocer los productos Oracle instalados y la versión:

select * from product_component_version;

•• Consulta Oracle SQL para conocer los roles y privilegios por roles:

select * from role_sys_privs;

•• Consulta Oracle SQL para conocer las reglas de integridad y columna a la que afectan:

select constraint_name, column_name from sys.all_cons_columns;

•• Consulta Oracle SQL para conocer las tablas de las que es propietario un usuario, en este caso "xxx":

SELECT table_owner, table_name from sys.all_synonyms where table_owner like 'xxx';

•• Consulta Oracle SQL como la anterior, pero de otra forma más efectiva  (tablas de las que es propietario un usuario);

SELECT DISTINCT TABLE_NAME

FROM ALL_ALL_TABLES

WHERE OWNER LIKE 'HR';

•• Parámetros de Oracle, valor actual y su descripción:

SELECT v.name, v.value value, decode(ISSYS_MODIFIABLE, 'DEFERRED',

'TRUE', 'FALSE') ISSYS_MODIFIABLE, decode(v.isDefault, 'TRUE', 'YES',

'FALSE', 'NO') "DEFAULT", DECODE(ISSES_MODIFIABLE, 'IMMEDIATE',

'YES','FALSE', 'NO', 'DEFERRED', 'NO', 'YES') SES_MODIFIABLE,

DECODE(ISSYS_MODIFIABLE, 'IMMEDIATE', 'YES', 'FALSE', 'NO',

'DEFERRED', 'YES','YES') SYS_MODIFIABLE , v.description

FROM V$PARAMETER v

WHERE name not like 'nls%' ORDER BY 1;

•• Consulta Oracle SQL que muestra los usuarios de Oracle y datos suyos (fecha de creación, estado, id, nombre, tablespace temporal,...):

Select * FROM dba_users;

•• Consulta Oracle SQL para conocer tablespaces y propietarios de los mismos:

select owner, decode(partition_name, null, segment_name,

segment_name || ':' || partition_name) name,

segment_type, tablespace_name,bytes,initial_extent,

next_extent, PCT_INCREASE, extents, max_extents

from dba_segments

Where 1=1 And extents > 1 order by 9 desc, 3;

•• Últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó:

select distinct vs.sql_text, vs.sharable_mem,

vs.persistent_mem, vs.runtime_mem, vs.sorts,

vs.executions, vs.parse_calls, vs.module,

vs.buffer_gets, vs.disk_reads, vs.version_count,

vs.users_opening, vs.loads,

to_char(to_date(vs.first_load_time,

'YYYY-MM-DD/HH24:MI:SS'),'MM/DD HH24:MI:SS') first_load_time,

rawtohex(vs.address) address, vs.hash_value hash_value ,

rows_processed , vs.command_type, vs.parsing_user_id ,

OPTIMIZER_MODE , au.USERNAME parseuser

from v$sqlarea vs , all_users au

where (parsing_user_id != 0) AND

(au.user_id(+)=vs.parsing_user_id)

and (executions >= 1) order by buffer_gets/executions desc;

•• Consulta Oracle SQL para conocer todos los tablespaces:

select * from V$TABLESPACE;

•• Consulta Oracle SQL para conocer la memoria Share_Pool libre y usada:

select name,to_number(value) bytes

from v$parameter where name ='shared_pool_size'

union all

select name,bytes

from v$sgastat where pool = 'shared pool' and name = 'free memory';

•• Cursores abiertos por usuario:

select b.sid, a.username, b.value Cursores_Abiertos

from v$session a,

v$sesstat b,

v$statname c

where c.name in ('opened cursors current')

and b.statistic# = c.statistic#

and a.sid = b.sid

and a.username is not null

and b.value >0

order by 3;

•• Consulta Oracle SQL para conocer los aciertos de la caché (no debería superar el 1 por ciento):

select sum(pins) Ejecuciones, sum(reloads) Fallos_cache,

trunc(sum(reloads)/sum(pins)*100,2) Porcentaje_aciertos

from v$librarycache

where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');

•• Sentencias SQL completas ejecutadas con un texto determinado en el SQL:

SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text

FROM v$session c, v$sqltext d

WHERE c.sql_hash_value = d.hash_value

and upper(d.sql_text) like '%WHERE CAMPO LIKE%'

ORDER BY c.sid, d.piece;

•• Una sentencia SQL concreta (filtrado por sid):

SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text

FROM v$session c, v$sqltext d

WHERE c.sql_hash_value = d.hash_value

and sid = 105

ORDER BY c.sid, d.piece;

•• Consulta Oracle SQL para conocer el tamaño ocupado por la base de datos:

select sum(BYTES)/1024/1024 MB,sum(BYTES)/1024/1024/1024 GB from DBA_EXTENTS;

•• Consulta Oracle SQL para conocer el tamaño de los ficheros de datos de la base de datos:

select sum(bytes)/1024/1024 MB,sum(bytes)/1024/1024/1024 GB from dba_data_files;

•• Consulta Oracle SQL para conocer el tamaño ocupado por una tabla concreta sin incluir los índices de la misma:

select sum(bytes)/1024/1024 MB from user_segments

where segment_type='TABLE' and segment_name='NOMBRETABLA';

•• Consulta Oracle SQL para conocer el tamaño ocupado por una tabla concreta incluyendo los índices de la misma:

select sum(bytes)/1024/1024 Table_Allocation_MB from user_segments

where segment_type in ('TABLE','INDEX') and

(segment_name='NOMBRETABLA' or segment_name in

(select index_name from user_indexes where table_name='NOMBRETABLA'));

•• Consulta Oracle SQL para conocer el tamaño ocupado por una columna de una tabla:

select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB from NOMBRETABLA

•• Consulta Oracle SQL para conocer el espacio ocupado por los usuario:

SELECT owner, SUM(BYTES)/1024/1024 MB, SUM(BYTES)/1024/1024 GB FROM DBA_EXTENTS

group by owner;

•• Consulta Oracle SQL para conocer el espacio ocupado por un usuario:

SELECT owner, SUM(BYTES)/1024/1024 MB, SUM(BYTES)/1024/1024 GB FROM DBA_EXTENTS where owner='usuario';

•• Consulta Oracle SQL para conocer el espacio ocupado por los diferentes segmentos

   (tablas, índices, undo, rollback, cluster, ...):

SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS

group by SEGMENT_TYPE;

•• Consulta Oracle SQL para obtener todas las funciones de Oracle: NVL, ABS, LTRIM, ...

SELECT distinct object_name

FROM all_arguments

WHERE package_name = 'STANDARD'

order by object_name;

•• Consulta Oracle SQL para conocer el espacio ocupado por todos los objetos de la base de datos,  muestra los objetos que más ocupan primero:

SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 MB,SUM(BYTES)/1024/1024/1024 GB FROM DBA_EXTENTS

group by SEGMENT_NAME

order by 2 desc;