Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • UNderstand and eliminate Latch contention. Oracle
  • Standby Database File Management in 10g with STANDBY_FILE_MANAGEMENT Oracle
  • find_cons.sql Oracle
  • Monitor and Trace Unix processes using truss Linux/Unix
  • How to calculate PROCESSES parameter Oracle
  • How to remove blank lines using vi editor command Linux/Unix
  • To see how much time or progress of long transaction Oracle
  • move_arch_files.ksh Linux/Unix
  • My FTP Job Scheduling for www.pvmehta.com PHP/MYSQL/Wordpress
  • TOP-N Sql to find Nth max or Top N rows Oracle
  • Some OS level threshold for performance. Linux/Unix
  • Drop database in Oracle 10g Oracle
  • ENQ: KO – FAST OBJECT CHECKPOINT tips Oracle
  • sql_doing_fts.sql Oracle
  • To Find Orphan OS processes. Linux/Unix

Year: 2006

segment_wise_space.sql segspace.sql Segment wise space usage (allocated and used)

Posted on 18-Apr-2006 By Admin No Comments on segment_wise_space.sql segspace.sql Segment wise space usage (allocated and used)

spool seg1 set lines 141 set serveroutput on execute dbms_output.enable(1000000); declare tmp varchar2(1); v_tbl_name varchar2(100); v_tot_blocks number; v_tot_bytes number; v_unused_blocks number; v_unused_bytes number; tot_mb number; unused_mb number; v_fileid number; v_blockid number; v_block number; cursor c1 is select OWNER, segment_name, segment_type, bytes/(1024 *1024) USED_MB, (extents * initial_extent)/(1024 *1024) Allocated_MB, Tablespace_name, extents from dba_segments where owner in…

Read More “segment_wise_space.sql segspace.sql Segment wise space usage (allocated and used)” »

Oracle, SQL scripts

catall.sh

Posted on 06-Apr-2006 By Admin No Comments on catall.sh

#!/bin/ksh for fname in `ls` do print “”; print “/* File Name : $fname */”; print “”; cat $fname done

Linux/Unix, shell

before_trunc.sql Before Truncate table needs to execute following:

Posted on 31-Mar-2006 By Admin No Comments on before_trunc.sql Before Truncate table needs to execute following:

/* before_trunc.sql This script gives disble constrints script before truncating table */ accept tblname prompt ‘Enter the Table Name You want to truncate -> ‘ column head heading “Execute Following Statements To Truncate &tblname, as &tblname is referenced by following foreign keys” select ‘alter table ‘ || lpad(b.table_name, 30) || ‘ disable constraint ‘ ||…

Read More “before_trunc.sql Before Truncate table needs to execute following:” »

Oracle, SQL scripts

TRUNCATE table and disabling referential constraints.

Posted on 31-Mar-2006 By Admin No Comments on TRUNCATE table and disabling referential constraints.

When you truncate table A, and Table A has some child table that contain data. TRUNCATE fails. For that you need to find table A’s all child tables and diable that referential integrity. /* before_trunc.sql This script gives disble constrints script before truncating table */ accept tblname prompt ‘Enter the Table Name You want to…

Read More “TRUNCATE table and disabling referential constraints.” »

Oracle, SQL scripts

Rownum with Order by

Posted on 31-Mar-2006 By Admin No Comments on Rownum with Order by

Above version 8.0, you can select rows for specified range. select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE — including the order by ) a where rownum = MIN_ROWS /

Oracle, SQL scripts

tblwopk.sql tablewopk.sql

Posted on 28-Mar-2006 By Admin No Comments on tblwopk.sql tablewopk.sql

select a.table_name from user_tables a where not exists ( select ‘x’ from user_constraints b where b.table_name = a.table_name and b.constraint_type = ‘P’);

Oracle, SQL scripts

fkwoind.sql fkwoindex.sql

Posted on 28-Mar-2006 By Admin No Comments on fkwoind.sql fkwoindex.sql

set pagesize 300 set linesize 205 set head on set echo off spool fkwoind accept usrid prompt ‘Enter User Name : ‘ col “Cons Name” format a30 heading “Cons Name” word_wrapped col status format a10 heading status col “Cons Type” format a12 heading “Cons Type” col “Column Name” format a30 heading “Column Name” col “Cons…

Read More “fkwoind.sql fkwoindex.sql” »

Oracle, SQL scripts

usnsql.sql Displays information about UNDO segments with sql statements

Posted on 27-Mar-2006 By Admin No Comments on usnsql.sql Displays information about UNDO segments with sql statements

select /*+ ALL_ROWS */ a.usn, a1.name, a.extents, a.hwmsize, a.status usn_status, b.start_date, b.status tx_status, c.sid, c.sql_id, d.sql_text from v$rollstat a, v$rollname a1, v$transaction b, v$session c, v$sqlarea d where a.usn = b. xidusn and a.usn = a1.usn and b.ses_addr = c.saddr and c.sql_id = d.sql_id;

Oracle, SQL scripts

Drop tempfiles from database

Posted on 21-Mar-2006 By Admin No Comments on Drop tempfiles from database

(1) alter tablespace temp add tempfile ‘/db7/oradata/WEBARCH/temp01.dbf’ size 1024M; (2) alter database tempfile ‘/db2/oradata/WEBARCH/temp_001.dbf’ drop including datafiles;

Oracle, SQL scripts

sid_wise_cursor.sql find open cursor basis on username or SID

Posted on 10-Mar-2006 By Admin No Comments on sid_wise_cursor.sql find open cursor basis on username or SID

accept u_name prompt ‘Enter User Name (Enter NONE if want SID specific query): ‘ accept s_id prompt ‘Enter SID (Enter 0 for Userspecific Query): ‘ select a.sid, b.sql_text, a.sql_id, c.spid, b.user_name from v$session a, v$open_cursor b, v$process c where a.sql_id = b.sql_id and (a.username = upper(‘&u_name’) or a.sid = &s_id ) and a.paddr = c.addr…

Read More “sid_wise_cursor.sql find open cursor basis on username or SID” »

Oracle, SQL scripts

Posts pagination

Previous 1 … 6 7 8 … 11 Next

Categories

  • Ansible (0)
  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • MYSQL (5)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • POSTGRESQL (0)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • SQL Server (6)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • SQL Server Vs Oracle Architecture difference25-Jul-2025
  • SQL Server: How to see historical transactions25-Jul-2025
  • SQL Server: How to see current transactions or requests25-Jul-2025
  • T-SQL Vs PL/SQL Syntax25-Jul-2025
  • Check SQL Server edition25-Jul-2025
  • Checking SQL Server Version25-Jul-2025
  • Oracle vs MYSQL Architecture differences (For DBAs)24-Jul-2025
  • V$INSTANCE of Oracle in MYSQL24-Jul-2025
  • Day to day MYSQL DBA operations (Compared with Oracle DBA)24-Jul-2025
  • MYSQL and Oracle Comparison for Oracle DBA24-Jul-2025

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • useful dg links Oracle
  • Processes parameter and its dependencies on OS kernel parameters Linux/Unix
  • How to set Processes Parameter Oracle
  • How to find the real execution plan and binds used in that explain plan in Oracle 10g?? Oracle
  • export import with parameter file. Oracle
  • Load SPM baseline from AWR Oracle
  • plan10g.sql good Oracle
  • Oracle 11g RAC on OEL 5 and Vmware 2 Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme