Ing Sistemas

afinando el data dictionary cache
gets = # numero de solicitudes para el objeto
getmisses = # numero de solicitudes para el objeto que fueron rechazadas
Objetivo
reducir el valor de rcradio a un valor inferior a 1
tarea:
ajustar el valor del parametro SHARED_POOL_SIZE en el init.ora, incrementandolo en pequeñas
cantidades.
SELECT SUM(GETS) HITS,
SUM(GETMISSES) LIBMISS,SUM(GETMISSES)/SUM(GETS) RCRATIO
FROM V$ROWCACHE ;
Compilar todas las funciones almacenadas
SPOOL compila.sql
SELECT 'ALTER FUNCTION ' || o.owner || '.' || o.object_name || ' COMPILE; '
FROM all_objects o
WHERE o.object_type = 'FUNCTION'
AND o.status = 'INVALID'
AND o.owner = Decode(Upper('&&1'), 'ALL',o.owner, Upper('&&1'));
SPOOL OFF
--ejecutar el script
@compila.sql

Consultar estadísticasacerca de la marca de agua alta - high water
mark
Desde la version 10, el diccionario de datos mantiene una vista con las estadísticas sobre
la marca de agua alta.
COLUMN name FORMAT A40
COLUMN highwater FORMAT 99999999999999
COLUMN last_value FORMAT 99999999999999
SELECT h.name,
h.highwater,
h.last_value
FROM dba_high_water_mark_statistics h
WHERE h.version = (SELECT MAX(h2.version)FROM dba_high_water_mark_statistics h2
WHERE h2.name = h.name);

Consultar las llaves foráneas de una determinada tabla
SELECT c.constraint_name "llave foránea",
p.constaint_name "llave referenciada",
p.constraint_type,
p.owner,
p.table_name
FROM dba_constraints p, dba_constraints c
WHERE c.owner = 'USUARIO1'
AND c.table_name = 'ESTUDIANTES'
AND c.constraint_type = 'R'

ANDp.owner = c.r_owner
AND p.constraint_name = c.r_constraint_name;

Consultar los usuarios conectados a la base de datos
SELECT username,
sid,
serial#
FROM v$session;

Crear una copia exacta de un usuario
rem ----------------------------------------------------------------------rem Filename: cr8like.sql
rem Purpose: Script to create a new user (with privs) like an existing
rem databaseuser. User data will not be copied.
rem Date: 02-Nov-1998
rem Author: Frank Naude, Oracle FAQ
rem Updated: Konstantin Krivosheyev - 7 Dec 2002
rem Updated: Frank Naude - 18 Dec 2003, 2 Dec 2004
rem ----------------------------------------------------------------------set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user to: "
accept newname prompt"Enter new user name: "
-- accept psw prompt "Enter new user's password: "
-- Create user...
select 'create user &&newname identified by values '''||password||''''||
-- select 'create user &&newname identified by &psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username =upper('&&oldname');
-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');
-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');
-- Grant TablePrivs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');
-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs

where grantee = upper('&&oldname');
-- Tablespace Quotas...
select 'alter user '||username||'quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');
-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES'
) loop
if length(defroles) > 0 then
defroles :=...