logs archiveIRC Archive / Freenode / #oracle / 2009 / December / 3 / 1
BUCKS
hello all
mikmaq
hi
TheBonsai
y0
Myish1
Quick question, I'm trying to figure out how to decrease a sequence if an insert containing seq.nextval fails. I've caught my exceptions and rollback my work, but the sequence still increases. Any thoughts?
TheBonsai
think about the rollback scenario again, then you might understand why they didn't do it :)
sequences are guarenteed to increase, they're not guarenteed to have no holes
Myish1
Right, but can I manually add code in my exceptions to decrease it by 1 then?
TheBonsai
mh, i only know ALTER SEQUENCE INCREASE
it seems you can't
it also won't give you a sequence without holes
Myish1
ya, it's just such a waste of numbers! :) I mean I'm using the sequence to build invoice numbers, and I don't want to just skip numbers when a creation fails
TheBonsai
if you must not have holes, a sequence is the wrong tool
a quick hack might be to have a "manual sequence", some table with an int column and only one row, you SELECT FOR UPDATE and +1 UPDATE inside your transaction, no other transaction can update it
or you pre-generate a set of numbers in some table
and mark them as used/free
and an invoice will get the first free one
         

Myish1
ya thats a good idea, I may go that route. I wonder how in-efficient it would be to get the last current seq value and if the insert fails drop the sequence and recreate it starting from the current value.
TheBonsai
well, a sequence is just metadata
it's nothing big
but it will be inefficient compared to not doing it
sequences are not made(!) for what you want to do
Myish1
:)
TheBonsai
they're guarenteed to increase and to be unique (never report the same value twice)
but that's all
Myish1
ya I just figured that their unique property would make them a good primary key candidate
Guest39766
"ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [], [], [], [], [], []" mwah
TheBonsai
interesting argv[1] heh
Myish1: i guess that's the primary purpose
Myish1: the problem with transaction logic is: imagine two transactions, A and B, after 1min B completes, after 2min A rolls back
Myish1: what should be the nextval?
Myish1
great call
TheBonsai
Myish1: that's the reason they're not covered under rollback
Myish1
I guess it's saving me from any potential headaches at the cost of a invoice number
TheBonsai
yes. but i think the pre-generated numbers would be okay
a rollback will also rollback that one row - your number is free again
Myish1
Interesting for sure. Thanks for the advice. Well bed is calling. Thanks again
TheBonsai
night :)
cmug
what is up with oracle.com these days, seems to be down 23h a deay
day*
or is it me and my proxies
boingolov
cmug, oracle.com is like an exclusive restuarant
or one of those too-hip-for-you night clubs with the bouncer and the velvet ropes and the "are you on the list?"
or maybe it's more akin to the occasional borg... "we will assimulate you. some day"
Reign1
hi, on 10g to send mails via apex, u must install these first: SQL> @$ORACLE_HOME/javavm/install/initjvm.sql SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql
what about 11g? as initplsj.sql not present
hi, how to make apex on 11g send mails?
TheBonsai
does it use the DBMS_ mailer package?
if yes, you might need to set a ACL to permit access
er
UTL_MAIL i mean
phimic
good morning
does Oracle-RAC uses scsi-2 or scsi-3 reservations for locking the voting disk?
Reign1
TheBonsai: i use htmldb_mail (apex_mail), and i kind of set ACL using this direction: http://download.oracle.com/docs/cd/B32472_01/doc/install.300/b32468/post_inst.htm#CHDCFCFA, besides i kind of get info that message processed, operation took 0.03 sec etc, but no mail comes
cmug
Since I don't know anything about Oracle Application server, should I be worried if at startup it says 0% cache hits?
or am I safe to assume that the cache builds up
looking at the histogram however I can see that the value was better
oh well, seems to work
         

mastro
hi, i have an ora-00942 trying to drop some table. I don't have the dbarole and i can't create/drop users so to delete all tables of my user I used the suggestion to drop all user table here: http://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdition/Oracle/user_guide/oracle_guide.html It dropped almost all the table but a bunch of them that I now can't delete anymore! i'm the owner of those table but even if I try to see their data
mastroDani
anyone replied? (i'm mastro my session went down.. I've some issue with proxy)
TheBonsai
you said you're not able to drop your own tables?
mastroDani
TheBonsai: exactly
TheBonsai
but it is a table and not a view?
mastro
lost connection again sorry
mator
is it table or view?
mastro
mator: table
TheBonsai
then a drop table has to work
you get 942?
mastro
TheBonsai: it doesnt
TheBonsai: yes i get 942
TheBonsai
weird
mastro
that's why i'm asking for help :)
TheBonsai
user_tables shows this table?
mastro
TheBonsai: any query i could execute that could help you?
TheBonsai: yes all of them
(check if i'm still here)
k
TheBonsai
and your current schema really is the logon schema?
i.e. you didn't alter session set current_schema or so?
does the drop work when you prepend the schema?
mastro
TheBonsai: i logged in as the user.. anyway if you tell me how can I check it i could
TheBonsai: tried to prepend the schema, it keep giving ora-00942
TheBonsai
some freaky upper/lowercase stuff?
mastro
TheBonsai: how can I check?
TheBonsai
googling, wait :)
mastro
k thank you
check i'm still here (sorry.. i had to connect through webchat.freenode.net and the proxy disconnect me, i've no mean to see if I'm still connected other then seeing if the messages reache the chat)
TheBonsai
your table names are all uppercase in user_tables.table_name?
(when object names are written inside double quotes, oracle respects case)
mastro
TheBonsai: doing a select on user_tables I see it as upper case if that answer your question
TheBonsai
i'm out of ideas. anybody?
mastro
and I tried with all upper case between quotes
TheBonsai
well, quotes would matter if it's not all uppercase
without quotes, oracle converts to all-upper
mastro
TheBonsai: i created those table
TheBonsai: and i did not used quotes
TheBonsai
as i said, i'm out of ideas :(
can you SELECT from those tables?
i.e. are they really there?
mastro
TheBonsai: no 00942
TheBonsai
logoff/logon, check user_tables
mastro
TheBonsai: already did :)
TheBonsai
usn_work: your guest :)
usn_work: user_tables lists tables he can't drop or select (ora-942 - doesn't exist)
mastro_
i was saying.. i could provide some background
I have 3 users: one is the owner and can create/drop/truncate/... on tables.. another is the application user: can't drop/create/truncate table but can do insert, select and so on... and the last can only to select (read only). I created the table / indexes with the owner and then gave the grants to my 2 other users, the logged with the other 2 users and created synonyms to all the tables.
when I dropped my tables with the owner (drop table NAME cascade constrains;) I forgot to drop the synonym from the other 2 users
it dropped almost all my tables but a bunch of them that now give 00942
mastro
I hope that could help you
(got disconnected again)
usn_work
mastro, did you check if they are views or synonyms?
both of them can appear in user_tables (sad)
TheBonsai
ah
ok
good to know
mastro
usn_work: no there is nothing else on user_tables
only my phantom-tables
usn_work: TheBonsai this is an example of select * from user_tables;
usn_work
give us select * from dba_objects where owner=<yourschema>;
select * from user_objects;
mastro__
I may have lost a part of the conversetion
the last thing I've read is: <mastro> usn_work: TheBonsai this is an example of select * from user_tables;
usn_work
select * from dba_tables where owner=<yourschema>;M
give us select * from dba_objects where owner=<yourschema>;
select * from dba_tables where owner=<yourschema>;M
select * from user_objects;
mastro__
usn_work: select * from dba_tables where owner=INI2_OWN; ««« give an ORA-00942
usn_work
become dba
for the beginning, show the user_objects please
mastro
i'm really sorry usn_work i lost connection again
---> the last thing I've seen <mastro__> usn_work: the second select: (select * from user_objects) gives a lot of things.. what should I look for?
usn_work
for tables
mastro_
usn_work: there are my tables and a lot of tables like: BIN$ec....==$0
TheBonsai
that's your recycle bin, don't care (or ''purge recyclebin;'')
mastro_
TheBonsai: ok.. i purge it
TheBonsai: or maybe I should wait (just in case)
TheBonsai: in my experience purgin is something you may regret if something is gone wrong and you are still trying to understand what
TheBonsai
the BIN$ tables are not your problem
mastro
(having fun with this crazy proxy .....)
usn_work
are they REALLY tables there?
can you filter the entries and show us in pastebin?
mastro
usn_work: I give you all the table in pastebin :)
TheBonsai
just write a useful SELECT ...
WHERE OBJECT_NAME = 'ONE_EXAMPLE_TABLE';
or object_type = 'TABLE' and object_name not like 'BIN$%'
whatever
mastro
TheBonsai: usn_work http://pastebin.com/d26fa36fa (ordered by type) now I try to filter it and give you the filtered result
usn_work
what about WHERE...?
mastro
TheBonsai: usn_work filtered: http://pastebin.com/d432f625e
usn_work: :) sorry i filtered with where
this was my query: select * from user_objects where object_type = 'TABLE' and object_name not like 'BIN$%'
TheBonsai
*scratches beard*
usn_work
please drop one of your tables you want to get rid of, with DROP TABLE XYZ CASCADE CONSTRAINTS; and provide anythign from the command to the error message. Do it on SQL*PLUS
-> pastebin
if it fails, become SYSTEM user and proceed to drop it with DROP TABLE SCHEMA.XYZ CASCADE CONSTRAINTS; and provide anything from the command to the error message. Do it on SQL*PLUS
mastro
usn_work: I don't have SQL Plus, I use Oracle SQL Developers (I'm on Linux).. I tried to execute it as script, the first time it gave me a different error, the i re-executed it and it give the 00942 as usual.. strange: http://pastebin.com/d76477b14
usn_work: and I can't became system user, i'm not the DBA
usn_work: i'll install SQL plus but I can't now
m0rph3us
how can I hide the passSystem in a script like this
define usuario=&1
define passSystem=&2
define BD=&3
mastro
I give up.. keep loosing connection here and not doiong much without DBA role... I'll wait the DBA can do something and hope he can fix it
I would like to know what i did wrong
usn_work: last chanche: the *new* error does say anything to you?
mastro_
my last message ... [12:24] <mastro> ORA-01654: unable to extend index SYS.I_OBJ2 by 128 in tablespace SYSTEM
TheBonsai
contact your dba
BrixSat
hello
i have a question
can i assing a select result to a variable inside a plsql procedure?
TheBonsai
google "select into"
BrixSat
thanks
usn_work
.
Techdeck
Caused by: org.springframework.jdbc.UncategorizedSQLException: Hibernate flushing: Could not execute JDBC batch update; uncategorized SQLException for SQL [insert into T_USER_ACTIVITY_LOG (ACTION_TIME, ACTION_TYPE_ID, USER_ID, APPLICATION_ID, ENTITY_TYPE_ID, ENTITY_ID, ID) values (?, ?, ?, ?, ?, ?, ?)]; SQL state [72000]; error code [14400]; ORA-14400: inserted partition key does not map to any partition
Anyone knows why exactly is this happening?
usn_work
~> oerr ora 14400
14400, 00000, "inserted partition key does not map to any partition"
/ *Cause: An attempt was made to insert a record into, a Range or Composite
/ Range object, with a concatenated partition key that is beyond
/ the concatenated partition bound list of the last partition -OR-
/ An attempt was made to insert a record into a List object with
/ a partition key that did not match the literal values specified
/ for any of the partitions.
/ *Action: Do not insert the key. Or, add a partition capable of accepting
/ the key, Or add values matching the key to a partition specification
Techdeck, the problem is inside your bind variables, you are writing BS into the database ;)
Techdeck
hmm ok I'll check, thank you
mastro
how do I found out the default tablespace for my user?
usn_work
mastro, http://www.usn-it.de/index.php/2009/02/25/get-oracle-user-ddl-with-dbms_metadata/
mastro
usn_work: thank you
usn_work: hum.. can I use that if i'm not the DBA?
spheniscus
any ideas why this works in sqlplus but throws a 06502 wrong numeric or value type error when called as java prepared statemnt? http://pastebin.com/mfb7188e
seems the function is not getting the table name correctly..
usn_work
mastro, select default_tablespace from user_users;
mastro
usn_work: thank you
usn_work
np
tkonto
Hello all. Some help with 10g on RHEL5? The installer keeps on failing Network Configuration Requirements.. Status "Not Executed"... However all my /etc/hosts entries are with FQDN and host name, none with DHCP... if I continue with installation I always end up with ORA-12154:TNS:could not resolve the connect identifier specified.... any tips?
usn_work
the INSTALLER? With TNS?
when does the ora-12154 come?
tkonto
at the end of installation... at 99%
usn_work
Do ypou install and create Db in one step?
« prev 1 2 next »