Showing posts from August, 2022
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 …
