--ESPACIO LIBRE EN TABLESPACE select [Link] tablespace, [Link]/1024/1024 "total MB", ([Link]-u.
libre)/1024/10 24 "Ocupado MB" from (select sum(bytes) total,tablespace_name tbt from dba_data_files group by tablespace_name) t,(select sum(bytes) libre, tables pace_name tbu from dba_free_space group by tablespace_name) u where [Link]=[Link] / --AUMENTAR TAMA O DE TABLESPACE ALTER DATABASE DATAFILE 'C:\ORACLEXE\ORADATA\XE\[Link]' RESIZE 3725M / --DATA FILES select FILE_NAME from dba_data_files / --TABLAS select * from dba_tables where owner = 'PORTAL' / --constraints que referencian a una tabla select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_nam e from all_constraints where constraint_type='R' and r_constraint_name in (select constraint_name from all_constraints where constraint_type in ('P','U') and table_name='PORTAL_INTERMEDIARIOS'); / --TAMA O DE LAS TABLAS select t.table_name, sum(bytes)/(1024*1024) MB from dba_tables T, dba_segments S where s.segment_name = t.table_name AND [Link] = 'SLABI' and t.table_name not like 'OWB%' group by t.table_name order by 2 desc, 1 / --SESIONES EN LA BBDD select sid, serial#, username, osuser, machine, program from v$session where osuser = 'leala' / --MATAR UNA SESION ALTER SYSTEM KILL SESSION '358,72' immediate; /
--MATAR SESIONES DE UN USUARIO select 'alter system kill session ' chr(39) [Link] ',' [Link]# chr(39) ' immediate;' from v$process p, v$session s where --[Link]= '&1' [Link] = [Link] and [Link] = 'APEX_PUBLIC_USER' order by spid /
select [Link], [Link], [Link] ',' [Link]# "SID,SERIAL", [Link] "Client", [Link] "Server", [Link], [Link], [Link], s.sql_address, s.sql_hash_value from v$process p, v$session s where [Link] = [Link] order by status / --OBJETOS BLOQUEADOS select --'alter system kill session ' chr(39) [Link] ',' [Link]# chr(39) ' immediate;' [Link], c.object_name, c.object_type, [Link], [Link]#, [Link], [Link], [Link] from v$locked_object a , v$session s, dba_objects c where [Link] = a.session_id and a.object_id = c.object_id --and object_name = 'COT_FINANCIAMIENTO' / --##COMPACTAR TABLAS --Mover tablas a otro TS SELECT 'alter table ANDES.' table_name ' ' 'move tablespace BASURA storage (initial 100K);' from dba_tables where owner='ANDES' AND tablespace_name='T_ANDES' --Mover de vuelta las tablas al TS original SELECT 'alter table' owner '.' table_name ' ' 'move
tablespace T_ANDES storage (initial 100K);' from dba_tables where owner='ANDES' AND tablespace_name='T_ANDES' --Reconstruir indices select 'alter index ' table_owner '.' index_name ' rebuild nologging tablespace ' tablespace_name ' online;' from DBA_indexes WHERE tablespace_name='T_ANDES' / --Fragmentacin de TS, a mayor % de indice de fragmentacion menor select tablespace_name, count(*) free_chunks, decode( round((max(bytes) / 1024000),2), null,0, round((max(bytes) / 1024000),2)) largest_chunk, 100 - nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) ) ),2), 0) fragmentation_index from sys.dba_free_space group by tablespace_name order by 2 desc, 1; / --COMPILAR OBJETOS INVALIDOS SET SERVEROUTPUT ON SIZE 1000000 BEGIN FOR cur_rec IN (SELECT owner, object_name, object_type, DECODE(object_type, 'PACKAGE', 1, 'PACKAGE BODY', 2, 2) AS recompile_ order FROM dba_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY') AND status != 'VALID' ORDER BY 4) LOOP BEGIN IF cur_rec.object_type = 'PACKAGE' THEN EXECUTE IMMEDIATE 'ALTER ' cur_rec.object_type ' "' cur_rec.owner '"."' cur_rec.object_name '" COMPILE' ; ElSE EXECUTE IMMEDIATE 'ALTER PACKAGE "' cur_rec.owner '"."' cur_rec.object_name '" COMPILE BODY'; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(cur_rec.object_type ' : ' cur_rec.owner ' : ' cur_rec.object_name); END;
END LOOP; END; / --CONSULTAS SQL DE UNA SESION SELECT sq.sql_text, [Link], [Link], [Link], [Link]#, [Link] FROM v$session s, v$sqltext_with_newlines sq, v$process p WHERE --[Link] = 159 --AND [Link]# = 24488 s.sql_address = [Link] AND --[Link] = 'SRV-BI' AND s.sql_hash_value = sq.hash_value and [Link] = [Link] ORDER BY [Link], [Link]#, [Link] / --PGA --Estadsticas de rendimiento select name,value --/1024/1024 from v$pgastat where name in ('aggregate PGA target parameter', 'aggregate PGA auto target', 'total PGA inuse', 'total PGA allocated', 'over allocation count', 'extra bytes read/written', 'cache hit percentage'); /