logs archiveIRC Archive / Freenode / #oracle / 2010 / February / 5 / 1
Reign1
whats the easiest way to copy rows from 1 table to another? can i do create table as select * table partition(x) tablespace y? (as i only want rows from exact partition and new table will be on another tablespace)
anyone?
rebrnje
hi, i'm working on a RedHat 5.3(32bits) OS, and want to install oracle 10g database on it. My system was installed without any X11 packages. And when i'm launching a "xhost+" from root or any users it's respond me 'xhost: unable to open display""'. My /etc/hosts is correctly edited. And i want to know if i need to install any X11 packages to do this ?
dagun
Reign1: create table schema1.table1 tablespace tbs1 as select * from schema2.table2 partition(z); ... something like this without checking docs :) you should also have quota on tbs1 and needed object/system privileges (select privilege on schema2.table2 for example and create table privilege)
Reign1
dagun: was thinking alike, thanks, will try it :)
oh, another question, if i wanna insert data into table from another tables exact partition? so i guess its "insert into new_table as select from old_table partition(x), right?
dagun
yes
but threse is one thing, both tables should have same definition
otherwise it won't work
Reign1
tablespaces differs, and old one is partitioned, that basicaly it
columns etc are the same
dagun
no problem then
Reign1
good
hm, need to specify values. bah
dagun
:) didn't notice that
         

Reign1
nah its ok, just missed "select *" part
works now :)
dsdel
i hate oracle for delaying the 11g r2 release for windows so long...
Reign1
another question, ive heared there are tools but im not sure, to shrink tablespaces? i mean lets say ive created 10GB tablespace and i only needed 2 of them, now ive used 8GB for nothing, how to solve this or recreating is the way? :)
dsdel
a) create from start on tablespaces with autoextend on dedicated partitions/asm
b) you have to reorganize and then you cna shrink it cause all data has to be first moved to the first blocks
the easiest and safest way is todo a complete xport, drop, shrink, import
http://www.oracle.com/technology/oramag/oracle/05-may/o35tuning.html
http://www.oracle.com/technology/oramag/code/tips2003/060103.html
follow the urls for the methods without export
dagun
or you can move all your objects to new tablespace with alter ... move option
and when done then drop the "old big" tablespace
but i don't suggest to do that while your applications are online
i meant moving objects between tablespaces
dsdel
nothing of this options should be done while online
and think of the backup before if it's a productive database - just for the worst case
dagun
right
Reign1
thanks :)
now, anyone saw any online documentation of upgrading from 11.1 to 11.2 on linux? downloaded pdf from technet but thats bullsh*t, while cant find anything decent on metalink
dsdel
Reign1: download 11.2, there should be a readme.html included
emca advises to remove public privileges. removed. now emca can't be removed/recreated/dropped...
mgolisch
whats the prefered way to migrate a database from one system to another including switch to different arch and newer software version?
is this going to be a tricky thing?
dsdel
mgolisch: exp/imp expdb/impdp with test,test,test,test,test,test,test
install everything on the new server, do a test-transfer, test,test,test,test,test,test,test
mgolisch
we currently run 10g on redhat 32bit want to go to 11g on redhat64bit on new hardware
dsdel
then do the prodswitch
mgolisch
so exp/imp is the prefered way?
this will transfer packages and stuff too right?
dsdel
read doc regardign exp/imp - i'm not sure if they transfer packages
rman backup/restore could be possible perhaps - but i'm not sure cause of the switch of 32 to 64 bit
mgolisch
i see
dsdel
google and then test the selected method
Reign1
dsdel: readme helps nothing
dsdel
for patches it's always very usefull :/
Reign1
nah, and this is BS, as i cant find anywhere upgrade from 11.1 to 11.2
:/
         

dsdel
http://www.google.de/search?hl=de&client=firefox-a&rls=org.mozilla%3Aen-US%3Aofficial&hs=ODL&q=site%3Aoracle.com+upgrade+to+11g+r2&btnG=Suche&meta=&aq=f&oq=
Reign1
saw those
maybe im too picky
dsdel
anyone ever here had problems with emca -confg -dbcontrol db on win2k8r1 with 11gr1 and 'no listener'?
Dienst "INTEX1" hat 2 Instance(s). Instance "INTEX1", Status UNKNOWN, hat 1 Handler für diesen Dienst... Instance "intex1", Status READY, hat 1 Handler für diesen Dienst...
Dienst "INTEX1_XPT" hat 1 Instance(s). Instance "intex1", Status READY, hat 1 Handler für diesen Dienst...
.........
xushi
hi all
mator
hello
xushi
i'm about to get some books and do some learning on Oracle. My company uses 10g at the moment, and i'm wondiering if i need to stick to 10g in terms of my books and learning etc.. or if i can go ahead and learn about 11g ?
I mean, is there a major difference in how they work or what i'll be learning? or is it just new features?
mator
xushi, you should learn 10g and only read 11g new features
xushi
mator: thanks for that. 10g it is! :)
dsdel
diag dir in 11gr1 11.1.0.6 directly after db-creation: 1.2gb
in 11.1.0.7 2.5 mb
....
Reign1
how do i change oracle home as SET shows old info? i do SET ORACLE_HOME=path, export ORACLE_HOME, its still old path
?
DiscordianUK
using which shell?
for the bourne or korn or bash shells ; ORACLE_HOME=path;export ORACLE_HOME is what you want not SET
Reign1
oh
yep, thanks
is it enough to change ORACLE_HOME or also i need to change ORACLE_BASE to the new path, as i got ora11g and now made ora11g dir next to it, first of all planing to install all the components ora11g got
nevermind
got it wrong
idletask
Hello
Someone mentioned here the possibility to create a custom procedure to check the validity of a password, and even gave an URL - but I have lost the URL
Do you know anything about that?
fly-away
hi
can anybody say me what is faster - XMLDOM or XMLGEN
?
xushi
if i wanted a simple distro just so i can have oracle to play with, which is the easiest that i can use? easy in terms of either installing oracle or ahve it preinstalled
idletask
xushi: CentOS
xushi: it is essentially the same as RHEL, so just follow the install guide for RHEL and you're set
It is 100% binary compatible
xushi
excellent. I would have chosen (k)ubuntu or debian, but CentOS ill give it a try
I mean i forgot about that distro :)
idletask
OK, what is the difference between a glogin.sql and a login.sql file?
dagun
g stands for global
it will be executed on every login
no matter who you are
login.sql will be executed only for its owner
TomJ
How good is flashback? I basically need to be able to snapshot my DB at a point in time and roll back all operations back to that point in time - all operations: DML, DDL, everything. Does it work fine? Any caveats I should know about?
sytse
I have a function with an in parameter with a table type (table of record); why does SELECT 1 INTO l_dummy FROM TABLE(p_param) give me the error ORA-00902: invalid datatype?
TomJ
think you might have to do a CAST but not very sure
Dormir
TomJ: I've used it to do just that
with load testing and with testing out certain products
have to be mondful of space requirements
mindful
idletask
TomJ: yes, LOBs
TomJ
if I added a column to a 30GB table, how much would be stored in the flashback? The entire 30GB table? Or much less than that because it just needs to remember 'this column wasn't there' ?
idletask
TomJ: they have to be explicitly configured to go through redo logs
sytse
TomJ: a cast to what? Returning the exact same type from another function and usin that function in the FROM TABLE() works just fine
Dormir
if the column doesn't have a default, I think it would be fairly small
TomJ
idletask: ah interesting - so if my LOBs are not so configured, they will not be snapshotted and can't be rolled back?
idletask
Yes
TomJ
and that applies equally to CLOBs and BLOBs?
idletask
Probably so, I have only ever used lob-type columns
Dormir
didn't know about LOBs. good to know
TomJ
thanks very much idletask
idletask
np
TomJ
is that just a case of adding a parameter to the storage config of each LOB column?
idletask
Yes
I don't remember the default, though (whether this is logging or nologging)
TomJ
thans
sytse
TomJ: oh wait, trying to pass the parameter to SQL at all fails; a SELECT otherfunction(p_param) INTO l_dummy FROM dual; fails with the same error message because it can't pass p_param (it is of the correct type though, l_dummy := otherfunction(p_param) works fine)
TomJ: so maybe there is no way at all to use any kind of PL/SQL table/array as a source of rows in a SELECT statement?
(which I'd hate, because I need to aggregate a table/array/cursor using sys_xmlagg)
TomJ
I think it's possble but sorry can't help right now
on a call
try more Google
"use PL/SQL table or array in SELECT" wil come up with something I'm sure
sytse
http://www.orafaq.com/forum/t/50787/2/ <-- ah, the (terrible) solution someone came up with is to put the table in a package local variable, and creating a function that pipelines that table back
idletask
Question about "OS authentication"
RJarett
dont do it...
idletask
There are OS groups, such as dba, which grant a role - is it possible to add other special groups with special privileges?
RJarett
cause if you need it, more than likely you should just be using sqlserver and dont care abotu security
idletask
RJarett: irrelevant
RJarett
you cant use most of the new advanced security and auth features in oracle w/ os auth
and if you want to do it right, use something like their SSO products
idletask
Well, OK, let me explain the problem clearly then...
The client doesn't want us to access their data, and we are sysdba - so far so good, I can delegate dbvault access to them with no problem
The thing is, for the apps to connect, a username/password is needed, and there are 3 app servers
RJarett
so... why arent you proxyuser'ing?
idletask
So, rather than having them going to the 3 servers and type the password 3 times, I wondered whether there was a way to just ignore the user/password combination and login as a given user to the database provided some dbvault rules are met
(given that we have no access _at all_ to the app servers anywayà
RJarett
ummmm
idletask
I have tried the secure external password store but it doesn't fill my needs :/
metalink suggested this, Kerberos and radius, but the two latter solutions are not an option here
(well, maybe, after all)
I was thinking about SSL auth, too, but I'm not sure I can do that with JDBC
RJarett
umm why not just use wallet based auth?
since what youre really asking about is certs or something if its jdbc
idletask
Because it allows me, as user oracle, to do sqlplus /@dedicatedservicename and be connected directly with the schema user, which I don't want (or what you're talking about is not SEPS, in which case I'd like a link)
Hmm, unless I can separate the $TNS_ADMINs for the two users...
No, that won't even work :(
Trek
is this channel +q and prevents non-regged to connect?
i've got someone in #ubuntu looking to get in here for help
usn_work
yes its is
Trek
okay, just double checking. i'm gona sit here until I can help get the non-regged into here after he registers
idletask
Well, I must be off
Have fun
babo1
i s
i need to install 11g on ubuntu. can anyone help me ?
TomJ
read the topic
babo1
TomJ, there's no ubuntu on there
what's oracle XE ?
TomJ
Baby, free Oracle. Very strict resource limitations - wont use more than one CPU, more than 1GB ram, and can't have more than 4GB of data
RJarett
oracle xe is a free heavily limited database with basic functionality and is more than enough for most idiots.
TomJ
It's meant to ween people off MySQL and the like
RJarett
i wouldnt say that
usn_work
http://www.oracle.com/database/product_editions.html
TomJ
If you have a project that needs Oracle and you can't afford the minimum $6k cost of Oracle Standard One then it's an option
RJarett
more like swaying people away from msde/sqlserver express
TomJ
Well, that too. But Standard One is the direct competitor to the basic SQL Server offerings
babo1
TomJ: Is it similar to 11g ?
for exam studying purposes ...
usn_work
I'd daresay you don#t need a license for exam preparation
RJarett
if its just study download enterprise
babo1
RJarett, will xe do ?
RJarett
Use enterprise
XE does nothing for backup and recovery
you need to learn rman backwards and forwards
usn_work
Use EE and don't think too much
babo1
where can i get enterprise ?
usn_work
And save your time and install the Db on OEL, not on Ubuntu
oracle technet
babo1
it doesn't seem to be in the apt-get
usn_work
"<babo1> it doesn't seem to be in the apt-get" --> I'm out...
babo1
well it'd be handy if someone had made a .deb ....
usn_work
Did you ever install Oracle?
babo1
usn_work, no
RJarett
babo1: you are FAR too stupid to get an oracle cert
usn_work
Try it and talk afterwards
hehe
babo1
usn_work, it's difficult then i take it ... ?
RJarett
babo1: have you bothered looking on oracles site for where to download software?
usn_work
Did you buy books for it? Sybex preferably?
RJarett
but ubuntu is NOT a certified OS for it
use redhat, centos, suse, or OEL
babo1
RJarett, yes, but no .debs ...
usn_work
babo1, the prep books contain test questions where you can check the situation
RJarett
shut the fu*k up you idiot
real software doesnt come in a deb
dsdel
babo1: debian == ONLY open source/completly free software
RJarett
sometimes theres something called a tar or a tgz
dsdel
babo1: oracle == commercial/closed source
RJarett
sometimes you need to show brainpower and read
« prev 1 2 next »