Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • findx.sql /* Find Indexes on specified USER.TABLE_NAME */ Oracle
  • Very clear article about oracle dataguard Oracle
  • normal maintenance for exp-imp and renaming table Oracle
  • Very Good Oralce Internal Tuning Book Oracle
  • Good links for x$ tables in oracle. Oracle
  • Privilege to describe the table. Oracle
  • Oracle Internal Good Websites 1 Oracle
  • login.sql Oracle
  • myfile Oracle
  • Guide to Linux System Command Mastery Linux/Unix
  • nfs mount command Linux/Unix
  • SQL_PROFILE – I explaination Oracle
  • SQLPLUS COPY command Precautions. Oracle
  • Rman Notes -1 Oracle
  • Oracle vs MYSQL Architecture differences (For DBAs) MYSQL

Category: SQL scripts

Error Handling in Proc

Posted on 21-Oct-2009 By Admin No Comments on Error Handling in Proc

There is a standard way of trapping any SQL or database errors in Pro*C.This is done using EXEC SQL WHENEVER SQLERROR DO ; just ensure that the above statement goes before any sql statements whenever an error is encountered , the control goes to the function function_name in the body of the function_name , just…

Read More “Error Handling in Proc” »

Oracle, SQL scripts

Proc code

Posted on 21-Oct-2009 By Admin No Comments on Proc code

1. EXEC SQL COMMIT WORK RELEASE; (When you want to commit transactions and disconnect;similar to EXIT in SQL*PLUS) 2. EXEC SQL ROLLBACK WORK RELEASE; (When you want to rollback transactions and disconnect;similar to QUIT in SQL*PLUS)

Oracle, SQL scripts

To see how much time or progress of long transaction

Posted on 30-Sep-2009 By Admin No Comments on To see how much time or progress of long transaction

select * from v$session_longops where sid=100;

Oracle, SQL scripts

cp_filesystem.sql

Posted on 15-Sep-2009 By Admin No Comments on cp_filesystem.sql

exec dbms_output.enable(10000); set lines 130 pages 1000 set serveroutput on size 1000000 declare cursor c1 is select substr(name,1,61) complete_filename, bytes/(1024) KB, substr(name, instr(name, ‘/’, -1)+1) only_filename from v$datafile order by file#; threshold_kb number(10) := 94000000 ; size_counter number := 0; mountpoint_counter number := 1; mountpoint_prefix varchar2(10) := ‘/800t’; common_mountpoint_prefix varchar2(15) := ‘/oradata/800T/’; target_filename varchar2(61); begin…

Read More “cp_filesystem.sql” »

Oracle, SQL scripts

Caching sequence in Memory

Posted on 14-Sep-2009 By Admin No Comments on Caching sequence in Memory

ALTER SEQUENCE CS.SESSION_ACTIVE_SEQ_ID CACHE 2000; exec sys.DBMS_SHARED_POOL.KEEP(‘CS.SESSION_ACTIVE_SEQ_ID ‘, ‘Q’) =>> To pin the sequence using the DMBS_SHARED_POOL.KEEP procedure (IF NEEDED) Potential drawbacks Setting a cache size larger then 0 can result in the loss of sequence values if the system is shutdown abruptly. When the system fails, the values that were preserved in memory are…

Read More “Caching sequence in Memory” »

Oracle, SQL scripts

Oracle 11g Environment Setup

Posted on 01-Sep-2009 By Admin No Comments on Oracle 11g Environment Setup

11g Environment Setting: ======================= —————————————————————– ==>cat .profile . /oracle/app/oracle/set_env_var_dba PS1=” `hostname`*${ORACLE_SID}-${PWD} ==>” EDITOR=vi; export EDITOR set -o vi stty erase PATH=$PATH:. dba() { cd /oracle/app/oracle/scripts/MAINT/DBA/bin } ———————————————————————————— ==>cat /oracle/app/oracle/set_env_var_dba # This File Sets all the Oracle Environment Variables and is called from # Each Users’s . profile file. # # Please Don’t change these settings…

Read More “Oracle 11g Environment Setup” »

Oracle, SQL scripts

New OFA for 11g

Posted on 25-Aug-2009 By Admin No Comments on New OFA for 11g

export INSTANCE_NAME=`echo $ORACLE_SID | tr ‘[:upper:]’ ‘[:lower:]’` export ORA_BDUMP=$ORACLE_HOME/admin/”$ORACLE_SID”_`hostname`/diag/rdbms/$INSTANCE_NAME/$ORACLE_SID/trace

Oracle, SQL scripts

Alter procedure auditing

Posted on 28-Jul-2009 By Admin No Comments on Alter procedure auditing

A common misconception is that the ‘audit any procedure’ is a privileges auditing option associated with the privilege ‘alter any procedure’ and thus does NOT cover the alteration of a procedure that is owned by the user altering it. Note that AUDIT PROCEDURE covers: CREATE FUNCTION CREATE LIBRARY CREATE PACKAGE CREATE PACKAGE BODY CREATE PROCEDURE…

Read More “Alter procedure auditing” »

Oracle, SQL scripts

EXTPROC

Posted on 23-Jul-2009 By Admin No Comments on EXTPROC

EXTPROC Scope and Application A list of the essential components for executing an external procedure is included below, along with a brief description of the role of each. Database: Responsible for storing any information required to locate and execute an external procedure (e.g. the location of shared library, number and types of arguments, etc.) Initiates…

Read More “EXTPROC” »

Oracle, SQL scripts

run this before doing any dbchange pvm_pre_change.sql

Posted on 08-Jul-2009 By Admin No Comments on run this before doing any dbchange pvm_pre_change.sql

column currdate new_value pvmdt noprint select ‘pvm_comp_’ || to_char(SYSDATE, ‘DDMONRRRR_HH24MI’) || ‘.sql’ currdate from v$instance; spool &pvmdt set lines 120 pages 2000 set head off set feedback off prompt set pages 5000 lines 120 prompt select ‘prompt ‘||a.owner||’.’||a.object_name||’ – ‘||a.object_type|| chr(10)|| prompt ‘alter ‘|| prompt decode(a.object_type,’PACKAGE BODY’,’PACKAGE’,object_type) || ‘ “‘|| prompt a.owner||’”.’||a.object_name||’ compile ‘|| prompt…

Read More “run this before doing any dbchange pvm_pre_change.sql” »

Oracle, SQL scripts

Posts pagination

Previous 1 … 6 7 8 … 35 Next

Categories

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

Recent Posts

  • Trace a SQL session from another session using ORADEBUG30-Sep-2025
  • 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

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • sesswait.sql Oracle
  • 272332.1 CRS 10g Diagnostic Collection Guide Oracle
  • Finding Oracle Patches with opatch Oracle
  • ext#.sql Oracle
  • How does one overcome the Unix 2 Gig file limit? Linux/Unix
  • good note for shared pool tunnig Oracle
  • proper cpu stats Linux/Unix
  • longtx.sql Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme