Oracle diccionario de datos data dictionary y consultas utiles

esta son unas consultas bastante utiles para aumentar y rastrear el rendimiento en ORALE como dijo Diego!! ejejejejeje... espero que tambien les sirva, en el centro se encuentra una consiguración de parametros de oracle para aumentar el rendimiento.


select program Aplicacion, count(program) Numero_Sesiones
from v$session
group by program
order by Numero_Sesiones desc
-- agrupadas por maquina
select program Aplicacion,MACHINE, count(program) Numero_Sesiones
from v$session
group by program,MACHINE
order by Numero_Sesiones desc

-- ultimas consultas ejecutadas por un usuario de una maquina especifica
select s.program Aplicacion, s.MACHINE, s.TERMINAL,vs.CPU_TIME , vs.runtime_mem,vs.EXECUTIONS,vs.SQL_FULLTEXT, vs.sharable_mem,
  vs.persistent_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, v$session s  
where (parsing_user_id != 0)  AND
(au.user_id(+)=vs.parsing_user_id)
AND au.USERNAME like 'HABITATSMS'
AND au.USERNAME like s.USERNAME
AND s.MACHINE like'JUBILO'
and (vs.executions >= 1) order by   vs.CPU_TIME, vs.executions desc

-- modificada menos parametros que la anterior -- ultimas consultas ejecutadas por un usuario de una maquina especifica
select s.program Aplicacion, s.MACHINE, s.TERMINAL,vs.CPU_TIME , vs.runtime_mem,vs.EXECUTIONS,vs.SQL_FULLTEXT||' ||| ',vs.SQL_TEXT||' ||| ', 
  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.parsing_user_id , au.USERNAME parseuser 
from v$sqlarea vs , all_users au, v$session s  
where (parsing_user_id != 0)  AND
(au.user_id(+)=vs.parsing_user_id)
AND au.USERNAME like 'HABITATSMS'
AND au.USERNAME like s.USERNAME
AND s.MACHINE like'JUBILO'
AND to_char(to_date(vs.first_load_time,
  'YYYY-MM-DD/HH24:MI:SS'),'MM/DD') like '06/28'
and (vs.executions >= 1) order by   first_load_time,vs.CPU_TIME, vs.executions desc

desc v$sqlarea
desc all_users
desc v$session
-- consulta para traza y optimizar .NET


-- pruebas
SELECT  instance_name,
        DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM    sys.v_$parameter ,
        v$instance
WHERE   name = 'spfile';
-- cantidad maximo de sessiones que se pueden tener activas
SELECT name, value
  FROM v$parameter
 WHERE name = 'sessions'
--
SELECT
  'Currently, '
  || (SELECT COUNT(*) FROM V$SESSION)
  || ' out of '
  || DECODE(VL.SESSIONS_MAX,0,'unlimited',VL.SESSIONS_MAX)
  || ' connections are used.' AS USAGE_MESSAGE
FROM
  V$LICENSE VL

select program Aplicacion, MACHINE, TERMINAL, PROGRAM, PROCESS, OSUSER
from v$session
order by Aplicacion, MACHINE desc
-- RESOURCE_CONSUMER_GROUP, MACHINE, TERMINAL, PROGRAM,
desc v$session

select owner||'.'||OBJECT_NAME as object_list
  from dba_objects
  where owner in ('HABITAT', 'GENERAL_COMMONS')
  order by owner

--aumentar maximo de conexiones ORACLE

alter system set processes = 1000 scope = spfile;
alter system set sessions = 1000 scope = spfile;
alter system set transactions = 1500 scope = spfile;


select TABLE_NAME,TABLE_TYPE, OWNER from SYS.ALL_TABLES where owner in ('HABITAT', 'GENERAL_COMMONS')
   order by OWNER, TABLE_NAME
--consulta que obtiene el nombre de todos los objetos de un usuario en la base de datos
SELECT object_list, OBJECT_TYPE, owner FROM (
select owner||'.'||OBJECT_NAME as object_list, OBJECT_TYPE, owner
  from dba_objects
  where owner in ('HABITAT', 'GENERAL_COMMONS', 'HABITATSMS')
  --order by owner
  UNION
select owner||'.'||SYNONYM_NAME as object_list, 'SYNONYM' OBJECT_TYPE, owner
  from ALL_SYNONYMS
  where owner in ('HABITAT', 'GENERAL_COMMONS', 'HABITATSMS')
  --order by owner
  )
  ORDER BY owner
 

  desc dba_tables
  desc dba_objects
  desc all_tables
desc user_tables
desc ALL_SYNONYMS

desc dba_objects
--propietarios y obajetos
select owner||'.'||OBJECT_NAME as object_list
  from dba_objects
  where owner in ('HABITAT', 'GENERAL_COMMONS')
  order by owner
 
  select * from user_catalog
--cantidad de objetos por usuario
select owner, count(owner) Numero
  from dba_objects
  group by owner
  order by Numero desc
 
 
  ---------------------------------------------------------
  select s.program Aplicacion, s.MACHINE, s.TERMINAL,vs.CPU_TIME , vs.runtime_mem,vs.EXECUTIONS,vs.SQL_FULLTEXT, vs.sharable_mem,
  vs.persistent_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, v$session s  
where (parsing_user_id != 0)  AND
(au.user_id(+)=vs.parsing_user_id)
AND au.USERNAME IN ('HABITATSMS' , 'GENERAL_COMMONS')
AND au.USERNAME like s.USERNAME
AND s.MACHINE IN('JUBILO','WARPATH')
AND vs.module IN ('HABITATSMS','ROOT-1-%','Axesnet.HabitatEnterprise.WindowsService.Habitat','JDBC Thin Client','QTAgent32.exe')
and (vs.executions >= 1) order by   vs.runtime_mem desc



--selecciona los bloqueos si existen
select used_ublk from v$transaction;

select username, status from v$session where username IN ('HABITATSMS' , 'GENERAL_COMMONS')

select * from v$transaction;



desc v$session
-- selecciona las conexiones y su estado con usuario diferente a oracle
SELECT sid||','||serial# username,
          status,
          MODULE,
          osuser,
          client_info,
          MACHINE,
          TO_CHAR(logon_time,'DD-MON-YY HH:MI:SSSS') loged_on
   FROM v$session where OSUSER!='oracle'
  
   order by MACHINE
  

SELECT sid||','||serial# username,
         
   FROM v$session where OSUSER!='oracle'
   where module like 'JDBC Thin Client'

-- comando para matar las sessiones
alter system kill session '25,1207';
alter system kill session '27,494';
alter system kill session '35,4668';
alter system kill session '771,4703';
alter system kill session '778,4548';
alter system kill session '793,8510';
alter system kill session '796,3188';
alter system kill session '798,618';
--seleccioona los identificadores  de jdbc para poder darles kill
SELECT sid||','||serial# username
         
   FROM v$session where OSUSER!='oracle'
   AND module like 'JDBC Thin Client';



SELECT 'GRANT SELECT, UPDATE, INSERT, DELETE ON GENERAL_COMMONS."'||object_name||'" TO USUARIO_CRUD;' FROM all_objects WHERE owner='DUENO' AND object_type IN ('TABLE','VIEW')
SELECT 'GRANT SELECT, UPDATE, INSERT, DELETE ON DUENO."'||object_name||'" TO USUARIO_CRUD;' FROM all_objects WHERE owner='DUENO' AND object_type IN ('TABLE','VIEW')

SELECT 'GRANT SELECT ON GENERAL_COMMONS."'||object_name||'" TO USUARIO_READ;' FROM all_objects WHERE owner='DUENO' AND object_type IN ('TABLE','VIEW')
SELECT 'GRANT SELECT ON DUENO."'||object_name||'" TO USUARIO_READ;' FROM all_objects WHERE owner='DUENO' AND object_type IN ('TABLE','VIEW')