Managing indexes
From: GopalaKrishnan K gopalak@wiproge.med.ge.com
To: oracle list oracle@telelists.com
Cc: oracle list oracle@telelists.com
Date: Thursday, June 24, 1999 10:55 AM
Subject: Index
How do i send my existing indexes to a separate
index table space.
I want to send my primary key and unique key
indexes to a separate table space.
can anyone tell me how to do this.
My database is 24*7 .
I can not shutsown db for this
g
From: Tom Pall tom@lexitrans.com
To: GopalaKrishnan K gopalak@wiproge.med.ge.com; oracle list
oracle@telelists.com
Cc: oracle list oracle@telelists.com
Date: Friday, June 25, 1999 09:06
Subject: Re: Index
I have been doing this quite often, as a growing 24 X 7 production
database's indexes often grow
faster than anticipated.
Issue an alter index index_name rebuild
with explicit storage and tablespace parameters. Oracle rebuilds
the index in the new tablespace
as a temporary segment using the values in the old index then
momentarily takes an exclusive lock
while it changes the temporary segment to an index segment with the
new name, renames the
old index segment. It then drops the old index segment.
I "promote" primary, unique and other indexes from small extent to
medium extent or medium extent
to gigantic extent tablespaces regularily. Works great.
Produces lots of redo. Make allowances for that.
Date: Fri, 25 Jun 1999 11:25:36 +0530
From: "GopalaKrishnan K" gopalak@wiproge.med.ge.com
To: "Tom Pall" tom@lexitrans.com
Cc: "Appslist" oraapps-l@cpa.qc.ca, "DBAlist" oracledba@themailinglist.net,
Subject: Re: Index
Hi
i would liek to know one more thing.
Oracle creates index by default for primary key and unique
constraints. My doubt is about these indexes only.
By default where these indexes go and sit.
In system tablespace or the tables' default tablespace.
How do i move these indexes to the new tablespace if these
stays on system tablespace. same as other index rebuliding
or any special care to be taken/
gopal
process.dat, regid.dat files
Date: Tue, 22 Jun 1999 09:37:23 PDT
From: J P Bhamu jpbhamu@hotmail.com
Subject: process.dat, regid.dat files??
Hi All,
Can someone pls suggest me how to turn the tracing off in the
$ORACLE_HOME/otrace/admin dir. There are some process.dat, regid.dat files
which are ever increasing in size.
My config is ;
HP-UX 10.2, RDBMS 7.3.3.5, Apps 10.7 NCA and I have around 4 instances
running on this machine.
thanks
Date: Tue, 22 Jun 1999 14:00:11 -0300
From: Melanie Perry Melanie.Perry@moncton.org
Subject: RE: process.dat, regid.dat files??
Make the env variable EPC_DISABLED=TRUE.
Date: Wed, 23 Jun 1999 18:39:21 +0100
From: "Jonathan Stuart" jdstuart@globalnet.co.uk
Subject: Re: process.dat, regid.dat files??
Hi,
These are Oracle trace files (not to be confused with SQL trace files) and I
believe that running otrccref, which can be found in $ORACLE_HOME/bin, will
reduce these files to their default (small) size. Then, by setting the
following environment variable
EPC_DISABLED=TRUE
and restarting your databases with this in your environment Oracle tracing
will be turned off. I haven't tried this so I can't guarantee it will work
but this is what I would try first.
Regards,
Jonathan.
cst_margin_temp table
From: Bharat Patel bpatel@DOMINOAMJET.com
Subject: cst_margin_temp table
Date: Mon, 28 Jun 1999 10:03:53 -0500
Hi,
Guys, above mentioned table is almost full in my instance. I would like
to know that can I delete/truncate all the records from it without affecting
the system?
I checked the tech reference manual and only it says that it is a temporary
table which is used for sorting info. for the MARGIN ANALYSIS REPORT.
thanks in advance.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
From: Yerraguntla Ravindra -G1447C G1447C@ga25epd.mot.com
Subject: RE: cst_margin_temp table
Date: Mon, 28 Jun 1999 13:12:16 -0400
Hi Bharat,
Don't truncate this table from SQL PLus instead, do that in
applications. The program "purge marginal analysis run" which 'll
purge/delete the records for the particular build name.
HTH
Ravindra Yerraguntla
Consultant
Motorola ESG
770 338 3659(W)
Date: Tue, 29 Jun 1999 05:42:07 PDT
From: DASARI SUBRAHMANYAM srdasari@hotmail.com
Subject: Re: cst_margin_temp table
Hi Bharat,
There is no problem if u delete cst_margin_temp and cst_margin_build tables.
These are populated when u run Margin Analysis Load Run. Make sure
whether users have any need to go back and run the report again for the past
margin builds. If there is no need u may delete them.
-dasari
Date: Tue, 29 Jun 1999 08:39:24 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
Subject: RE: cst_margin_temp table
Thanks guys, I ran the report which Yerranguntla mentioned yesterday. I
couldn't posted back that we figured it out message yesterday as your email
system was down.
Thanks once again.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
Deleting a Datafile
Date: Sat, 04 Apr 1998 18:27:23 +0200
From: aragab a_ragab@tccbce.com
Subject: urgent DLETE A DATFILE
hi all
can i remove a datafile after assign it to any tablespace.......
thanks
ahmed ragab
Date: Mon, 5 Jul 1999 19:28:47 -0400
From: "sudershan virdi" virdi@interlog.com
Subject: Re: urgent DLETE A DATFILE
you will have to drop the tablespace to delete the file.
if you can't do that, you can always resize the file using
alter database datafile 'xxx' resize xxxm.
or you can even place the datfile on a different mount point.
hope this helps.
sudershan
Storedprocedure to retrieve a set of record from oracle database server
Date: Mon, 8 Feb 1999 04:11:38 -0500 (EST)
From: Yuliana yuliana@elang.stts.ac.id
Subject: StoredProcedure Can Return A Set Of Records !!
I already use oracle and Delphi in my application. how can i use
storedprocedure to retrieve a set of record from oracle database server.
Then i will put the records that i get to dbgrid.
Thanks For All Attention !!
Yuliana
Date: Mon, 8 Feb 1999 17:27:31 -0500 (EST)
From: Bharat Patel bpatel@DOMINOAMJET.com
Subject: RE: StoredProcedure Can Return A Set Of Records !!
you can user the out parameter as record type. Also as per oracle 8 you
can use the who table or sets of table by define record type. you can
find it out in new feature of oracle 8.
Bharat Patel
DBA
bpatel@dominoamjet.com
Date: Mon, 8 Feb 1999 20:25:01 -0500 (EST)
From: Yuliana yuliana@elang.stts.ac.id
Subject: StoredProcedure & %Rowtype !!
I am sorry if i didn't say my question correctly.
A set of record that i have to send to DELPHI is a set of record that
already become a member of one table. So i have to use type %rowtype in
out parameter. Also i must have one variable that already declare with
same type with the table to retrieve the result from my procedure. Because
i have to send this variable to DELPHI.
Locks on partial / full tables
Date: Wed, 07 Jul 1999 15:53:53 -0600
From: "Rosy Virani" Rosy.Virani@CRHA-Health.Ab.Ca
Subject: Requisition lines locked by another user
Help,
One of the user had to end the task, because of the error frm-99999 message
- this message appeared when the user was in process of autocreate.
User tried to auto create PO - when ever user tries, it gives this error
message line # of req 12428 is currently locked by another user.
This happened yesterday. I dont have any login information about this user
in the FND_USER tables.
Any suggestions will be greatly appreciated.
Rosy.
Date: Wed, 7 Jul 1999 16:19:04 -0700
From: skirby@bco.com
Subject: RE: Requisition lines locked by another user
I would check the v$session table and v$locks for locks on those tables.
I stole this script from a DBA that was kind enough to post this a while
back ...
just cut and paste ..
It doesn't solve the problem, but it will point you in the right direction
for sessions to kill to relieve locks. This is a good tool.
******************************************
set pages 60
set lines 132
spool get_locks
prompt System Locks
prompt ##############
prompt
column "ORA USER" format A10
column "OS USER" format A10
column "OBJECT NAME" format A25
column "LOCK TYPE" format A15
column "MODE HELD" format A10
column "MODE REQ." format A9
column "BLOCKING" format A12
column "SID" format A8
SELECT TO_CHAR(lk.sid) "SID",
serial#,
oracle_username "ORA USER",
os_user_name "OS USER",
DECODE(lk.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
lk.type) "LOCK TYPE",
DECODE(lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(lmode)) "MODE HELD",
DECODE(request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(request)) "MODE REQ.",
DECODE(block,
0, 'Not Blocking', /* Not blocking any other processes */
1, 'Blocking', /* This lock blocks other processes */
2, 'Global', /* This lock is global, so we can't
tell */
to_char(block)) "BLOCKING",
object_name "OBJECT NAME"
FROM v$locked_object lo,
sys.dba_objects do,
v$lock lk,
v$session s
WHERE (lo.object_id = do.object_id)
AND (lk.sid = lo.session_id)
AND (lk.sid = s.sid);
spool off
******************************8
Steve Kirby
skirby@bco.com
Date: Thu, 19 Aug 1999 08:01:57 +0300
From: "Lavy, Shay" Shay_lavy@icomverse.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: Requisition lines locked by another user
Hi Rosi,
My company also suffers from locked records in that auto create form.
I would like to know on which release are you working.
Also, have you found a solution for your problem by now ? and if so - how
?
We are on Apps 11.0.1 (going for 11.0.3)
DB 8.0.5
Regards,
Shay Lavy
ITA Department
Comverse Network Systems
Phone no. : 972-(0)3-765-5794
Cell Phone: 972-(0)51-325794
Attachements in 11.0.2 (preview and update)
Date: Thu, 8 Jul 1999 01:00:59 +0200
From: Alexandre_Gravelin@ernst-young.fr
Subject: Attachements in 11.0.2 (preview and update)
Hi all,
I've some problems with teh attachments in 11.0.2 : How is it possible to
have a preview of word or excel attachments without entering in it ? Is it
possible to modify them directly on the server (when I try to save my
updates, it ask me to choose a directory on my hard drive...) ?
This 2 functions were available in 10.7 SC with OLE objects...
Thanks for help
Alexandre GRAVELIN
France
GL Code Combination Indexes
Date: Sat, 19 Jun 1999 02:50:58 -0700 (PDT)
From: S F sf3010@yahoo.com
To: oraapps-dba@cpa.qc.ca
Subject: GL Code Combination Indexes
Hi,
Could someone tell me what are the pros and cons if I make concatenated
indexes on account segments in Oracle GL instead of single segments.
Would it be okay if I created/maintained these indexes instead of
running the GL Code Optimizer?
Thanks in advance,
Regards,
AFG
Date: Mon, 21 Jun 1999 06:17:49 -0700 (PDT)
From: Ted Sasscer tsasscer@yahoo.com
To: oraapps-dba@cpa.qc.ca
Subject: Re: GL Code Combination Indexes
AFG,
It's ok to do that.
Another option, which I use is, let GL Optimizer create the indexes,
then go through and delete all but the 2-5 most selective ones, and
make sure you keep the index(es) that the users query on most often.
Many queries on GLCC use all segments in the where clause, so if there
are only a couple very selective indexes to choose from it will be
efficient.
To determine index selectivity without creating statistics, I run for
each column:
SELECT count(distinct &segment) from gl.gl_code_combinations ;
Ted
Baytree Associates
Views and indexes on base tables
Date: Mon, 21 Jun 1999 20:11:23 +0200
From: amy davidson amy@itsyn.it
To: oraapps-dba@cpa.qc.ca
Subject: views and indexes on base tables
I have the following query and I don't know how to
tune it to make the view merge to use the base tables
and thus the indexes on the base tables.
select compname,
mainname
from maintainance,
component
where maintainance.compid = component.compid (+);
There is an index on compid column for maintainance table.
Component is a view made up of 2 tables: componentunit and
componenttype. This is the component view
select cu.compid,
ct.comptypeid
from componentunit cu, componenttype ct
where cu.compid = ct.compid
The table componentunit has an index on compid.
Any help would be appreciated.
Regards,
amy
Transaction count
Date: Tue, 29 Jun 1999 16:24:17 +0800
From: Chow Chee Weng cheeweng@singtel.com
To: "'oraapps-dba@cpa.qc.ca'" oraapps-dba@cpa.qc.ca
Subject: Transaction count
Hi All,
I'm currently running PeopleSoft 5.8 on Oracle 7.3.4 .
Is there a way to capture the number of transactions processed over a
particular period?
How about the response time of the database?
Many Thanks.
x-------------x
--- "McClung, Ed D." EDMCCLUNG@escocorp.com wrote:
Date: Mon, 12 Jul 1999 09:58:44 -0700 (PDT)
From: Bill Bertolotti billbert@yahoo.com
To: oraapps-l@cpa.qc.ca, Multiple recipients of list oraapps-l@cpa.qc.ca
Subject: Re: How does find out how many online transactions were processed
Ed,
In most of the applications, not all, there exist reports that can
isolate a period's (day, week, month) activity. By themselves these
reports will not give you just manually entered transactions, but with
some creative use of things like unique invoice types in AP, and unique
transaction types in AR, etc, these reports can be passed parameters to
show manual activity in the applications. Also if this is not what you
are looking for, note columns on the database side like CREATION_DATE,
CREATED_BY, SOURCE, JE_SOURCE_NAME, etc. These columns are populated
differently by different sources of data entry into the system.
Bill Bertolotti
Date: Mon, 12 Jul 1999 20:57:36 -0400
From: fushan@gamma.aei.ca
To: oraapps-l@cpa.qc.ca
Subject: Re: How does find out how many online transactions were processed
well check with applsys.fnd_concurrent_processes table. It has all
the record of all transactions until you run a purge job. There is a
column called completed_date and start_date in
fnd_concurrent_processes table. So it will be very easy for you to
select from fnd_concurrent_processes table by given the start_date
or completed_date
vFu-shan Louisa Leong
Oracle DBA, Oracle Financial Application
Andersen Consulting
Table/Index sizing
Date: Wed, 14 Jul 1999 11:13:25 -0500
From: Diana Boules dboules@csac.com
Subject: Table/Index sizing
Does anyone have a spreadsheet or other automated way to size tables and indexes
using the sizing calculations from the DBA guide?
I am getting a lot of requests for sizing tables and don't want to have to do
this by hand. I used to have a spreadsheet, but somewhere along the way I've
lost it.
I would appreciate it if someone could share this tool with me.
Thanks,
Diana
mailto: dboules@csac.com
Rollback Segment specific to an Apps program
Date: Thu, 15 Jul 1999 12:04:35 +0400
From: mzaveri@eppco.co.ae
Subject: Rollback-seg
I am planning to allocate the big segment to one of our long running
invoicing program, which is running short of rollback segment. Please
confirm following steps :-
1) Create a big rollback segment (as per the need of invoice)
2) Tell progs to access this segment explicitly using dbms_transaction and
use_rollback_segment
Now my query is, what is the guarantee that this rollback segment will not
be used by some other transaction when this invoicing program needs??
Should we put online and offline statements also in program ( i.e. online
while needed and take back offline once finished. All from within invoicing
program).
Any comments would be highly appreciated!
mehul.
Date: Thu, 15 Jul 1999 11:37:52 -0700
From: Ron Leedy rleedy@us.oracle.com
Subject: Re: Rollback-seg
Don't make it a "PUBLIC" rollback segment.
Date: Thu, 15 Jul 1999 13:26:43 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
Subject: RE: Rollback-seg
Mehul I am using the same technique and when ever I needed I bring the big
rollback seg online and rest of all offline. Also we are using the time when
all other users are not on the system.
If you can't make that arrangement then you have to increase the size of the
rollback segments.
thanks.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
All_errors
From: Kik Nichol [mailto:m_sharawy@tccbce.com]
Sent: Tuesday, July 13, 1999 1:14 PM
Subject: DBA:all_errors
hello
I just learn that I have aobject all_errors I run select stattment I got
this
Pl. click here to see the errors
Date: Tue, 13 Jul 1999 15:12:49 -0500
From: "Hostetler, Don" Don.Hostetler@impacgroup.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: all_errors
First compile all invalids.
$AD_TOP/sql/
sqlplus
adcompsc.pls APPS APPS%
Second compile all grants and synonyms via adadmin.
Regards,
Don Hostetler
Date: Tue, 13 Jul 1999 15:39:17 -0500
From: Bharat Patel bpatel@DOMINOAMJET.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: all_errors
you have invalid objects in apps. you need to compile it.
two ways to do it
1. compile the apps schema.
2. compile individual objects.
hope this helps.
Bharat Patel
D.B.A.
bpatel@dominoamjet.com
847-244-2501 ex 1249
Date: Tue, 13 Jul 1999 20:01:29 -0400
From: fushan@gamma.aei.ca
To: oraapps-l@cpa.qc.ca
Subject: Re: DBA:all_errors
This table will give you some hint when your object has problem.
If you try to re-compile CE_AUTO_BANK_CLEAR package body,
it returns you error. I suggests you should double check with
CE_AUTO_BANK_CLEAR package. make sure it is valid. If yes,
that mean it was problem but fixed. If it is not valid, the problem is
just because the package specification is not there according to the
dba_errors table.
Fu-shan Louisa Leong
Oracle DBA, Oracle Financial Application
Andersen Consulting
Date: Tue, 13 Jul 1999 17:01:21 +0000
From: Arvind Gidwani agidwani@cisco.com
To: oraapps-l@cpa.qc.ca, Multiple recipients of list oraapps-l@cpa.qc.ca
Subject: Re: DBA:all_errors
This object just stores errors for every single package , procedure etc
and is non harmful.
Whenever you have an say error in a package or a procedure
you issue a statement
SQL show errors package CE_AUTO_BANK_CLEAR
The DB will select from this table and display to your screen the errors
for this package
FYI - Arvind
Unable to extend index
Date: Sat, 17 Jul 1999 18:02:52 +0400
From: Chetan Banglorewala ChetanB@allied-medical.co.ae
To: oraapps-l@cpa.qc.ca
Cc: orapps-dba@cpa.qc.ca
Subject: ORA 1654 unable to extend index
Hello DBA's
We are implementing 10.7 SC. I am a functional person. Unfortunately, we are
working currently without a DBA. I have got the following error,
ORA 1654 unable to extend index APPLSYS.FND_CONCURRENT_REQUESTS_N6 by 16 in
tablespace FNDX.
Can someone please advice me on the SQL statement or otherwise to rectify
the above.
Regards,
Chetan Banglorewala
Date: Sat, 17 Jul 1999 07:34:56 -0700
From: Karen Blackwell KBlackwell@rockshox.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: ORA 1654 unable to extend index
Here are a couple of scripts I have created for extending Indexes and
Tables. The only concern you now have is whether or not there is adequate
table space defined.
/*
extend.sql
This script will alter the max extents of the specified table.
&tablename will prompt for the name of the table
&value prompts for the "new" max_extents.
The proper entry to the prompt for tablename is OWNER.TABLE_NAME
*/
alter table &tablename storage (MAXEXTENTS &value)
/
$ more extindex.sql
/*v
extindex.sql
This script will alter the max extents of the specified index.
&index will prompt for the name of the index
&newvalue prompts for the "new" max_extents.
The proper entry to the prompt for index is OWNER.TABLE_NAME
*/
alter index &indexname storage (MAXEXTENTS &newvalue)
/
I hope these help you. The comments explain the scripts for you. (Being a
new Oracle DBA, I sympathize with your current position of not having a
DBA.)
Cheers!
Karen Blackwell
DBA / SA
RockShox, Inc.
Date: Sun, 18 Jul 1999 10:19:10 +0400
From: Chetan Banglorewala ChetanB@allied-medical.co.ae
Subject: RE: ORA 1654 unable to extend index
Hi Karen,
Thanks for your response.
Extending the extents did not resolve the issue. I then altered the
tablespace FNDX by adding datafile.
Regards,
Chetan
Date: Mon, 19 Jul 1999 08:09:50 -0300
From: Melanie Perry Melanie.Perry@moncton.org
Subject: RE: ORA 1654 unable to extend index
Hi Chetan
If you have Oracle RDBMS version 7.3 or later, there is an ALTER command
that can RESIZE your tablespace :
ALTER database datafile "datafile name" resize ###MB;
You would only add a new datafile if the current one is near 2 Gig. There
is no steadfast rule that I know of except that once the datafiles get that
large, they are hard to manage (ie move, etc). I could be wrong but this
is what I go by anyways.
Melanie Perry
SSA \DBA
The City of Moncton