Tech Support & Travel

ORA-1652: unable to extend temp segment by 21446 in tablespace SYSTEM  ORA-1652: unable to extend temp segment by 21446 in tablespace SYSTEM reported in the alert.log. This occurs when Users have the SYSTEM tablespace assigned as their default tempora…

Monitor Temporary Tablespace Usage SELECT (s.tot_used_blocks / f.total_blocks) * 100 AS "percent used"   FROM (SELECT SUM (used_blocks) tot_used_blocks           FROM v$sort_segment          WHERE tablespace_name = 'TEMPTS1') s,   …

RENAME AND OR MOVE A LOGFILE 1. Shutdown the database.     SQL> SHUTDOWN IMMEDIATE;    2. Copy the logfile to the new name/location at operating system level.     $cp oldfile  newfile   3. Mount the database.      SQL> STARTUP MOUNT     …

In Oracle 10g there is a New syntax in the database_file_clauses lets to rename tempfiles as well as datafiles.  The following procedure to be followed to rename a tempfile  ##################################### If the file is the default temporary…

To drop the existing temporary tablespace    The following procedure to be followed    a. create another temporary tablespace in another  drive ( windows ) or filesystem.(linux)     CREATE TEMPORARY TABLESPACE  TEMP1 TEMPFILE '<PATH>/temp1…

Issue :   Error while adding another file   to the existing temporary tablespace    ALTER TABLESPACE temp ADD datafile '<path>/temp.dbf' size 1G reuse;    ALTER TABLESPACE temp ADD datafile '<path>/temp.dbf' size 1G reuse  …

Changing the default temporary tablespace for the database or reclaiming the space occupied by the current temporary tablespace   The following procedure to be followed    a. create another temporary tablespace in another  drive ( windows ) or files…

How to Create Temporary Tablespace  or Add tempfile to Temp tablespace The following issues may occur with Temporary tablespaces in Oracle 1. Monitoring  2. Running out of Space. 3. Resizing  4. Changing the default temporary tablespace for the databa…

Manual scanning of a PC   Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\>cd\   C:\> sfc /scannow   C:\>

Pivot query in Oracle  report generated each hour wise   for zone and circle in the following query   select *     from (    select  ZONE,CIRCLE,TO_NUMBER(to_char(ococ_dt,'hh24'))  grp,count(*) cnt     from OCOC_CAF_JAN2022        group by  ZO…

-- block changes per sec happening in the Oracle database declare  v_blockchg number; begin    select value    into  v_blockchg    from   v$sysstat    where  name='db block changes';    dbms_lock.sleep(60);    select (value-v_blockchg)/60    …

# UNDO Recovery progress ...... select state, undoblockstotal, undoblockstotal-undoblocksdone "Still to Recover", undoblocksdone / undoblockstotal * 100 "Percentage Reconvery Done" from v$fast_start_transactions; undoblocksdone,

To know the locks held on a particular table SELECT     'alter system kill session'''||A.SID||','||A.SERIAL#||''' IMMEDIATE;'  KILLSTMT,     'kill -9 '||D.spid ,     A.SID,     a.serial#,A.INST_ID,     …

The following script shows the event that is blocking the sessions with high activity if no rows are displayed it means that the database is working fine with no blocking sessions    select    event,blocking_session,wait_class,    count( sid)  sessio…

1)   ssa.in    - This file contains all the values for which a procedure is to be run on O/S shell $ cat ssa.in ADB ATP CDP ELR GTR KAA KHM KNL MBN NGD NLR NZB ONG RMY SGD SK TRP VJA VM VZM WGL HTD …