logs archiveIRC Archive / Freenode / #oracle / 2010 / January / 19 / 1
luisgrin
hi, im new to oracle, i builded a view, system is the owner, but i want it to belong to another user
sytse
copy/paste and recreate it, how hard can it be :P
(logging in as the other user, or by doing something like 'create view otheruser.myview as ...')
luisgrin
let me see :)
sytse
then you can drop the view in the system schema
luisgrin
sytse: thank you, ill try to do it
thanks sytse, ill try tomorow, very late here
rizzo
Man
Do I really need to install grid infrastructure to install ASM in 11.2?
delfis
hello, this is excecution plan
http://www.part.lt/img/0bc9c929aa551573b8da35205297e06c401.jpg
so how it is excecuted, 1 2.1.1 2.1 2 3
or 1 2 2.1 2.1.1 3?
so any ideas?
KWhat_Mobile
anyone know what is wrong with this create type statement http://pastebin.com/d35fa13ad ? Every time i enter it i just continues down another line like its expecting a ; or something.
ahh /
wish my book mentioned that
__phil__
hi all, I'm trying to find an easy way in SQL to "truncate all entries for field XXX to 17 characters"
         

Xgc
__phil__: Does your database have a string function like LEFT() or SUBSTRING()?
__phil__: Use an UPDATE statement to take just the first N characters from the value for all rows.
__phil__
it's oracle 10g
looks like substring will do the trick
something to the effect of: update table YYY set XXX = substring(XXX,1,17)
er: update table YYY set XXX = substr(XXX,1,17);
thanks Xgc!
delfis
hello, this is excecution plan http://www.part.lt/img/0bc9c929aa551573b8da35205297e06c401.jpg
so how it is excecuted, 1 2.1.1 2.1 2 3
or 1 2 2.1 2.1.1 3?
krz
hi
hello. is there a better way to model this: http://imagebin.org/80656 ? look at tbl_interaction, tbl_media_interaction and tbl_artist_interaction. i was thinking of removing tbl_media_interaction and tbl_artist_interaction and having media_id and artist_id as fk's (not null = false) in tbl_interaction. is this a better solution?
sorry got disconnected
is there a better way to model this: http://imagebin.org/80656 ? look at tbl_interaction, tbl_media_interaction and tbl_artist_interaction. i was thinking of removing tbl_media_interaction and tbl_artist_interaction and having media_id and artist_id as fk's (not null = false) in tbl_interaction. is this a better solution?
anyoone?
knandan
Hi..
I want to delete all the records where a field contains a particular value..
i want to know whats wront with this statement?
delete from crs_web_criteria where (contains(guid, "crt_guid") > 0);
guid is the filed name
and the field should contain the string "crt_guid";
any pointers.
Rudemeister
moarning
SilSila
my source db is having WE8PC850 nls_characterset i want to import the source db as AR8ISO8859P6. how can i export? am i have to set the nls_lang as WE8PC850 or AR8ISO8859P6 while exporting?
idletask
Hello
I need to open a database but not all datafiles are there, and you need the database to be open in order to bring tablespaces offline...
How can I do that? (Oracle 10gR2)
Rudemeister
idletask: I assume, mount instance, bring TS in offline state and open instance
SilSila: Id suggest import but refer to the docs to be sure
idletask
Rudemeister: unfortunately no
You can only bring tablespaces offline when the database is open
But I've found the solution
(and don't worry, it's not a production system :p)
Rudemeister
whats the solution
backup and restore through rman?
idletask
No
alter database mount;
Rudemeister
and then?
idletask
alter database datafile '/the/datafile' offline drop; -- for each datafile
alter database open;
Rudemeister
yes
:>
idletask
It helps
We had to restore an old backup on another backend for a client which has mistakenly removed data...
And the database is 580 GB
The client's schema + system, redos, etc only took 85 :p
Pe3k
hello, I have column with custom defined type (it is 'varray(4) of number'), is there way to connect and query data from c# with system.data.oracleclient namespace?
SilSila
have anybody worked in oracle application server 10g(RHEL 4)? i want to get the arabic characters in the windows xp's ie6 or above while accessing the forms from the rhel4 oas server? i have tried by setting in default.env registry.dat formsweb.cfg but no use....can anyone help me in this regarding? i was searching in metalink, otn forums, orafaq forums....but still i am not able to get the solution.
Trengo
SilSila do you have metalink access? open a TAR?
siamba
yay! after changing sga_target from 2.5G to 8.5G oracle became faster! ;)
         

BoomSie
Hi there guys, I'm googling my ass of for days now and almost know the developer Forum of Oracle by heart, I have problems with a view I'm trying to create. Then I thought, why not migrate from a working MSSQL 2005 version - ran into the same problem with the same view
the problem is the subselect in the view, when I extract that part, I'm also not able to execute it normally and keep running into SQL Error: ORA-00904: "CONTSTATUS"."CONTSTATUSID": invalid identifier
idletask
Well, the error says it all
BoomSie
you can shoot me for not seeing it, but this is the simple subselect that it "errors" upon
but the Table + Columns ARE there
idletask
You said you used a view
BoomSie
this is part of the view that I try to create :)
idletask
Is contstatus a view? If yes, is constatusid actually defined in this view?
BoomSie
no, contstatus = table
yep
idletask
Then the problem is in the view definition
BoomSie
hmm
idletask
Do desc contstatus, is there a contstatudid anywhere in this table?
BoomSie
contstatusid is present as a column, no data yet. It's a fresh migration towards oracle in this moment
not auto generated by the "Quick Migration Tool"
idletask
Well, if this column does not exist in the source table, it's no wonder that the view creation failed
Oracle checks the table definition when it creates a view
BoomSie
Column is there :)
http://pastebin.org/78659 Is the full view btw
idletask
Yet the error says it doesn't
BoomSie
Select ContStatusID FROM ContStatus > All Rows Fetched: 0 in 0.002 seconds
I agree with you fully that it's the "tumble" point in the view
but physically it exists
In advance: ContGoods.Id exists too. I change the order to descending now
no luck however :)
I'm feeling the heat, that it might be a collision with the usages of reserved SQL words
NOOOOooooo .... *cry cry*
idletask : I saw in the errors that it was escaping my table & column names ALL the time. We both know that when you escape Table / Column names, in Oracle it becomes case sensitive. So I did some researching on the net and stumbled upon http://forums.oracle.com/forums/thread.jspa?threadID=623437 where someone mentions that with parameters, you need to single escape the parameter
so I put ' around ContStatus.ContStatusID ... works flawless
hope it does what I want it to do :)
idletask
Hmmyes, the double quotes are the Oracle way of forcing the case on identifiers
And that's also valid for usernames
rsavu
hello all. if i have a table with 2 columns like this: bob - english | bob - russian | julie - english | julie - spanish | julie - portugese | jack - english | jack - french
RJarett
thats nice
rsavu
how do i get the name of the people who don't speak spanish
like jack and bob
can i do that?
RJarett
you probably not. most others can
rsavu
how do i aggregate that data?
any ideeas pls?
RJarett, could you help?
Discordian
maybe if you ask in #sql, rsavu
idletask
rsavu: use the "except" keyword
sidh
greetings gentlemen
i would need your advice, i 'm looking for backup DB scripts , and i found this (simple) one : http://www.orafaq.com/scripts/back_rec/backup.txt, so i tested it on a XE DB , and i saw that only dbf files (and a log file) are backuped, so i wonder if only dbf file are enough to restore a DB ?
because i've read about control file too (that are not in my backup directory)
zasz
you should also backup the control file, spfile/pfile and archive logs
sidh
oh i just see at the end of the script , the file with the name in its name is the controlfile, and it makes the logfiles switch
zasz
backing up the oracle home wouldn't hurt either
sidh
zasz: for doing hot backup with this script i had to create a pfile to be able to put the XE DB in archivelog mode, is it that pfile you're talking about (as i created it , i wonder)
zasz
pfile or spfile
backup whichever your using
dsdel
sidh: read the description: Simple on-line backup script (user managed - put tablespaces in backup mode and copy out)
so tablespace backupscript
is rman included at XE?
ah sadly not :/
zasz
time for upgrades!
sidh
dsdel: sorry i'm discovering oracle backups, so you mean recovering database from this kind of backup script MUST requires RMAN, but suppose you have made a backup with this script, on a 10gR2 Enterprise Edition, and the day after , there is a breakdown, is it possible to restore the databse on another machine from only those dbf file and ctl file ?
dsdel
sidh: if you don't use oracle xe, always use rman
rman con backup everything and also restore everything from the backup set
also selectable
but if you don't have used rman and you're using that kind of script you less need the dbcreation files and the pfile/spfile. you can re-create the database with EXACTLY same paths and so on, shutdown, copy files over, startup
but that can get kinda tricky, especially if you have no single control file and modified the tablespaces afterwards - then you have to adjust the create database scripts f.x.
that the control files are correctly recognizing all files
sidh
ok dsdel i will connect on a 10gR2 server test DB for testing this script and learn how to use rman with those dbf files
dsdel
i would just use rman :)
without that script
sidh
ok i will look in the documentation for simple complete backup/restore procedure with rman
i really thank you for your advice dsdel
dsdel
http://www.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ#RMAN_backup_and_recovery
http://www.oracle.com/technology/deploy/availability/htdocs/rman_overview.htm
http://www.oracle.com/pls/db102/portal.portal_db?selected=4 right side, top
then you've got enough to read hehe
but we're doing the backup only with rman
these /enterprise/ backup solutions from 3rd party companies are so expensive that you can pay a person for the license fees...
sidh
whaoww rman seems very great
"The biggest advantage of RMAN is that it only backup used space in the database"
RJarett
well rman can do full image copy of the datafiles
which is was more handy in many cases
especially VLDB where recovery times would normally take a long time
alter database switch to copy; <-- saved my job a few times
rman recovery times on compressed backup files for multiple TB takes FOREVER otherwise
dsdel
compressed && TB :D
superlinux
hi all
I have a problem downloading from oracle. my Login /IS/ correct. however, I just cannot download from oracle
RJarett
so what are we supposed to do about it?
superlinux
RJarett, thanks that you reply. well I want 11g for Linux and I don't know if some one can provide it torrent or any other means.
RJarett
yeah create a new login, and download it from the source since they give up to 2MB/sec download speed
superlinux
RJarett, that bug is something i saw it recently listed in the OTN forum
RJarett
or just see where you download from and switch download-east for -west or whatever
superlinux
RJarett, i was just told that flash player must be installed correctly
RJarett
why?
you can even use wget in linux to get the stuff
hali_
wget can use the mozilla cookie jar
RJarett
i was able to DL 11gr2 yesterday without issue
superlinux
can you jsut give me the link please
I hope you still have it
RJarett
32 or 64bit?
superlinux
32 bit brother. thanks
RJarett
http://www.oracle.com/technology/software/products/database/index.html
if you go there
does it say "welcome yourname" up top?
superlinux
yes it does
RJarett
ok
click accept license agreement
does it switch to the tank you for accepting the otn license agreement?
superlinux
yes the accept/decline is working. however, the problem is that when I ask for the download, it asks me again for a username and password, i put the correct ones. still redirects me back to login screen
RJarett
once it switches to accept
http://download-west.oracle.com/otn/linux/oracle11g/R2/linux_11gR2_database_1of2.zip
paste that to the browser bar
superlinux
RJarett, I still get the login screen
RJarett
hmm weird
i dont
where are you from?
superlinux
RJarett, I live in Lebanon.
RJarett
what about download-uk?
superlinux
how? what do you mean by download-uk?
RJarett
see the link above
replace west with uk
superlinux
ok
RJarett
you may get lucky and hit a redirector without the issue
superlinux
ok
sidh
i have to migrate a sqlserver DB of 16 GB to oracle 10G, what kind of approach do i have to have for finding the value of sga_max_size and sga_target ?
RJarett
memory used/needed is the least of your issues
zasz
lol
RJarett
just start with 6gb sga, 4 pga
or less
zasz
do you even know how much ram his server has
RJarett
because it wont really matter
doesnt matter
after all the nightmares of switching enumerating columns over
« prev 1 2 next »