tutorailwale How To Find Database Objects With Datafiles? SELECT DISTINCT t.ts#,t.name AS Tablespace_name,d.name AS DATAFILE,s.owner,s.segment_name,s.segment_type,s.bytes / 1024 / 1024 AS “MB”FROM v$tablespace t,v$datafile d,dba_segments sWHERE t.ts# … Read more
How To Move a Table To a Different TableSpace? SYNTAXALTER TABLE table_name move TABLESPACE tablespace_name (INITIAL=64k MINEXTENTS=1 MAXEXTENTS=UNLIMITED); EXAMPLEALTER TABLE hr.employee_details move TABLESPACE hr; OR … Read more
To Find Total Undo Statistics ALTER SESSION SET nls_date_format=’dd-mon-yy hh24:mi’; set lines 120set pages 2000SELECT begin_time,end_time,undoblks,txncount,maxquerylen,unxpstealcnt,expstealcnt,ssolderrcnt,nospaceerrcntFROM v$undostat; Total Undo Statistics since specified year SELECT ‘TOTAL … Read more
List All Undo Datafiles With Status And Size show parameter undo show parameter db_block_size Undo TablespaceSELECT tablespace_nameFROM dba_tablespacesWHERE tablespace_name LIKE ‘%UNDO%’; Total undo spaceSELECT SUM(bytes) … Read more
To Check When a Table is Last Analysed SELECT owner,table_name,tablespace_name,status,last_analyzed,partitioned,dependencies,droppedFROM dba_tablesWHERE table_name LIKE ‘YourTableName‘; SELECT owner,table_name,tablespace_name,last_analyzed,partitioned,dependencies,droppedFROM dba_tablesWHERE table_name LIKE ‘YourTableName‘; Read More Articles List All … Read more
List All Datafile Of Undo Tablespace SELECT tablespace_name,file_name,file_id,autoextensible,bytes / 1048576 Mbytes,maxbytes / 1048576 maxMbytesFROM dba_data_filesWHERE tablespace_name LIKE ‘%UNDO%’OR tablespace_name LIKE ‘%RBS%’ORDER BY tablespace_name,file_name; Read More … Read more
Undo Space Used By All Transactions set lines 200col sid FOR 99999col username FOR a10col name FOR a15SELECT s.sid,s.serial#,username,s.machine,t.used_ublk,t.used_urec,rn.name,( t.used_ublk * 8 ) / 1024 … Read more
List of long running queries set head OFFSELECT ‘LIST OF LONG RUNNING – QUERY SINCE INSTANCE STARTUP’FROM dual; set head ONSELECT *FROM (SELECT To_char(begin_time, ‘DD-MON-YY … Read more
Undo Tablespace Utilization By All Sessions set lines 200col sid FOR 99999col username FOR a10col name FOR a15SELECT s.sid,s.serial#,username,s.machine,t.used_ublk,t.used_urec,rn.name,( t.used_ublk * 8 ) / 1024 … Read more
SQL Query To Check Active Transactions set head ONSELECT usn,extents,Round(rssize / 1048576) rssize,hwmsize,xacts,waits,optsize / 1048576 optsize,shrinks,wrapsFROM v$rollstatWHERE xacts > 0ORDER BY rssize; Read More Articles … Read more