Dataprix

Qui no té la seva costella de consultillas útils que se solen utilitzar en el dia a dia, i en les nostres aventures i desaventures amb la base de dades?
Incloc en aquest primer post un llistat de consultes, la majoria sobre les vistes del diccionari d'Oracle, i extretes de la Cibermanuales.com, i animo a tot el que vulgui afegir-ne altres que consideri d'utilitat a respondre el post publicant les seves, a veure si entre tots creem un reposador que puguem consultar des de qualsevol lloc per facilitar-nos la vida, o treure'ns d'algun que un altre conflicte.

 

.. Consulta Oracle SQL sobre la vista que mostra l'estat de la base de dades:
select * from v$instance

.. Consulta Oracle SQL que mostra si la base de dades està oberta
select estatus from v$instance

.. Consulta Oracle SQL sobre la vista que mostra els paràmetres generals d'Oracle
select * from v$system_parameter

.. Consulta Oracle SQL per conèixer la Versió d'Oracle
select value from v$system_parameter where name = 'compatible'

.. Consulta Oracle SQL per conèixer la Ubicació i nom del fitxer spfile
select value from v$system_parameter where name = 'spfile'

.. Consulta Oracle SQL per conèixer la Ubicació i nombre de fitxers de control
select value from v$system_parameter where name = 'control_files'

.. Consulta Oracle SQL per conèixer el Nom de la base de dades
select value from v$system_parameter where name = 'db_name'

.. Consulta Oracle SQL sobre la vista que mostra les connexions actuals a Oracle Para visualitzar-la és necessari entrar amb privilegis d'administrador
select osuser, username, machine, program
from v$session
order by osuser

.. Consulta Oracle SQL que mostra el nombre de connexions actuals a Oracle agrupat per aplicació que realitza la connexió
select program Aplicacion, count(program) Numero_Sesiones
from v$session
group by program
order by Numero_Sesiones desc

.. Consulta Oracle SQL que mostra els usuaris d'Oracle connectats i el nombre de sessions per usuari
select username Usuario_Oracle, count(username) Numero_Sesiones
from v$session
group by username
order by Numero_Sesiones desc
Propietaris d'objectes i nombre d'objectes per propietari
select owner, count(owner) Numero
from dba_objects
group by owner
order by Numero desc

.. Consulta Oracle SQL sobre el Diccionari de dades (inclou totes les vistes i taules|posts de la Base de Dades)
select * from dictionary

.. Consulta Oracle SQL que mostra les dades d'una taula|post especificada (en aquest cas totes les taules|posts que portin la cadena "XXX"
select * from ALL_ALL_TABLES where upper(table_name) like '%XXX%'

.. Consulta Oracle SQL per conèixer les taules|posts propietat de l'usuari actual
select * from user_tables

.. Consulta Oracle SQL per conèixer tots els objectes propietat de l'usuari connectat a Oracle
select * from user_catalog

.. Consulta Oracle SQL per al DBA d'Oracle que mostra els tablespaces, l'espai utilitzat, l'espai lliure i els fitxers de dades dels mateixos:
Select t.tablespace_name "Tablespace", t.status "Estat",
ROUND(MAX(d.bytes)/1024/1024,2) " MB Mida",
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 Lliures",
t.pct_increase "% increment",
SUBSTR(d.file_name,1,80) "Fitxer de dades"
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 per conèixer els productes Oracle instal·lats i la versió:
select * from product_component_version

.. Consulta Oracle SQL per conèixer els rols i privilegis per rols:
select * from role_sys_privs

.. Consulta Oracle SQL per conèixer les regles d'integritat i columna que afecten:
select constraint_name, column_name from sys.all_cons_columns.. Consulta Oracle SQL per conèixer les taules|posts de les quals és propietari un usuari, en aquest cas "xxx":
SELECT table_owner, table_name from sys.all_synonyms where table_owner like 'xxx'

.. Consulta Oracle SQL com l'anterior, però d'una altra forma més efectiva (taules|posts de les quals és propietari un usuari):
SELECT DISTINCT TABLE_NAME
FROM ALL_ALL_TABLES
WHERE OWNER LIKE 'HR'
Paràmetres d'Oracle, valor actual i la seva descripció:
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 mostra els usuaris d'Oracle i dades seves (data de creació, estat, aneu, nom, tablespace temporal...):
Select * FROM dba_users

.. Consulta Oracle SQL per conèixer tablespaces i propietaris dels mateixos:
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
Últimes consultes SQL executades a Oracle i usuari que les va executar:
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 per conèixer tots els tablespaces:
select * from V$TABLESPACE.. Consulta Oracle SQL per conèixer la memòria Share_Pool lliure i 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

'

 

Cursors oberts per usuari

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 per conèixer els encerts de la

caché

(no hauria de superar l'1 per cent)

select

sum(pins) Execucions, 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

');

 

Sentències SQL completes executades amb un text determinat 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 CAMP LIKE%'

ORDER BY

c.sid, d.piece

 

Una sentència SQL concreta (filtrat per

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 per conèixer la mida ocupada per la base de dades

select

sum(BYTES)/1024/1024

MB from

DBA_EXTENTS

 

.. Consulta Oracle SQL per conèixer la mida dels fitxers de dades de la base de dades

select

sum(bytes)/1024/1024

MB from

dba_data_files

 

.. Consulta Oracle SQL per conèixer la mida ocupada per una

taula|post

concreta sense incloure els índexs de la mateixa

select

sum(bytes)/1024/1024

MB from

user_segments

where

segment_type='TABLE'

and

segment_name='NOMBRETABLA'

 

.. Consulta Oracle SQL per conèixer la mida ocupada per una

taula|post

concreta incloent els índexs de la mateixa

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 per conèixer la mida ocupada per una columna d'una

taula|post
select

sum(vsize('NOMBRECOLUMNA'))/1024/1024

MB from NOMBRETABLA

.. Consulta Oracle SQL per conèixer l'espai ocupat per usuari

SELECT owner

, SUM(BYTES)/1024/1024

MB FROM

DBA_EXTENTS

group by owner

.. Consulta Oracle SQL per conèixer l'espai ocupat pels diferents segments (

taules|posts

, índexs,

undo

,

rollback

,

clúster|grup de sectors

...)

SELECT

SEGMENT_TYPE, SUM(BYTES)/1024/1024

MB FROM

DBA_EXTENTS

group by

SEGMENT_TYPE

 

.. Consulta Oracle SQL per obtenir totes les funcions d'Oracle:

NVL

,

ABS

,

LTRIM

...

SELECT distinct

object_name

FROM

all_arguments

WHERE

package_name = 'ESTÀNDARD'

order by

object_name

 

.. Consulta Oracle SQL per conèixer l'espai ocupat per tots els objectes de la base de dades, mostra els objectes que més ocupen primer

SELECT

SEGMENT_NAME, SUM(BYTES)/1024/1024

MB FROM

DBA_EXTENTS

group by

SEGMENT_NAME

order by

2

desc
PR

:

wait

...

 

I:

wait

...

 

L:

wait

...

LD

:

wait

...

 

I:

wait

...

wait

...

SD

:

wait

...