Oracle DBA Blog

high standard of personal ethics

2008-05-22T04:22:00.000-07:00

A SHORT MEANINGFUL STORY

A contractor, who had made a fortune building homes, told his supervisor of 35 years,

"i'm going to build one last house and u will build it for me coz i'll b gone for a year.

use the best material --money is no consideration ..make it the greatest house we've ever built"......

having given these instruction , the man left ..

the supervisor thought that this was a great oppurtunity to make a fortune .

He used the cheapest material inside but made the house look beautiful frm outside..

after a year ,the contractor returned,he inspected the house and asked the supervisor what he thought of the house .

The supervisor replied"it's the best house i've ever built"

THE CONTRACTOR HANDED OVER THE DEED TO HIM AND SAID

"THIS IS MY PARTING GIFT TO U"

MORAL --- we shud Develop a high standard of personal ethics ....and always do
the right things ,even if no one is watching us.

Murtuja.

I would prefer to be a honest failure than to be a corrupt success..

Can't connect to MySQL (10061)

2008-04-17T09:24:00.000-07:00

Add this line to my.cnf file
bind-address = 127.0.0.1

restart mysql service

nowconnect mysql from command prompt
mysql -u root -p
Password
mysql>use mysql;
mysql>UPDATE user SET password=password("newpassword") WHERE user="root";
mysql>FLUSH PRIVILEGES;

lsnrctl status is not returing service name in RAC

2008-04-10T21:55:00.000-07:00

Problem:
ORA-12154: TNS:could not resolve the connect identifier specified
lsnrctl status is not returing service name

Fix:
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = blr-catfish-rac1-vip) (PORT = 1521))' scope=both sid='ORCL1';

How to avoid hot block contention for small look-up tables ?

2008-04-07T22:36:00.000-07:00

Find out block contention .
While run the query found HOTBLOCK of tables and indexes on the schema.

Identified like :
==========
1.

SQL> SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait
WHERE event = 'buffer busy waits';

file# block# class#
---------- ---------- ----------
1045 99752 130
1045 99752 130
1045 99752 130
1045 99752 130
1045 99752 130
1045 99752 130

6 rows selected.

2.

SQL> SELECT relative_fno, owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &file
AND &block BETWEEN block_id AND block_id + blocks - 1; 2 3 4
Enter value for file: 1045
old 3: WHERE file_id = &file
new 3: WHERE file_id = 1045
Enter value for block: 99752
old 4: AND &block BETWEEN block_id AND block_id + blocks - 1
new 4: AND 99752 BETWEEN block_id AND block_id + blocks - 1

RELATIVE_FNO OWNER
------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
22 SCAADM
SCA_TMS_LOV_MAP
TABLE

==========
Table : SCA_TMS_LOV_MAP

alter table table_name minimize records_per_block;

verify

dbms_stats.gather_table_stats(ownname => user, tabname => v_table , estimate_percent => 100,
method_opt => 'for all indexed columns size auto',
degree => 4 ,cascade => v_cascade );

select table_name,num_rows,blocks from user_tables where table_name ='table_name '

Oracle TKPROF

2008-04-03T05:28:00.000-07:00

This link will explain you what is tkprof ? how to generate tkprof?

http://www.oracle-base.com/articles/8i/TKPROFAndOracleTrace.php
http://www.oracleutilities.com/OSUtil/tkprof.html

Once you generated tkprof then you need to interpret it’s out put

http://www.adp-gmbh.ch/ora/tuning/tkprof/count_cpu_elapsed.html
http://www.adp-gmbh.ch/ora/tuning/tkprof/query_current.html

http://www.jlcomp.demon.co.uk/tkprof_01.html

The skill of searching

2008-01-27T21:41:00.000-08:00

The skill of searching is one of the most important skills you need these days to survive.
The communication on the net is another one.

Ignorance is the opposite.

Just to kick-start your skilling:
when searching:
clearly state the question/request:
in your case: "I need a sql to delete duplicate rows in oracle table"
cut the overhead out:
delete duplicate rows

Go through some of the items on top, and if you didn't find the answer - then your keywords are wrong.

When all other fails - ther eare many other search engines.
My personal preference is Google, but feel free to use any other. When searching on the generic search engine, you should include the keyword "Oracle".
Let's see if this is good for you:
http://www.google.com/search?q=oracle+delete+duplicate+rows

If you didn't find the answer in the first five references - your list of keywords is wrong!

If you want to learn moer about the searching from master Fravia:
http://www.searchlores.org/

Also - it pays to educate in the general communication skills on the net.
Seatch on netiquette on google:
http://www.google.com/search?hl=en&lr=&q=netiquette
then read a bit.
When ready - come back with some real questions.
Good luck

determine the service pack that's installed on your SQL Server

2008-01-07T03:40:00.000-08:00

Execute the following command:

SELECT @@VERSION

SELECT SERVERPROPERTY('ProductLevel')

Not Null constraints

2007-12-26T02:05:00.000-08:00

CREATE OR REPLACE PROCEDURE check_uncons_columns_for_null (
p_schema VARCHAR2,
p_table_name VARCHAR2 DEFAULT NULL
)
IS
CURSOR x
IS
SELECT table_name, column_name,
MAX (column_name) OVER (PARTITION BY table_name) AS lastcol
FROM (SELECT a.table_name, a.column_name
FROM dba_tab_columns a
WHERE a.owner = p_schema
AND a.table_name = NVL (UPPER (p_table_name), a.table_name)
MINUS
SELECT a.table_name, b.column_name
FROM dba_cons_columns b, dba_constraints a
WHERE a.owner = p_schema
AND a.constraint_type = 'C'
AND a.table_name = NVL (UPPER (p_table_name), a.table_name)
AND a.table_name = b.table_name
AND a.owner = b.owner
AND a.constraint_name = b.constraint_name)
ORDER BY 1, 2;

str0 VARCHAR2 (32767);
str1 VARCHAR2 (32767);
str2 VARCHAR2 (32767);
str3 VARCHAR2 (32767);
prev VARCHAR2 (100) := '*';
cnt NUMBER;
trailer VARCHAR2 (5);

PROCEDURE do_sql (thesql VARCHAR2)
IS
tcursor INTEGER;
dummy INTEGER;
BEGIN
tcursor := DBMS_SQL.open_cursor;
DBMS_SQL.parse (tcursor, thesql, 2);
dummy := DBMS_SQL.EXECUTE (tcursor);
DBMS_SQL.close_cursor (tcursor);
END;
BEGIN
FOR i IN x
LOOP
IF prev != i.table_name
THEN
str0 := 'declare ';
str1 := 'begin select ';
str2 := ' into ';
str3 := ' ';
cnt := 1;
END IF;

IF i.column_name = i.lastcol
THEN
trailer := ' ';
ELSE
trailer := ',' || CHR (10);
END IF;

str0 := str0 || 'v' || LTRIM (cnt) || ' number;';
str1 :=
str1 || 'sum(decode(' || i.column_name || ',null,1,0))' || trailer;
str2 := str2 || 'v' || LTRIM (cnt) || trailer;
str3 :=
str3
|| 'if v'
|| LTRIM (cnt)
|| ' = 0 then '
|| 'dbms_output.put_line(''alter table '
|| p_schema
|| '.'
|| i.table_name
|| ' modify ('
|| i.column_name
|| ' not null);''); end if;'
|| CHR (10);

IF i.column_name = i.lastcol
THEN
str2 := str2 || ' from ' || p_schema || '.' || i.table_name || ';';
str3 := str3 || ' end;';
do_sql (str0 || ' ' || str1 || ' ' || str2 || ' ' || str3);
END IF;

prev := i.table_name;
cnt := cnt + 1;
END LOOP;
END;
/

calculate time between to columns

2007-11-29T02:00:00.000-08:00

SELECT TO_DATE( '27-NOV-07 15:35:44', 'DD-MON-YY HH24:MI:SS' )
- TO_DATE( '27-NOV-07 15:02:50', 'DD-MON-YY HH24:MI:SS' ) days
, TO_CHAR( TRUNC( sysdate )
+ ( TO_DATE( '27-NOV-07 15:35:44', 'DD-MON-YY HH24:MI:SS' )
- TO_DATE( '27-NOV-07 15:02:50', 'DD-MON-YY HH24:MI:SS' )
)
, 'MI:SS' ) minsec
FROM dual;
DAYS MINSE
---------- -----
.022847222 32:54

composite unique constraint mysql

2007-10-25T03:01:00.000-07:00

alter table room_new add unique key(room_no,rack_no,slot_no)

stealthpuppy / Script Corner

2007-10-23T00:39:00.000-07:00

stealthpuppy / Script Corner

Index on Miscellaneous Items (JL Comp)

2007-10-22T05:00:00.000-07:00

Index on Miscellaneous Items (JL Comp)

www.oracledba.co.uk

2007-10-22T03:41:00.000-07:00

www.oracledba.co.uk

Joomla! Extensions Directory - Tools

2007-10-22T01:58:00.000-07:00

Joomla! Extensions Directory - Tools

How to drop a dismounted ASM disk group?

2007-10-15T22:10:00.000-07:00

Problem :

I was not able to Mount or Drop ASM disk group.

SQL> select group_number, name, STATE from v$asm_diskgroup;

GROUP_NUMBER NAME STATE
------------ ------------------------------ -----------
3 RAC_DATA1 DISMOUNTED
4 RAC_DATA2 DISMOUNTED
3 RAC_DG1 MOUNTED
4 RAC_DG2 MOUNTED

SQL> drop diskgroup RAC_DATA1;
drop diskgroup RAC_DATA1
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup "RAC_DATA1" does not exist or is not mounted

SQL> alter diskgroup RAC_DATA1 mount;
alter diskgroup RAC_DATA1 mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15024: discovered duplicately numbered ASM disk 0

Solution

I have followed ML Note:268481.1
Re-creating ASM Instances and Diskgroups

I have used force option while creating diskgroups

Complete checklist for manual upgrades to 10gR2

2007-10-15T21:42:00.000-07:00

I referred the following Metalink Notes for the same.

316889.1
233436.1

How to calculate folder size in Linux

2007-10-02T01:40:00.000-07:00

du -ahc folder_name

Solaris User Profile Customization

2007-10-01T22:27:00.000-07:00

Edit /etc/profile

#User Profile Customization
PS1="\u@\h\w>"
export PS1
TERM=vt100
export TERM

It will set prompt as USER@HOSTNAME

How to find out which version of Linux

2007-10-01T22:16:00.000-07:00

How to determine if Linux is 64bit ?

uname -a

Linux snook.srv.bang.indorion.org 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:27:17 EDT 2006 i686 i686 i386 GNU/Linux

x86_64 GNU/Linux indicates I have 64bit CPU. If you use see i386/i486/i586/i686 it is a 32 bit CPU.

How to find out which version of Linux you are running ?
Method 1

ls /etc/*release*
ls /etc/*version*

This will give you a file that contains the version.
cat file_that_ls_found

Method 2

uname -a (prints all information)

uname -r (prints the kernel release)

uname -v (prints the kernel version)

uname -o (prints the operating system)

Oracle Edition Version Check

2007-10-01T21:38:00.001-07:00

select * from v$version;

select * from PRODUCT_COMPONENT_VERSION;

SET SERVEROUT ON
EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION);

query CRS version

2007-10-01T21:36:00.000-07:00

srvctl -V
export ORACLE_SID=+ASM

select status from v$instance;

select * from v$version;

crsctl query crs softwareversion
crsctl query crs activeversion

cemutlo -w

The cemutlo command is used by the EM agent to query CRS version.

2007-10-01T21:33:00.001-07:00

2007-10-01T21:29:00.000-07:00

How to speed up Import

2007-09-27T23:10:00.001-07:00

How to speed up Import
Here are are some suggestions.
1. Check the database alert log. If you see messages like Checkpoint Incomplete, or if log files are switching frequently, then you need to increase the size and number of redo log files.
2. Set or increase the size of the BUFFER parameter on your import. This will increase the size of the buffer that Oracle uses to do the bulk inserts.
3. If possible, create a large UNDO tablespace, and switch to that large UNDO tablespace by using the alter system undo_tablespace=Large_Undo before the import. Then, run the IMP with the COMMIT=N option. After the import completes, switch back to the old UNDO tablespace and then drop the large one if it is no longer necessary. If you do not have the enough space for a larger redo then try increasing the COMMIT parameter setting on the IMP command.
4. Increase the SORT_AREA_SIZE parameter. Increasing this parameter will provide more space for sorting operations when creating indexes.
5. Increase the LOG_CHECKPOINT_INTERVAL. This will help to reduce the number of checkpoints.
Here are are some suggestions from Chris Marquez
———————–
Faster IMPORTS
———————–
Set IMP parameter COMMIT = Y.
Set IMP parameter RECORDLENGTH >= EXP RECORDLENGTH value.
Set IMP parameter BUFFER = MB (Set in the MB range not KB range)
Set IMP parameter STATISTICS = NONE (9i, n/a 8i)
Set IMP parameter RECALCULATE_STATISTICS = N. (8i, n/a 9i)
Set IMP parameter ANALYZE = N. (8i, n/a 9i)
Set IMP parameter INDEXES = N (Import them later…separately)
Set init.ora parameter LOG_ARCHIVE_START = FALSE *&* alter database
noarchivelog;
Set init.ora parameter _disable_logging = TRUE (Warning, this could be
dangerous and unrecoverable in failure. Backup immediately after import
- * Redo records (to disk) will NOT be generated (redo WAS generated in
the log buffer.) [Even with _disable_logging you still “switch” logs
when they get “full”])
Set init.ora parameter _wait_for_sync = FALSE (Warning, this could be
dangerous and unrecoverable in failure. Backup immediately after import
- _wait_for_sync: Wait_for_sync is an oracle generic parameter which,
when set to false, will allow the system to complete commits without
waiting for the redo-log buffer flushes to complete.)
Make redo logs enormous; 500MB, 1GB, etc.
Use Locally Managed Tablespaces on target database.

csv format with select *

2007-09-27T23:09:00.002-07:00

Hi,

Here you have both - XML extraction to generate CSV:http://laurentschneider.com/wordpress/2007/05/csv-format-with-select.htmlRegards,

Gathering Index Statistics

2007-09-27T23:09:00.001-07:00

Oracle can gather some statistics automatically while creating or rebuilding a B*-tree or bitmap index. The COMPUTE STATISTICS option of CREATE INDEX or ALTER INDEX … REBUILD enables this gathering of statistics.

set vi as default editor

2007-09-27T23:06:00.000-07:00

I have set vi as default editor by editing glogin.sql

define_editor=vi