logs archiveIRC Archive / Freenode / #oracle / 2010 / January / 29 / 1
sytse
rednblue: maybe you should try sql*loader for inserting this data?
dunno if it can handle 2gb long columns btw
TomJ
Is it possible to limit the privileges of a particular function or package? Basically I'm trying to design a sort of plugin system for our DB - whereby we can extend the product by writing plugin SPs, and those SPs will be executed by a Plugin Manager package
but I therefore want to be able to limit what hte plugin can do - ideally limiting it to only being able to call methods of the plugin manager itself
I suppose one crude way would be if it were possible to execute a given SP as a different user, one who has far fewer privileges
rednblue
Dormir: u online 24 hours on #oracle :o
Ceendat
anyone familiar with oracle + vmware?
rednblue
me not Ceendat
gigasoft
is oracle any good?
Dormir
yes
Reign1
<Dormir> Reign1: you could create one catchall partition, then merge everything to that one
any example link or?
whats the easiest way to get rid of partitions in the table? (wanna leave table and data as it is, only remove partitions)
anyone?>
rednblue
1
         

Reign1
i imagine easiest way to get rid of partitions is to create temp table, move all the data into it, drop partitioned table, rename table to needed name, create indexes? or?
rednblue
which is the best active oracle forum?
domas
hi! I have a question about oracle mysql!
Reign1
rednblue: dunno, link?
rednblue
no
please giime link wer ill get reply to my querries
GHOSh
now mysql is under oracle :(
cmug
One word: InnoDB
Reign1
another one: MariaDB
http://askmonty.org/wiki/index.php/MariaDB
Dormir: around?
aquafina
hi. am trying to import a csv file to a test db on my comp.
but there's some problem with the date formats. they get switched for some rows
like DD-MM-YYYY turns into MM-DD-YYYY on its own
is there a way to fix this?
rednblue
dows oracle work on linux?
does*
aquafina
yes
you get lots of howtos to install oracle on redhat/centos based systems
DiscordianUK
I have 10-XE and 11G running on linux
Reign1
im on 11g and i wanna know how to remove partitions from the table leaving data not touched, anyone?
idletask
Hello
Even though I set up the ADR_BASE environment variable, I have to explicitly declare set base $ADR_BASE in adrci
Is this normal?
Reign1
im on 11g and i wanna know how to remove partitions from the table leaving data not touched, anyone?
idletask
No idea, I don't use partitioning
hali
Reign1: merge partitions
alter table table_name merge partitions foo2,foo3 into partition foo1;
with some limitations
Reign1
hali: but table will still be with a partition, while i dont need it at all?
hali
you can merge it down to one partition
mamurdian
is omething like this possible in oraacle:
create table fk_test ( x int primary key, y int, foreign key (y) references pk_test (a) on update cascade )
k tom said he never need it
Reign1
hali: whats next when i got only 1 partition left? thats it, or can i somehow make table bare, without partition?
hali: ?
         

hali
Reign1: not sure if you can, create a new table, drop old and rename
Reign1
what about indexes and so one? which parts of all that stuff ill have to recreate after i drop and rename?
and is there a point of merging partitions, if ill be droping table anyway? or itll make data copy to temp table faster?
hali
depends if they are local or global indexes
i would probably change it all global before you start changing
idletask
Reign1: just grab the DDL for the table you wish to move, create it under another name, insert into select * from oldtable, recreate indexes, drop old table, rename new table, end of story
As you said yourself, why merge partitions if you wish to have it non partitioned eventually
Do NOT create indexes before inserting, however
Create them only after inserting
Reign1
got it
giusef
HI there. In oracle identity manager, I am reconciling an Active Directory account via trusted recon. If I delete the corresponding account from AD, the OIM User is also deleted. If I re-create that user, OIM fails... Why?
mgolisch
are rman backups only compatible with the same arch + software release?
i asume it just pulls out the raw datafiles from asm right? its not somekind of exp/imp like dump
idletask
mgolisch: rman backups are supposedly platform independent (AFAIK)
Unless you specify explicitly to dump datafiles
But even then, the only problem is endianness, and you have a way to fix the endianness as well
Well, all this is based on some limited testing and much reading, but I don't use rman in production, so don't take what I say for granted
mgolisch
what else do you use?
i allways though its the prefered way to do backups
or am i wrong?
henriquemoody
Flavinho, Hi man...
RuslanPopov
re
ppl, I need help with spfile
solved :)
mator
you're welcome
idletask
I have gathered as much on this channel yesterday (thanks to RJarett) - is it true that PL/SQL procedures execute with the privileges of the user which compiled it?
compiled them
Well, you see what I mean (hopefully)
RJarett
no more so than any other plsql you run is ran by the user running oracle
or do you mean the DB internal perms?
siamba
RuslanPopov, have you found a new job?
RuslanPopov
not yet, I've migrating inside the company
siamba
oh, fun
idletask
RJarett: the db internal permissions
ie, I'm connected as user foobar, I compile a pl/sql procedure as user foobar and grant permissions on another user on this procedure
From what I understood so far, this other user will execute the procedure with the privileges of user foobar
RJarett
hmmm that doesnt jump out at me but it would be easy to test for
idletask
Well, the thing is, a client requires that we use dbvault and tde, and we are not allowed to have the wallet password and dbvault user passwords either - even though we do need the dbvault passwords from time to time, and the client does need sysdba to open the wallets
So, if procedures really are "setuid" by default, the problem is "solved"
I don't want the client to have sysdba and they don't want me to have dbvault passwords :p
Well, I am currently reading this --> http://www.jlcomp.demon.co.uk/faq/plsql_privs.html <-- and it appears that I misunderstood it all :(
RJarett
that doesnt even have to do with compiled
idletask
But it says: "Calling a procedure or function (whether inside a package or not) owned by another user, to which you have been granted execute access, causes it to run as though you were the owning user"
So, it appears to be right
Argh
RJarett
well thats an old doc
and theres a ton of crap to handle that
proxyuser and such
idletask
RJarett: pointers to docs would be appreciated (and even to appropriate Google query terms)
I need to figure that out
RJarett
whats your specifics?
think about the problem at hand
idletask
RJarett: let me a couple minutes to tell the problem at hand and then I tell you the whole story
OK, here is it
A client requires that its data be protected by TDE and dbvault
Which means two things: 1. it needs to open the wallet at mount time (otherwise the encrypted tablespaces cannot be online), and 2. we cannot have the dbvault passwords although we will need them if we need to create other realms
As to point 2, the realms will always have the same characteristics (only the user will change), so it can be a procedure with the user as an argument
As to point 1, AFAICS, you need sysdba privileges to open a wallet
So, if PL/SQL procedures are indeed "setuid" when they are compiled, I figured that I could do 1 by compiling the procedure as sys, and 2 by compiling the procedure as dbvo
RJarett
no
idletask
OK, so I got it all wrong then :/
RJarett
why would you be the one opening the wallet?
idletask
I will NOT be the one, I'm not the client
RJarett
i mean your app
idletask
And I don't want the client to have sysdba privileges
Err, the app is a separate entity at this point
RJarett
why are you using tablespace vs column?
w/ column you can have individual table keys
TBE is more so backups and crap or on disk data is encrypted
idletask
Well, to complicate matters further since you're asking the question, we use iFS
Yes, I know
RJarett
Are you trying to use TBE as a way to hide data from certain people or what?
idletask
We, no, the client asks for it
So, ultimately, the answer is yes
And don't ask me to try and make the client set back on these requirements :p It's a lost cause
They want 1. to be the only ones to have the ability to decrypt their data, and 2. to forbid everyone but the target app to read the data
1. requires TDE, and 2. requires dbvault
I tried to push the argument of "availabilty vs privacy" (data integrity remains equal), but they won't listen
One alternate solution would be to disallow "OS privileges" (ie, that the dba group doesn't imply the sysdba role) at the instance level - I _think_
But that doesn't solve the wallet problem
So there, I get a s*load of requirements to meet without even half the knowledge necessary to achieve it all :p
But anyway, this particular phrase: "Calling a procedure or function (whether inside a package or not) owned by another user, to which you have been granted execute access, causes it to run as though you were the owning user
Doesn't that mean that this way, procedures are setuid?
It says "owned", however, not "compiled"
Well, I guess the keyword here is "TIAS"
Which I will do
Well, I must run
See you
Have fun
BoomSie
HI there fellow oracle dba's, might be a stupid question, however, I'm already googling my ass of & read numerous OTN pages but didn't find: Is it possible to ALTER a table to define it to an IOT (based on columns x y z)
RJarett
youll have to recreate it (if you dont want downtime use online redefinition with dbms_redefinition)
why do you want it as an IOT? iot have major limitations
BoomSie
well, we're migrating from MSSQL to Oracle and try to do it step by step
RJarett
But why IOT?
What are you trying to accomplish?
BoomSie
then we found out we missed indexes and "clustered" indexes, so I was wondering if it can be done through "alter table"
RJarett
Dont use mssqlserver mentality on oracle
BoomSie
I know :)
RJarett
clustered tables and indexes arent the same in the 2
And IOT may not be beneficial
BoomSie
no, but clustered indexes are more or less equal to IOT no?
RJarett
no
Whats the problem you are trying to solve in the code?
BoomSie
It needs to be ordered physically to the data that will be most queried and not by the 'last' record
RJarett
Why?
BoomSie
cause the majority of the queries will be after instances of created bookings
(open instances I might add)
I read an asktom article that pretty much described what I was after, only his example was about stock exchange closing per company
RJarett
Was it an old note or something within the last 3 years?
How often does your data change?
BoomSie
the data itself?
RJarett
yes
how often does the left side of the pkey change
BoomSie
not much, will be created, later bond to visits and thus change status
however, since it's a sh*tload, we'd like to keep it ordered for performance
RJarett
iot != better performance
especially with chained rows, or incorrect guess maps because you relocated your data
are you running std or enterprise?
BoomSie
XE, testenvironment (developer here)
RJarett
so you dont care abotu testing performance of what the real system will be huh?
BoomSie
I know how I want it to be, how the system performs under MSSQL, and want the same response from Oracle :)
thus based upon theory I arrived to IOT
Index Organized
RJarett
thats like saying youre converting to sqlserver express or msde for dev
and think productionw ill act the same way with 100x mroe data
BoomSie
that's something we're testing right now :)
RJarett
on xe?
BoomSie
but I want to know if it can be done by altering or not
RJarett
no
BoomSie
yeah, why not?
RJarett
like i said in the first place
BoomSie
(no, why not on XE ... not the altering)
RJarett
because XE is limited features, different in how the optimizer works, different threading of how file reads happen
will the production system be std or enterprise?
how much data in this table?
BoomSie
limited I understood, but I understood also that performance wise it was comparable to Std edition
60 k easy
RJarett
only 60k rows?
thats it?
BoomSie
yeah :S
RJarett
in 10 years how big?
BoomSie
after a year or what
RJarett
will it ever break 200k?
in 5 years?
BoomSie
in 10 years 600 K (unless the company grows twice the transport ... then it can arrive till million(s))
RJarett
ok thats still tiny
and should return 1-2 rows with a normal table and index in sub-second time
using IOT is more headache than good
« prev 1 2 3 next »