SQL DIVERSE:

Ruleaza de pe WINMADMIN:
1.Identifica toti copii care blocheaza obiecte de pe skema “iasi”: (vezi filtru where)
select distinct ses.SID,ses.SERIAL#,ses.USERNAME,ses.OSUSER,ses.TERMINAL,ses.PROGRAM,acc.OWNER from 
    v$session ses inner join v$access acc on ses.SID = acc.SID
where acc.OWNER = 'IASI' AND SES.STATUS <> 'KILLED';

2.Ca sa il dai afara pe unu dintre ei faci asa:
alter system kill session 'SID,SERIAL#;
exemplu : alter system kill session '517,1959', unde 517 = SID si 1959 = SERIAL# din SQL precedent.

3.Ca sa vezi ce SQL se executa la un moment dat pe o skema(vezi filtru ses.USERNAME = 'MARIAN')
select s.SQL_TEXT,s.SQL_FULLTEXT 
from v$session ses inner join v$sql s on ses.SQL_ID = s.SQL_ID
where ses.USERNAME = 'MARIAN'; 

Sesiuni care se blocheaza reciproc:
select s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    from v$lock l1, v$session s1, v$lock l2, v$session s2
    where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2 ;
Daca returneaza ceva in seamna ca e nasol, de exemplu:
BLOCKING_STATUS:
--------------------------------------------------------------------------------
TMARIAN@THI\MARIAN ( SID=1152 )  is blocking WINMADMIN@THI\MARIAN ( SID=1716 )

Mai departe ma uit sa vad ce face sesiunea care blocheaza adica aia cu SID=1152
select sql_fulltext from v$session s , v$sql q 
where s.sql_id = q.SQL_ID and s.sid = 1152;   
SQL_FULLTEXT
--------------------------------------------------------------------------------
update test set den = :"SYS_B_0" where cod = :"SYS_B_1"

Daca nu returneaza nimic inseamna ca SQL-ul care blocheaza s-a termin dar asteapta COMMIT. 
Daca returneaza ceva inseamna ca inca se executa .

Poti folosi acelasi select ca sa afli se ruleaza pe o sesiune, daca stii USERNAME poti rula direct asa:
select sql_fulltext 
from v$session s , v$sql q 
where s.sql_id = q.SQL_ID and s.username = 'MARIAN';  

4.Informatii legate de server:
select instance_name,host_name,version,to_char(startup_time,'dd/mm/yyyy hh24:mi:ss') pornit_la from v$instance;

5.Versiunea BD , inclusiv editia (standard, enterprise, etc...)
   select * from v$version;

INTEROGARI UTILE PENTRU A NE PUTEA FACE O IMAGINE ASUPRA DATELOR DE PE O FIRMA
Ruleaza de pe WINMADMIN:
Mai intai ii zicem lu' oracle sa isi actualizez informatiile despre toate obiectele firmei astfel:
execute dbms_stats.gather_schema_stats('IASI');
1.Total  spatiu ocupat pe disk(table + indecsi) 
select sum(bytes)/1024/1024 as MB from dba_segments where owner = 'IASI';

2.Spatiu desfasurat pe tipuri de obiecte (table si indecsi )
select segment_name,segment_type, sum(bytes)/1024/1024 as MB 
          from dba_segments where owner = 'IASI' group by segment_name,segment_type order by   sum(bytes)/1024/1024 ;

3.Informatii detaliate pe tabele . Ne conectam pe skema firmei (in cazu de fata , iasi)
select u.table_name,u.num_rows ,(u.blocks * 8192 )/1024/1024 size_MB,u.avg_row_len
 from user_tables u where u.temporary = 'N'
 order by u.num_rows desc;

Table_name = numele tablei
num_rows  = numarul total de inregistrari
size_MB   = marimea in Mega
avg_row_len = marimea medie a unei inregistrari in bytes

4.Info carnete de documente / luni :
select distinct an,luna,count(*) over (partition by an,luna) DOCUMENTE from intrari order by an, luna;
Informatii si despre alte table de documente, inlocuim intrari cu denumirea tablei in cauza  

5.Pentru table ca intrari,iesiri, transf (cele care tin mai multe tipuri de documente ) putem incerca ceva mai elegant :
select an,luna, 
       case  
       when GROUPING_ID (an,luna,pkg_list_base_utils.decode_tipdoc(tipdoc))  = 0 then
           pkg_list_base_utils.decode_tipdoc(tipdoc)
       when GROUPING_ID (an,luna,pkg_list_base_utils.decode_tipdoc(tipdoc))  = 1 then 
           LPAD('TOTAL LUNA '||luna,length('TOTAL LUNA '||luna)+1,' ')     
       when GROUPING_ID (an,luna,pkg_list_base_utils.decode_tipdoc(tipdoc))  = 3 then     
           LPAD('TOTAL AN ' ||an,length('TOTAL AN ' ||an)+2,' ') 
       when GROUPING_ID (an,luna,pkg_list_base_utils.decode_tipdoc(tipdoc))  = 7 then     
          LPAD('TOTAL GENERAL',length('TOTAL GENERAL')+3,' ')
       else 
         null
      end Obs,      
   sum(DOCUMENTE) nr_documente
 from ( 
     select distinct an,luna,tipdoc,count(*) over (partition by an,luna,tipdoc) DOCUMENTE from intrari order by an,luna)
 group by rollup (an,luna,pkg_list_base_utils.decode_tipdoc(tipdoc));

Inlucuim, in SQL de mai sus intrari cu iesiri si respectiv transf pentru informatii pe alt tip de document.