Index Range Scan
If your index is NONUNIQUE, you cant enforce Index Unique Scan.
SQLPLUS COPY command Precautions.
One test case today i found with copy command. When we are using sqlplus copy command with “APPEND” clause, and if source table contains any NUMBER datatype without Precision and Scale, then COPY statement fails with error indicating “NUMERIC ERROR and Number data type precision is higher than 38 digit”.
Optimizer_Index init.ora parameter explaination.
For Index ( Range Scan and Fast Full Scan Setings )in query (1) RULE uses index range scan (2) CBO uses Index fast full scan ( called Index FFS ) Here CBO uses multi block IO reading as per db_file_multi_block_read_count). By default, the CBO costs physical IO and logical IO the same. So it ignores…
Read More “Optimizer_Index init.ora parameter explaination.” »
CTAS with LONG Column for 9i and higher
Executing CTAS statement having TO_LOB(LONG COLUMN). For example: create table blah as select GNT_ID.nextval, 1060 as gnt_ref_id, org.gnt_id, org.gnt_group_note, org.gnt_language, to_lob(org.gnt_text_string) as gnt_text_string, org.gnt_text_emphasize from asra_002.gnt_groupnote_txt org where org.gnt_group_note in (select gnr_note from asra_002.gnr_groupnote_ref)
CTAS with LONG Column for 7.x and 8 and 8i
How to Copy Data from a Table with a LONG Column into an Existing Table ======================================================================= Purpose: ======== The purpose of this article is to provide a working example of how to COPY data from a table with a LONG column into another existing table with the same structure definition. This document is especially useful…
My FTP Job Scheduling for www.pvmehta.com
/* file : 1 at_command_syntax */ at 00:00 /every:M,T,W,TH,F,S,SU “C:Pvmehsqlfilesftp01.bat” at 09:00 /every:M,T,W,TH,F,S,SU “C:Pvmehsqlfilesftp01.bat” at 12:00 /every:M,T,W,TH,F,S,SU “C:Pvmehsqlfilesftp01.bat” at 15:00 /every:M,T,W,TH,F,S,SU “C:Pvmehsqlfilesftp01.bat” at 18:00 /every:M,T,W,TH,F,S,SU “C:Pvmehsqlfilesftp01.bat” at 21:00 /every:M,T,W,TH,F,S,SU “C:Pvmehsqlfilesftp01.bat” /* file : 2 my.cmd */ open ftp.pvmehta.com pvmehta nupur46 lcd C:pvmehdoc1Z0-030 put oracle_9i_upgrade_certification.htm close open ftp.pvmehta.com pvmehta nupur46 lcd c:websitepvmehtacgi-bin cd cgi-bin get PERLMYSQL.txt…
First Entry in RAC
Real Application Cluster = OPS in previous oracle versions
Windows based Command line mailing program like mailx (Sednmail for windows)
Download for free from http://prdownloads.sourceforge.net/blat/Blat250.zip?use_mirror=voxel and installed using following command. Blat -installSMTP mail.pvmehta.com me@pvmehta.com After that i sent a text message using following command. F:UsefulBlat250Blat250full>blat – -body “test” -to me@pvmehta.com Blat v2.5.0 w/GSS encryption (build : Sep 14 2005 22:46:29) Sending stdin.txt to me@pvmehta.com Login name is me@pvmehta.com F:UsefulBlat250Blat250full>
get_vmstat.ksh
select a.measured_date, a.total_users, a.active_users, b.user_cpu, b.system_cpu, b.idle_cpu, b.wait_cpu from stats$totalusers a, stats$vmstat2 b where a.measured_date = b.start_date and to_char(measured_date,’DD-MON-YY’)=’22-NOV-05′ and to_char(measured_date,’HH24′) > ’14’ and to_char(measured_date,’HH24′) < '19' order by measured_date desc Script that populates stats$vmstat2 table is dbblkabva01:/export/home/oracle/oraprocs/get_vmstat2.ksh ——– vmstats.ksh ————- #!/bin/ksh # First, we must set the environment . . . . ORACLE_SID=BLINKT export...
DBMS_UTILITY PACKAGE
DBMS_UTILITY Oracle provides two procedures under the DBMS_UTILITY package related to statistics generation. (Oracle recommends use of DBMS_STATS package for generating statistics). DBMS_UTILITY.ANALYZE_SCHEMA This routine will generate statistics on an individual schema level. It is used for analyzing all tables, clusters and indexes. It takes the following parameters: schema – Name of the schema method…