Transcript A Bag of Tips and Tricks for DBAs and Developers

More Tips and
Techniques for DBAs
and Developers
Ari Kaplan
www.arikaplan.com
Independent Consultant
Paper 78
EOUG Madrid 2000
Introduction

Most books and documentation are set up like
reference materials

Training courses focus on the fundamental aspects
of products in a limited time

Hands-on experience is one of the best ways to
learn tricks to maximize your use of Oracle
“Gotchas” of Dropping and
Recreating Tables
Dropping a table will cause the following actions to occur:
 All indexes on the table are dropped from the database
 All triggers on the table are dropped from the database
 All triggers that reference the table are invalidated
 All constraints on the table are dropped (PK, UK, Check, Not
Null)
 All foreign keys pointing to the table are disabled
 All packages, procedures, and views that reference the table
are invalidated
 All grants on the table are dropped from the database.
“Gotchas” of Dropping and
Recreating Tables
DBA_DEPENDENCIES
Name
Null?
Type
----------------------------------OWNER
NOT NULL
NAME NOT NULL
TYPE
REFERENCED_OWNER
REFERENCED_NAME
REFERENCED_TYPE
REFERENCED_LINK_NAME
----------------------VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(12)
VARCHAR2(30)
VARCHAR2(64)
VARCHAR2(12)
VARCHAR2(128)
DEPENDENCY_TYPE
VARCHAR2(4)
“Gotchas” of Dropping and
Recreating Tables
SELECT OWNER, NAME, TYPE, REFERENCED_OWNER,
REFERENCED_NAME, REFERENCED_TYPE
FROM ALL_DEPENDENCIES
WHERE REFERENCED_NAME = ‘EMP_TABLE’;
OWNER
NAME
TYPE
REFERENCED_OWNER
REFERENCED_NAME
REFERENCED_T
--------------------------------------------------------------- --------------------------------PROD
PROD_BATCH_UTILITY_PPACKAGE
PROD
EMP_TABLE
NON-EXISTENT
PROD
PROCESS215
FUNCTION
PUBLIC
EMP_TABLE
SYNONYM
PROD
POST205
FUNCTION
PUBLIC
EMP_TABLE
SYNONYM
“Gotchas”
of Dropping and Recreating
Set
heading off
spool recompile.sql
SELECT ‘alter ‘||decode(object_type,’PACKAGE
BODY’,’PACKAGE’,object_type)||
‘ ‘||owner||’.’||object_name||’ compile;’
FROM dba_objects
WHERE status= ‘INVALID’;
spool off
Tables
alter PROCEDURE PROD.CM1279 compile;
alter FUNCTION PROD.DELETE_TRADE_DATE
compile;
alter FUNCTION PROD.POP_TEMP_MATCH compile;
alter VIEW TEST_ACCT.POST215_V compile;
alter FUNCTION TEST_ACCT.PROCESS215 compile;
alter FUNCTION TEST_ACCT.PROCESS216 compile;
alter PACKAGE TEST_ACCT.TEST_UTILITY_P
compile;
See Hidden INIT.ORA Parameters
Regular INIT.ORA:
Name
-------------------------------------------------
V$PARAMETER
Null?
------------------------
Type
----------------------------------------
NUM
NAME
TYPE
VALUE
ISDEFAULT
ISSES_MODIFIABLE
ISSYS_MODIFIABLE
ISMODIFIED
ISADJUSTED
NUMBER
VARCHAR2(64)
NUMBER
VARCHAR2(512)
VARCHAR2(9)
VARCHAR2(5)
VARCHAR2(9)
VARCHAR2(10)
VARCHAR2(5)
DESCRIPTION
VARCHAR2(64)
See Hidden INIT.ORA Parameters
To view the “regular” init.ora parameters, issue:
SQL> SELECT NAME, VALUE, ISDEFAULT
2
FROM V$PARAMETER
3
ORDER BY NAME;

NAME
VALUE
ISDEFAULT
------------------------------------------------ --------partition_view_enabled
FALSE
TRUE
plsql_v2_compatibility
FALSE
TRUE
pre_page_sga
TRUE
FALSE
processes
150
FALSE
push_join_predicate
FALSE
TRUE
read_only_open_delayed
FALSE
TRUE
recovery_parallelism
0
TRUE
See Hidden INIT.ORA Parameters
“Hidden” INIT.ORA:
X$KSPPI and X$KSPPSV
X$KSPPI
X$KSPPSV
Name
Type
Name
Type
-----------------------------
----------------------------------------
-----------------------
---------------------------------
ADDR
INDX
INST_ID
KSPPINM
KSPPITY
KSPPDESC
KSPPIFLG
RAW(4)
NUMBER
NUMBER
VARCHAR2(64)
NUMBER
VARCHAR2(64)
NUMBER
ADDR
INDX
INST_ID
KSPPSTVL
KSPPSTDF
KSPPSTVF
RAW(4)
NUMBER
NUMBER
VARCHAR2(512)
VARCHAR2(9)
NUMBER
See Hidden INIT.ORA Parameters

To see all init.ora parameters, “regular” and “hidden”, that have been
modified directly or indirectly, issue:
SQL> SELECT nam.ksppinm || '=' ||
val.ksppstvl
2
FROM x$ksppi nam, x$ksppsv val
3
WHERE nam.indx = val.indx AND
4
val.ksppstdf ='FALSE’ ORDER BY 1;
NAM.KSPPINM||’=’||VAL.KSPPSTVL
------------------------- ------------------------ --------partition_view_enabled=FALSE
_affinity_on=FALSE
_wait_for_sync=TRUE
Changing the SQL Prompt

Global Login Script:
$ORACLE_HOME/sqlplus/admin/glogin.sql

Local Login Script:
$ORACLE_PATH/login.sql
set heading off
select 'Logged in as '
|| username from
user_users;
set heading on
Changing the SQL Prompt
In glogin.sql or login.sql:
set heading off
set prompt off
spool make_prompt.sql
SELECT ‘set sqlprompt ‘‘‘|| d.name ||‘@‘
||
substr(s.machine,1,decode
(instr(s.machine,‘.‘), 0,
length(s.machine),
instr(s.machine,‘.‘) - 1)) || ‘SQL> ‘‘‘
FROM V$SESSION s, V$DATABASE D
WHERE s.SID=1;
The prompt will look
like:
spool off
@make_prompt.sql
PHIS@survlpd-SQL>
set heading on
Resize the Redo Logs
SQL> SELECT * FROM V$LOG;
GROUP# THREAD#
FIRST_TIM
SEQUENCE#
BYTES
MEMBERS
ARC
STATUS
CHANGE#
--------------------------------------------------------------------------- --------- --------- --------- --------- --------1
2
1
1
NO
NO
3
1
1
INACTIVE
422
680764231
INACTIVE
423
680764271
1
1
NO
CURRENT
424
680764463
05-APR-00
05-APR-00
52,428,800
262,144,000
262,144,000
05-APR-00
SQL> SELECT * FROM V$LOGFILE;
GROUP#
STATUS
MEMBER
------------------------------------------------------------------------------/emc08/ORACLE/DHIS/redoQHIS01a.log 1
Resize the Redo Logs
1) Drop the redo log (or group of redo logs):
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
(If the active log group is 1, you will get the following error:)
ORA-01623: log 1 is current log for thread 1 – cannot drop
(If this occurs, switch the logfile group: “ALTER SYSTEM SWITCH LOGFILE;”
and reissue.)
Database altered.
2) Now there are two log groups remaining (#2, 3). Next, we will physically
remove the file:
SQL> !rm /emc08/ORACLE/DHIS/redoQHIS01a.log
3) Add the redo log back to group 1, with a 16M size:
SQL> ALTER DATABASE ADD LOGFILE GROUP 1
'/emc08/ORACLE/DHIS/redoQHIS01a.log' SIZE 16M
Database altered.
4) Switch the logfile until it points to the recently changed group (in this
example group 1).
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
The new redo log (from group 1) is now 16M in size.
Repeat #1-4 above until all three redo logs have been resized.
Using DECODE for Multiple Values in One Clause
Decode Example:
SQL> SELECT DECODE(SEX, ’M’, ’Male’, ’F’, ’Female’, ’Unknown’)
2
FROM TABLE_NAME;
Table Example:
SQL> SELECT * FROM ACCOUNT_PHONES_T;
PHONE
555-1212
5553333
SQL> SELECT PHONE, SUBSTR(PHONE,1,3)||
2
DECODE(SIGN(7-LENGTH(PHONE)),-1,
3
SUBSTR(PHONE,4,99),
4
“PHONE_FORMATTED”
5
FROM ACCOUNT_PHONES_T;
PHONE
PHONE_FORMATTED
555-1212
555-1212
5553333
555-3333
If the length of
phone > 7
'-'||SUBSTR(PHONE,4,99))
If the length of
phone not > 7
Using DECODE for < or > Comparisons
Table Example:
SQL> SELECT * FROM SALARIES;
HUSBAND_SALARY
WIFE_SALARY
----------------------------- ----------------------45000
60000
90000
39700
SQL Using DECODE and LEAST:
SQL> SELECT husband_salary, wife_salary,
DECODE (LEAST (husband_salary,wife_salary),
husband_salary,'The Husband has the
lower salary',
'The Wife has the lower salary')
"WHO_IS_LOWER"
FROM SALARIES
WHERE husband_salary <> wife_salary;
HUSBAND_SALARY
WIFE_SALARY
WHO_IS_LOWER
-----------------------------------------------------------------------45000
60000
The
Husband has the lower salary
Using DECODE for < or > Comparisons
Table Example, with <, >, and = values:
SQL>
SELECT * FROM SALARIES;
HUSBAND_SALARY
WIFE_SALARY
45000
60000
90000
39700
55000
55000
SQL Using DECODE and LEAST:
SQL> SELECT husband_salary, wife_salary,
decode(husband_salary - wife_salary, 0, 'The
salaries are the same',
decode(least(husband_salary,wife_salary),
husband_salary,'The Husband has the lower
salary',
‘The Wife has the lower salary'))
"WHO_IS_LOWER”
FROM SALARIES;
HUSBAND_SALARY
WIFE_SALARY
WHO_IS_LOWER
Identify and Delete Duplicate Records
Table Example, with duplicate values:
SQL> SELECT * FROM EMP;
EMP_ID
OFFICE_ID
EMPNAME
305
12
ELLISON, GEORGE
305
12
MERCURIO, JASON
128
17
SIMPSON, LINDA
305
22
JACKSON, DREW
To see duplicates, use the following SQL:
SQL> SELECT COUNT(*), EMP_ID, OFFICE_ID
FROM EMP
GROUP BY EMP_ID, OFFICE_ID
HAVING COUNT(*) > 1;
The result will be:
COUNT(*)
EMP_ID
2
305
OFFICE_ID
12
Identify and Delete Duplicate Records
Table Example, with duplicate values:
SQL> SELECT * FROM EMP;
EMP_ID
OFFICE_ID
EMPNAME
305
12
ELLISON, GEORGE
305
12
MERCURIO, JASON
128
17
SIMPSON, LINDA
305
22
JACKSON, DREW
To delete ALL duplicates (not leaving any of the set):
SQL> DELETE FROM EMP A WHERE
(EMP_ID, OFFICE_ID, 2) IN
(SELECT EMP_ID, OFFICE_ID,
decode(count(*),1,1,2)
FROM EMP B
WHERE A.EMP_ID=B.EMP_ID AND
A.OFFICE_ID = B.OFFICE_ID
Identify and Delete Duplicate Records
Table Example, with duplicate values:
SQL> SELECT * FROM EMP;
EMP_ID
OFFICE_ID
EMPNAME
305
12
ELLISON, GEORGE
305
12
MERCURIO, JASON
128
17
SIMPSON, LINDA
305
22
JACKSON, DREW
To delete all of the duplicates (leaving just one of the set):
DELETE FROM EMP A
WHERE ROWID > (
SELECT min(rowid) FROM EMP B
WHERE A.EMP_ID = B.EMP_ID AND
A.OFFICE_ID = B.OFFICE_ID);
SQL>
OS Commands and Utilities
 oerr
> oerr ora 3200
03200, 00000, "the segment type specification is invalid"
// *Cause: segment type is not TABLE, INDEX, or CLUSTER
// *Action: use a correct segment type

dbv (db verify)
> dbv file=system_01.dbf feedback=100
DBVERIFY: Release 8.1.6.0.0 - Wed Jul 4 09:15:04 1999
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
DBVERIFY - Verification starting : FILE = system_01.dbf
.........................................................................
.......
DBVERIFY - Verification complete
Total Pages Examined
: 9216
Total Pages Processed (Data) : 2044
Total Pages Failing
(Data) : 0
Total Pages Processed (Index): 733
Total Pages Failing
(Index): 0
Total Pages Empty
: 5686
Total Pages Marked Corrupt
: 0
Total Pages Influx
: 0
OS Commands and Utilities


unixenv
oraenv
> oraenv
ORACLE_SID = [QHIS] ? THIS
ORACLE_HOME = [/home/oracle] ?

ipcs -a
T
ID
KEY
MODE
QNUM QBYTES LSPID LRPID
STIME
OWNER
RTIME
GROUP
CTIME
CREATOR
CGROUP CBYTES
Message Queues:
q
0
PID
0 0x3c180330 -Rrw--w--wroot
root
0 32768
0
0 no-entry no-entry 13:48:08
LPID
ATIME
DTIME
root
root
root
sys
oracle
dba
CTIME
Shared Memory:
m
0 0x2f140002 --rw------2 1286144 894 2:16:36 no-entry
m
10 0x83e2a3c0 --rw-r----oracle
19 82288640 22369 24272 16:45:30 16:56:48
T
ID
KEY
NSEMS OTIME CTIME
MODE
root
2:16:36
sys
dba
9:00:59
OWNER
GROUP
CREATOR
CGROUP
root
sys
root
sys
Semaphores:
s
0 0x2f140002 --ra-ra-ra-
Files that Grow by Default

the listener.log file
$ORACLE_HOME/network/log

the alert_{SID}.log file
$ORACLE_BASE/admin/{ORACLE_SID}/bdump

default audit files
$ORACLE_HOME/rdbms/audit

background dump files
$ORACLE_BASE/admin/{SID}/bdump
$ORACLE_BASE/admin/{SID}/cdump
$ORACLE_BASE/admin/{SID}/udump
Where to Now?
 There are many discussion Newsgroups on the internet for you to give questions
and get answers:
comp.databases.oracle.server
comp.databases.oracle.tools
comp.databases.oracle.misc
 These can be accessed through a newsgroup program or “www.deja.com”
 Ari’s free Oracle Tips web page at:
There are over 370 tips and answers to questions that have been posed to me over the
years. This paper will be downloadable from the web page as well.

Other good sites with links: www.orafaq.org, www.orafans.com, www.ioug.org,
www.orasearch.com, www.revealnet.com, www.lazydba.com, www.dbdomain.com