|
 |
| Recent blog posts: | |
|
| They have birthday today: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Thursday, 10 August 2006
|
| TEMP tablespace Airmax 05:10:25 |
| |
Hello DBA’s
I have a problem with my TEMP tablespace. It’s 1 GB and its full.
Shouldn’t be empty? When the tablespace is full i have to restart the
database in order clean up the tablespace, but this is not a
solution!!! I don’t want to extend it becuase it will be full again. In
my previous job the table space was 800MB and it was always empty.
What I should do?
Regards
Panos
--
airmax
------------------------------------------------------------------------
airmax's Profile: http://www.dbtalk.net/m627
View this thread: http://www.dbtalk.net/t324583
|
| |  1 answer | Add comment |
|
| EXCEPTION INTO table storage Guest 02:10:43 |
| | Can someone point me to location of where Oracle stores reference table
for EXCEPTION INTO constraint option.
I have the following contstraint:
ALTER TABLE MY_Table ADD
CONSTRAINT MY_Table_UQ UNIQUE (I1)
DEFERRABLE
INITIALLY IMMEDIATE ENABLE VALIDATE
EXCEPTIONS INTO MY_Table_EXCEPTIONS
I was not able to find dependence reference between MY_TABLE and
MY_TABLE_EXCEPTIONS. I have checked ALL_CONSTRAINTS, ALL_DEPENDENCIES,
ALL_TABLES
Oracle 9.2.0.7
|
| | 1 answer | Add comment |
|
| create dynamic variables Mgmonzon 01:12:12 |
| | Hi Everybody,
Is posible to create dynamic variables in a PL/SQL procedure?
This is my problem, I have a table called TABLE_VARIABLE with this
values :
#VAR_NAMEVAR_VALUE
------- --------------- ------------------------
1SALARY1000
2DISCOUNT300
3NET(SALARY - DISCOUNT)
I want create a procedure where the variables names should be the same
name defined in the table TABLE_VARIABLE.
The user is able to create ne records and new variables names an the
names should be processed like a pl/sql variables in order to store
the result information in other table.
Sample :
Create or replace read_variables as
Begin
for reg in (select VAR_NAME, VAR_VALUE from TABLE_VARIABLE ) loop
<create variable defined in the field VAR_NAME in TABLE_VARIABLE> ;
<Assign value using the field VAR_VALUE in TABLE_VARIABLE> ;
End Loop ;
End ;
Is possible do that?
Mgmonzon
|
| | 9 answers | Add comment |
|
| to_char number format with optional decimal-point? Martin T. 00:14:11 |
| | Hello all!
Basically, what I want is the following.
Num -> Str
1 -> '1'
10 -> '10'
1.01 -> '1.01'
Is there a possible Number Format Model that will not display the
decimal if there are no decimal digits?
(I know this is the default behaviour of to_char, but I need aditional
formatting, so I need to spec the format model.)
Related to this: Is it possible to specify a number-format model that
will exactly reproduce the to_char default behaviour?
thanks!
best,
Martin
|
| | 7 answers | Add comment |
|
| 9i Client Against 10g and Upgrade Gotchas Oracle_User 00:06:15 |
| | Fairly new to Oracle and my boss is asking me to upgrade 8i (8.1.7) and
9i (r1 and 2)databases to 10g. I'm going to read the upgrade guides!!
but a couple of questions
1. Can the 9i client be used against 10g and if so will it cause me
any problems
2. Are there any docs beyond the oracle provided stuff that will
prepare me for any upgrade problems I might hit?
Thanks
|
| | 3 answer | Add comment |
|
| 2nd table Guest 00:02:48 |
| | What is the easiest way to update a 2nd table based on data in a
materialized view of a 1st table? The 1st table and 2nd table should
be identical following the update.
Thanks.
|
| | 2 answer | Add comment |
Wednesday, 9 August 2006
|
| Cascade Update Trigger Problems Guest 23:57:26 |
| | Does anyone know how to use a trigger to update a table (& all child
tables) so that an existing pk is updated to another existing pk. For
instance, merging records so that:
Key CompanyName
1 GE
2 General Electric
3 general electric
4 G.E.
All become one single record. I would even be satisfied by assigning
all 4 of these into a 5th record as long as all related records are
attached. These also have a number of child records associated with
them as well (orders, employees, etc).
Thanks
|
| | 8 answers | Add comment |
|
| Denormalizing a database table Guest 23:51:42 |
| | Folks,
Any help with creating a SP that denormalizes the below table to the
desired output will be appreciated.
Current Input Table Structure:
SNum KeyName KeyVal value
1 SCM1 name DFW
1 SCM1 OVal 200
1 SCM1 OSum 500
2 SCM2 name ORL
2 SCM2 OVal 100
2 SCM2 OSum 200
3 SCM1 name DFW
3 SCM1 OVal 100
3 SCM1 OSum 100
Desired O/p in a secondary temp table
DFW 200 500
ORL 100 200
DFW 100 100
Rgds
Alk
|
| | 4 answer | Add comment |
|
| configure service name in RAC Cs 23:00:13 |
| | Hi,
I have created a service name, appserv in my 3-node RAC environment.
$> srvctl status service -d dbrac -s appserv
appserv PREF: inst1 AVAIL: inst3 inst2
When inst1 crashed, all sessions were re-routed to inst3. However, when
inst1 was restarted, the appserv service was not added back in inst1
(did not appear in "show parameter service_names").
Subsequent new connections will always connect to inst3. My
understanding is if the preferred instance, inst1 is made available
again, subsequent new sessions should automatically get connected to
the preferred instance. Is that correct? How can I configure the
service name to do that?
Also, I'm not sure why during a failover, all sessions were re-routed
to only inst3. I expected the failed over sessions to get distributed
between inst3 and inst2 since I have those two instances in my
available list of instances and listener load balancing was also turned
on.
Can someone please assist?
Thanks.
chris
|
| | 1 answer | Add comment |
|
| Materialized view refresh and very long wait events G3000 22:56:27 |
| | Hello,
Im on 9.2.0.7 EE AIX v5 The server is a 3 CPU box and the disk
system is "SHARK" (whatever that is ). 8 DBWR's . I have a materialized
view with 31 million records in a base table log. The other logs have
are empty except one that has 5000.
I have indexes on all the rowids ( all local to the partition )
materialezed view and a composite primary key.
create materialized view dis_mv2
PARALLEL
PARTITION BY HASH(store_id)
PARTITIONS 8
STORE IN (DISCOVERER_DATA,
DISCOVERER_DATA,
DISCOVERER_DATA,
DISCOVERER_DATA,
DISCOVERER_DATA,
DISCOVERER_DATA,
DISCOVERER_DATA,
DISCOVERER_DATA)
NOCACHE
NOLOGGING
build immediate
refresh fast on demand
as
SELECT /*+ index(itmloc pk_as_itm_rtl_str) index(soh
pk_rk_store_item_soh) index(ris pk_rk_item_supplier) index(itm
pk_as_itm)*/
psr.id_str_rt store_id,
psr.rowid psr_rid,
po.nm_orgn store_name,
po.rowid ps_rid,
soh.id_itm item_id,
soh.rowid soh_rid,
SUBSTR(itm.id_strc_mr,1,INSTR(itm.id_strc_mr,':')-1)
Dept,
soh.id_itm ||'-'|| itm.de_itm id_desc,
itm.de_itm item_description,
itm.lu_uom_dft default_uom,
itm.rowid itm_rowid,
itmloc.id_spr primary_supplier,
itmloc.rowid itmloc_rid,
ps.nm_spr vendor_name,
ps.rowid ps_rowid,
itmloc.id_spr ||'-'|| ps.nm_spr VDesc,
ris.vpn vendor_style,
ris.rowid ris_rid,
itmloc.repn_type repn_type,
soh.total_quantity soh,
soh.in_transit_quantity in_transit_quantity,
soh.adjust_unavail_qty unavailable_quantity,
aq.allocated_quantity,
aq.rowid aq_rid,
ooq.on_order_quantity,
ooq.rowid ooq_rid,
r.rowid region_rid,
decode( sign(soh.total_quantity), 0, 'ZERO', -1,
'NEGATIVE' ) status
FROM sim.pa_str_rtl psr,
sim.as_itm_rtl_str itmloc,
sim.as_itm itm,
sim.rk_item_supplier ris,
sim.pa_spr ps,
sim.rk_store_item_soh soh,
sim.pa_orgn po,
sim.alloctab aq,
sim.on_ord_qty ooq,
sim.regionA r
WHERE itmloc.id_str_rt = psr.id_str_rt
AND itmloc.id_itm = itm.id_itm
AND ooq.store_id(+) = soh.id_str_rt
AND ooq.item_id(+) = soh.id_itm
AND itmloc.id_itm = ris.id_itm
AND itmloc.id_spr = ris.id_spr
AND ris.id_spr = ps.id_spr
AND aq.id_str_rt(+) = soh.id_str_rt
AND aq.id_itm(+) = soh.id_itm
AND soh.id_str_rt = itmloc.id_str_rt
AND soh.id_str_rt = r.storeid
AND soh.id_itm = itmloc.id_itm
AND psr.id_prty = po.id_prty_orgn;
I attempted a fast refresh and it was still running after 18 plus
hours. I finally issued a kill. The below is what I got.
free buffer waits:
Total Waits = 87201514
Total Timeouts = 34172
Time Waited = 2437656
db file sequential read:
Total Waits = 87201514
Total Timeouts = 0
Time Waited = 5155028
db file scattered reads:
Total Waits = 59886
Total Timeouts = 0
Time Waited = 42692
write complete waits
Total Waits = 492
Total Timeouts = 33
Time Waited = 27412
is the problem my local indexes on my partitions? Or is the server and
IO sub system too small? Should I do a complete vs a fast refresh for
this amount of data? BTW the trace file had a bunch of these
WAIT #1: nam='pipe get' ela= 1953135 p1=504403159891195536 p2=4096
p3=600 before it stopped ( which I think have to do with tracing )
Thanks for your help.
|
| | 2 answer | Add comment |
|
| Refresh my Oracle Database Plactoon 22:38:17 |
| | Hi,
My problem is,
i have two databases in diferent servers, one is the production
database and the other is the test database, now, the production
database has changed, new tables, data, etc...
what i need is to refresh my test database, tablespaces, schema, etc,
but no data,
my question is, what is the best way to do this?, iВґm using oracle 9i
on solaris.
thx.
Plactoon
|
| | 2 answer | Add comment |
|
| Solaris 10 System Boot and Database Startup Michael42 21:56:33 |
| | Hello,
On a Solaris 10 (sparc) system I am unable to get my Oracle 10
listener to start using a technique that I commonly use on Solaris 8
systems. My database comes up OK and the script I have will start both
the database and listener if I run it manually AFTER the system has
booted. In the /ect/init.d my is basically:
==================================================================
#!/bin/sh
#
# CREATE pfile'/usr1/home/oracle/product/10.2/dbs/initDB1.ora' FROM
spfile;
# Set Variables ORA_HOME should be equivalent to the ORACLE_HOME
ORA_HOME=/usr1/oracle/product/920
ORA_OWNER=oracle
LOGDIR=/usr1/oracle/admin
case "$1" in
'start')
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart 2>&1 >
$LOGDIR/dbstart.log
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start" 2>&1 >
$LOGDIR/lsnrstrt.log
;;
'stop')
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop" 2>&1 >
$LOGDIR/lsnrstp.log
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut 2>&1 >
$LOGDIR/dbshut.log
;;
esac
==================================================================
On system boot when it runs, the error I am getting is that the network
is unavailable.
Can anyone please share your Sun UNIX script that starts your Oracle
database and listener (and any required OS changes etc.)?
Thanks,
Michael42
|
| | 11 answers | Add comment |
|
| queue up changes Guest 20:54:20 |
| | Is there a way for Oracle to queue up changes to a materialized view
and then only transmit those changes across a DB link once a day?
It is important that it doesn't send the full tables.
|
| | 1 answer | Add comment |
|
| automatically configure materialized views Guest 20:37:20 |
| | I heard that Oracle has some utility that will automatically configure
materialized views for replication. Does anyone know of anything like
this?
|
| | 1 answer | Add comment |
|
| Adding a database to VCS cluster Nirav 20:01:00 |
| | Hi ,
Can you give me an idea of what steps are needed to add a database to
an existing VCS cluster? We have a database in standalone server, and
there is a need to move it to Veritas clustered environment -To move it
to another server groups where we have oracle 9i on VCS (on solaris 8).
any documents/links would be very useful to me.
With thanks,
Nirav.
|
| | 3 answer | Add comment |
|
| imp-00015 for PK constraint Ben 18:55:24 |
| | running 9.2.0.5 on AIX 5.3, LMT's
I'm trying to duplicate a schema within an instance. I used OEM to
create a user "like" the user/schema I want to duplicate. Then I
revoked unlimited tablespace from that new user DP2 and gave it
unlimited quota on a tablespace that I created for that user.
When I run the imp using the following params I get an imp-00015 on
certain primary key constraints. The odd thing is I don't get this for
all pk constraints. How can I get around this? Just build the pk
manually after the imp?
Here are the params:
fromuser=dp
touser=dp2
ignore=n
full=n
grants=y
rows=y
indexes=y
commit=y
Thanks for any help,
|
| | 4 answer | Add comment |
|
| loadjava and Java version Guest 17:37:43 |
| | Hello,
I'm trying to upload a Java class into an 8.1.7 database as a Java
Stored Procedure. I issue the following command:
loadjava -u <user>/<pass> -v -resolve MyClass.java
The following error is displayed:
ORA-29535: source requires recompilation
MyClass:61: Method split(java.lang.String) not found in class
java.lang.String.
The error refers to the following line of code:
String[] response = returnString.split(",");
The variable returnString is a String. After doing a "java -version"
and finding that the server is running version 1.5.0_01, I checked the
1.5 API and found that there *is* a method called "split" in the class
java.lang.String.
So, I'm confused as the class has such a method, yet Oracle seems to
think that it does not. Any ideas as to what am I doing wrong?
Thanks for any assistance.
Oaf
|
| | 5 answers | Add comment |
|
| SQL script to insert user responsibilities Guest 17:26:55 |
| | IВґm looking for a SQL script to add Oracle userВґs responsibilities,
bypassing Oracle forms.
Is there any way ?
|
| | 1 answer | Add comment |
|
| UNDOTBS Guest 16:19:12 |
| | Oracle 9i env, tbs is in MANUAL management mode and auto extend is off.
We have a large update script which filled up about 80% of a 20G
UNDOTBS. The script crashed and we plan to reload the schema from a
backup and run the script again, however the UNDOTBS is still sitting
at 80% full.
Will Oracle know that this rollback data in the UNDOTBS is not required
and start over writing it when we run the update script again, or do we
need to perform some manual intervention to "flush" this data?
We dont want to rerun the update script and have it fail becaues the
UNDOTBS tablespace fills up.
Thx in advance.
|
| | 4 answer | Add comment |
|
| PLS-00103: Guest 15:38:01 |
| | Hi,
I am trying the following and the error i am getting is also pasted.
OS: solaris 9
DB: SQL*Plus: Release 9.2.0.1.0
I am lo
create procedure sample6 2 a:=1 number;
3 b:=1 number;
4 c number;
5 begin
6 c:=a+b;
7 end;
8 /
Warning: Procedure created with compilation errors.
show error Errors for PROCEDURE SAMPLE6:
LINE/COL ERROR
--------
-----------------------------------------------------------------
2/1 PLS-00103: Encountered the symbol "A" when expecting one of
the
following:
( ; is with authid as cluster compress order using compiled
wrapped external deterministic parallel_enable pipelined
|
| | 2 answer | Add comment |
|
| Fast utility to transfer data from one database to another excluding IMP/EXP Aman.Oracle.Dba 11:57:05 |
| | I have nearly 20 billion records in table. Now I have to transfer data
to another database without IMP/EXP.
|
| | 24 answer | Add comment |
|
| Oracle RAC newbie Guest 06:52:06 |
| | I am newbie to RAC. some very basic questions
let's say we have a two nodes RAC, does it means
- Two system tablespaces
-Two Temp tablespaces
- Redo logs for each instance in its own disk
- The only thing is shared are User Datafiles and they must be in SAN
- What has to be in both init.ora minimun?
- Database create command is difference or not? Can you send me the
script as sample?
- When I connect from sqlplus from another PC what exactly happen?
_ what is differenne in listenetr.ora and =tnsnames?
- AS soon as one node crash what happens?
- Who do the load balance?
- How do you value RAC to Hot Standby honestly?
Sorry too much questions but shed a brief light give me idea. Thanks
millions
|
| | 22 answer | Add comment |
|
| Java functions and the Oracle optimizer Dan 04:41:35 |
| | We seem to be having an issue where SQL statements that call a function
written in java behave very differently than the same sql without the
java function. For example, with java in the SQL a hash join is used
and a large (23G) table is hashed into memory instead of the tiny 64M
table it is joining to. Even when we put a hint in the code that says
USE_NL (use nested loop), the sql ignores it and still does a hash
join.
Is this common practice, does the optimizer get confused when
non-native functions like java are called?
Any help or advice as to how to get the nested loop to work or how to
get the optimizer to behave would be greatly appreciated.
Dan
|
| | 1 answer | Add comment |
|
|
