logs archiveIRC Archive / Freenode / #oracle / 2010 / April / 20 / 1
thecolor3
Okay here is a newbie question, working on a existing application. how does select group_ttime, entrance_time, sales_time, differ from select a.group_time, a.entrance_time, a.sales_time when calling the oracle database?
What does the a. do
RJarett
did you name a table a after your from tablename?
thecolor3
no
not as far as I can see
I am in em atm and dont see any a.sales_time or anything like that
I see sales_time
Reign1
hi, how to i check status of exact listener on maschine with several instances?
RJarett
lsnrctl status
lists all registered instances with the default listener
Reign1
lists none, while i know there is 2 instances, tho i see lsnrctl as oracle 9, while both instances are oracle 10
RJarett
your ORACLE_HOME set right?
and TNS_ADMIN?
ps -ef | grep lsnrctl
Reign1
that was the case, orahome, thanks RJarett
crashanddie_
Hi guys, I'm having a hard time understanding a date/time format
I'm sure this is a common one, but Google sadly only gave me blog results which are unavailable through this network: yyyymmddhh24missff6
I understand that's everything up to hh24miss (which I assume is hour-hour [in 24h format] minute minute second second), but "ff6" has me baffled.
         

RJarett
MI is minutes
SS is seconds
FF is typically timestamp for mroe precision
6 holders for fractional second
select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS:FF6') from dual;
crashanddie_
RJarett: hmm, ok
RJarett: thanks
Junior
yello ;)
crashanddie_
green
admin0
hi .. how do I reduce my redo buffer waits? Its value is currently at Redo Buffer Waits
----------------------
146638
sorry: 1117115
here are current redo stats: http://oracle.pastebin.com/aYAUzerA
can i change size of oracle log buffer without shutting down ?
TheBonsai
what makes you sure you need more log buffer?
what's your switch frequency?
archangle25
i installed the EM agent as the mysql user on a machine to test the mysql EM plugin. I'm now trying to install Oracle DB as the oracle user on the same platform, but am getting You do not have sufficient permissions to access the inventory '/export/home/mysql/oraInventory', the emagent was removed a while ago. there are no /etc/ora* files on the machine. where else can i look to manually remove them?
TheBonsai
/etc/oraInst.loc is the inventory pointer
archangle25
apparently writing a question out spurs me to check an otherwise overlooked directory.
/var/opt/oracle/oraIInst.loc
TheBonsai
oh, you removed those files
ah
ok :)
archangle25
ty
:)
problem shared eh?
TheBonsai
hehe
no matter which users you run oracle software as, there's always an inventory owner and a software owner
archangle25
ah ok - good to know. ive deleted the files so hopefully the fresh install will work now
TheBonsai
yea, oracle is relatively easy to remove (if you know the backgrounds). nothing hidden somewhere, just a few files and the software directories (and maybe changed shell profile files vor environment variables, but oracle doesn't write that by itself)
deebo
anyone know how to properly set a null sys_refcursor IN parameter in a stored procedure call via JDBC CallableStatement?
KeiKurono
morning
idletask
Hello
Trengo
morning
idletask
11g with dbvault and TDE, I see a lot of I/O using system tools (dstat) but when I query v$filestat, I/O is low... There really isn't anything else running than the database apart from syslog which sleeps all the time
Where should I start to look?
hali
idletask: look at iotop to see if it's oracle or not
         

idletask
It is
In fact, iostat -kd says I/O is located on the SYSTEM tablespace
Duh
v$filestat reports none of that :(
Well, OK, that's not entirely accurate
There's also a control file on this LV
emboss
Hi, I'm trying to setup a 10g Release 2 cluster on win2k3, both machines are domain members, I have a number of problems but the first seems to be user equivlance failing, I'm logged in as a domain admin which is a local admin on both boxes but it fails. any ideas?
idletask
But why would the database read several MB per _second_ from a control file?
I'm starting to wonder whether v$filestat is accurate at all :(
pee-kay
are functional dependencies and normaltization the same?
idletask
Define "functional dependency"
Trengo
no, functional dependencies are something else
say function "enter new order"
which tables and columns will it use?
thats functional dependency
use a case tool to manage them for you
oracle used to have case*dictionary many years ago
pee-kay
do you know of a freeware tool to resolve fds?
Trengo
not really
pee-kay
i got a e/r diagram that i want to normalize, i don't know where to start it's taking me ages to figure out
idletask
Search the net for first normal form, second normal form, third normal form
These are relational dependencies BTW
Ensure that your schema is at least 2NF
Trengo
functional dependencies arent visible in an E/R
bpm/uml yes
pee-kay
i'm visualizing the tables in my head and trying to figure out
admin0
is it possible to index an existing table? moving partition data to a new tablespace ?
not indiex .. partition
idletask
Yes it is
admin0
never mind .. le tme try it first :)
usn_work
dbms_redefinition?
idletask
I know it is... But don't ask me how, I don't use partitioning :p
admin0
thanks usn_work
usn_work
or at least CTAS into a new, partitioned table ;)
admin0
i have one big tablespace .. if i create 12 tablespace for jan .. dec , can i use dbms_redefinition and it copy/move data from that big tablespace to tablespace jan -- dec ?
usn_work
please have a look at the docs. If you have enough space to duplicate all data, it might be much more painless to ctas
dbms_REDEFINITION is very powerful and has thousands of possibilities
but admin0 - in general, yes
just to give you an impression - the reverse case:
http://www.usn-it.de/index.php/2009/03/30/oracle-convert-a-partitioned-table-into-an-unpartitioned-one/
admin0
usn_work, you should have a reverse of this also :)
usn_work
And prevent you from all the fun to write such a script? :)
admin0
:)
(Action) passes a big pizza to usn_work
usn_work
Thanks, good idea. Nearly lunchtime
balek
hi i have a question please i use oracle rac i have a problem to connect to the service name when i try this : sqlplus sys/test @"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.10 (PORT=1521)))(CONNECT_DATA=(SID=test)))" as sysdba i have an error : ORA-12505: TNS:listener does not currently know of SID given in connect
if i use lsnrctl status i see : I see Service "test" has 2 instance(s). and it's ready
thanks for your help
I FOUND THE SOLUTION THANKS
MaL0
what was the solution ?
TheBonsai
SERVICE_NAME
or a rea#l SID
emboss
I'm trying to configure clusterware and on the cluster config storage it's not offering the disks I created for the OCR and CSS, I can see them in disk managment win2k3, any ideas?
pexy_
is oracle 10g R2 client supported on 32bit windows 7?
no, the other way: is windows 7 supperted by oracle 10g R2 client
idletask
pexy_: well, if Oracle installs on Windows 7, the answer is yes, obviously
Of course, this is not speaking of whatever firewall there may be on a stock Windows 7, which I can't tell
I don't use Windows
pexy_
it installs, but configuring doesn't work. i wouldn't use windows either if i could
balek
TheBonsai : i modify sid for service_name :)
dsdel
define 'configuring'
'oh yes a 4 year old product doesn't work with an up-2-date windows - windows is sh*t!!1!'
....
wonder what you say when glibc is changing on linux
emboss
Right, solved all cluster issues, at last up and running
This channel was pretty useless tho
KeiKurono
yeah
idletask
Grrr
TDE and DBvault together seem to wreak havoc on performance
But which one is the culprit?
Does v$filestat really tells the whole story wrt file I/O?
s,tells,tell,
RJarett
no
idletask
RJarett: so, how can I view all I/O on tablespaces generated by oracle then? :p
RJarett
you have a san dont you?
you cant ever use just 1 method
idletask
Yes, and I also have dstat, which is how I detected the abnormal I/O
RJarett
awr/statspack, san stats, etc etc
idletask
The block device on which the abnormal I/O is detected is a LV with only the SYSTEM tablespace and a controlfile on it
usn_work
idletask, do you see wait events for controlfile activity?
if yes, which of them?
idletask
usn_work: how can I see such wait events?
usn_work
One option is the diagnostics pack-sponsored moving performance window in Enterprise manager.
Another is v$session_wait
idletask
I don't have the former, so I'll use the latter, I guess :p
usn_work
but the latter is just a flashlight of the current situation, to see speaking values, you have to repeat the query on it
but statspack will give you wait event histograms as well
I just don#t know if the current statspacks know / operate all "new" wait events of 10g and 11g
idletask
Well, I'll pastebin the result of this query: select event, sum(seconds_in_wait) from v$session_wait group by event order by 2
order by 2 desc, even
http://paste.pocoo.org/show/204053/
There's also a very peculiar thing happening...
If I run this query, once a second (using watch): "select a.file_name, b.phyrds, b.phyblkrd, b.phywrts, b.phyblkwrt from dba_data_files a, v$filestat b where a.file_id = b.file# order by b.file#"
usn_work
sorry, got to go now. will be back tomorrow.
idletask
I get a lot of I/O on SYSTEM! Which is NOT accounted for in v$filestat
RJarett
you get alot of io on system because you are calling the data dictionary
idletask
RJarett: up to 10 _megabytes per second_???
RJarett
and making it do a sh*tload of reads of stats on every extend of the db
correlation not causality
idletask
And reading the dictionary is not accounted for in v$filestat?
RJarett
did you phrase that question right?
idletask
Yes I did
Seriously, why?
I cannot believe that 10 MB/s need to be read from SYSTEM to answer such a simple query
RJarett
simple?
how large a database?
and how many datafiles
idletask
The database is 10 GB and there are, in total, 8 datafiles (including the temporary and undo tablespaces)
RJarett
10gb? thats it?
idletask
Yes, that's it
RJarett
you really screwed something up lol
idletask
Well, default settings, just had to add TDE and DBVault
ANd I was VERY careful about the latter
It doesn't have any command rule except for CONNECT, and the realms are checked for only once per session
Sessions are opened only once, at application (re)start
That's it
This is why I incline to blame TDE
Anything to solve that problem would be welcome :(
RJarett
need more information
idletask
RJarett: just ask, I'll comply
RJarett
http://pastebin.com/kJmS9QuC
idletask
In progress
Hmm
No row selsected
RJarett
ok
idletask
...
RJarett
so you have no hot blocks that are often in a busy wait event
idletask
(Action) adds comment to SQL file
RJarett
describe the symptoms more
idletask
Well, it looks like _any_ query to the data dictionary will generate an abnormally high amount of I/O, always on the SYSTEM tablespace event
s, event$,,
RJarett
and?
whens the last time you gathered system stats?
or data dictionary stats
idletask
I gather stats every night at 11pm (local time) using dbms_stats.gather_database_stats
With no extra parameters
No optimizer parameters have been changed from the default
Only cursor_sharing has been set to FORCE, due to a misbehaving proprietary lib which we have to use
As to system stats, err...
No idea
RJarett
one of the queries that appears bad out of all i run is my tablespace space checker
idletask
I have detected the fact that the SYSTEM tablespace was being read badly by doing a watch -n 1 iostat -kd in one window, and running this query in another: http://paste.pocoo.org/show/204071/
THAT particular query generates a LOT of read activity on SYSTEM, which is not accounted for by the query itself :/
RJarett
but thats a seriously sh*tty ass test
« prev 1 2 next »