logs archiveIRC Archive / Freenode / #oracle / 2010 / February / 8 / 1
TomJ
What do I do to a tablespace when I want to archive its datafiles and thereby stop using this tablespace for the time being. I thought it wsa just 'offline' but that seems to be intended for temporily taking itt offline?
RJarett
well.. you will be using it in the future?
TomJ
that's possible, but no immediate plans. i want to remove it from oracle, highly compress its datafiles, and then leave it on archive storage in case it's ever needed again
RJarett
run this: exec dbms_tts.transport_set_check('yourtablespacename', TRUE);
TomJ
so it shouldn't be visible in this DB any more, but it needs to be impossible to add it to a DB in the future if required
RJarett
tell me if it errors
TomJ
ah ok I was just reading that too, thanks
it will take me a minute, it's moving partitions into the tablespace now
RJarett
SELECT * FROM transport_set_violations;
if you get no errors...
set it to readonly.
TomJ
by the way i disconnected before seeing your answer on moving supartitions to another table. I used this two step method - is this what you would have done? http://pastebin.com/d7dc50907
         

RJarett
exportdp transportable_tablespace it.
TomJ
ok thanks
RJarett
save the metadata dump, and datafiles
TomJ
will try that as soon as it's ready
RJarett
then drop the tablespace from the DB
id expdp the data too to be on the safe side
or do a normal tablespace rman backup
TomJ
yeah i have both a DB backup and a whole DB expdp export
ok thanks very much
MTecknology
Any ideas what I'm doing wrong here? http://oracle.pastebin.com/m1f90cb2f
I suppose an error would help.. http://oracle.pastebin.com/m2e70f939
Reign1
hi, what extent size would you advice, if table is lets say 40 million records?
TheBonsai
usually that's something you should deligate to the rdbms
RuslanPopov
can't find dbca at ora11/bin
what utility do I have to use to create database?
TheBonsai
er, dbca :)
Reign1
TheBonsai: i do set initial and next one in storage section while creating table
TheBonsai
the number of records is secondary, how large are the records (in terms of storage)? how's the change frequency? etc
RuslanPopov
what place does oracle installer write its log on sun solaris?
it seemd there is an ugly installation, damn admins
I guess /tmp
Reign1
TheBonsai: tables are like ~3.5GB, if u ask this, and those changes once per 24h
atm got this: INITIAL 1048576 NEXT 1048576
TheBonsai
Reign1: if they're re-created every 24h, it might be worth to think about allocating a very big extent. though, that's milliseconds. if you do a full table load, for example with a batch job, you shouldn't be able to measure a big difference. when it's extended during the day, it will slow down things a little bit
Reign1
nah, tables are created once, and updates once per 24h (at night) with a new portion of data (last day data)
TheBonsai
well, if the extent config is unlucky, then this job will run a second slower (because the instance will allocate the next extent). does that matter?
(assuming the job appends data, and doesn't just update)
Reign1
i guess it doesnt then
siamba
! ORA-01502 !
why it happened to me ;(
         

RuslanPopov
tnsnames?
cmug
hurrah, last 9i db's killed!
hali
woho
RuslanPopov
cmug, congratulation!
cmug
took us about 36hours work in the weekend, talk about crosseyed here
siamba
why 01502 happens?
RuslanPopov
what is 01502?
hali
siamba: could be lots of reasons, have you rebuilt it to fix it now?
siamba
hali, yes, I've already rebuilt it and everything works fine
hali
siamba: is it a partitioned table
siamba
RuslanPopov, "ORA-01502: index 'USER.RECORDS_ID_PK' or partition of such index is in unusable state"
hali, no
hali you think that could happen because of asm on 10g2?
TheBonsai
it usually happens because an index is unusable :D
siamba
TheBonsai :)
hali
siamba: unlikely, more likely to be a user error or corruption
siamba: nothing in the alert log?
siamba
hali seems like nothing interesting, but I think it happened after resizing/reorganizing tables
hali
siamba: reoganize will change the ROWID and hence render the index unusable
siamba
hali ;(
TomJ
hali: Can you confirm it's possible to use a single ASM diskgroup for two hosts to run two separate databsaes from? I have 36 x SAN disks, all are in a single ASM disk group, called DBLIVE1. DB1 runs PRODDB out of +DBLIVE1/PRODDB/ . I then went DB2 to run a new STAGDB out of +DBLIVE1/STAGDB/
so it's two hosts, two independent, unrelated DBs, but running from a single disk group on shared storage
does that work OK?
hali
yes
not sure if you need clusterware on the hosts though
i would think you do
TomJ
is clusterware just ocssd.bin ? I have that running anyway - thought it was a requirement to use ASM. Or is clusterware somethig more than that?
dagun
smth more
usn_work
qwak
Hi all - has anone here an idea for this question? It's about case insensitive search in Oracle.
http://www.freelists.org/post/oracle-l/Indexes-for-Case-Insensitive-search
sytse
use postgres? :P
(Action) hides
usn_work
:P
hali
ctx can do that but will make it more complex to maintain and query
usn_work
yeah, the queries have to stay standard-SQL in a kind of way
(portability to MSSQL)
sytse
CREATE INDEX col_idx ON tbl ((lower(col)))... it -just works- in postgres
nice
hali
that's not really case insensitive
it just forces it to lowercase
hence the query must use the function too
usn_work
and that's exactly what I DO NOT want, sytse
TomJ
hali: re needing clusterware, is that just ocssd.bin ? I have that running - it made me run it to use ASM. Or is clusterware somethig more than that?
hali
try it :)
im not sure
sytse
usn_work: oh, okay.. and I heard from a coworker that that is possible with oracle, too..
sorry, I just re-read the post, now I get it :-)
usn_work
TomJ, you are on 10g/11gR1?
did you make ocssd.bin run with "localconfig add"?
does crsd run?
sytse
http://richardfoote.wordpress.com/2008/01/09/introduction-to-linguistic-indexes-part-ii/ <-- fwiw, some issues with using linguistic indexes with good demos
usn_work
tnx
meatbizzy
So i created 7 DB's using dbca, they are started an I can login using sqlplus
but my listner isnt starting
hali
do you really need 7 instances?
meatbizzy
yea
hali
creating users in one instance is the same as creating 7 db's in most other rdbms platforms
mator
should huge memory footprint
should be
(Action) & installing new T5120
hali
anyway, what error does lsnrctl start give then?
meatbizzy
it just not starting for any of them
starts for "PLSExtProc"
and status is unknonw
hali
and if you issue alter system register; in an instance?
and has the listener got a default config? i.e. you haven't renamed it or anything
meatbizzy
yea
i have installed and used dbca in the past and never had this problem
found it
local_listner=
had a value that was wrong (i didnt set it)
i removed the value to blank and it worked
rservers
huzzah
mastro
i remember to have read something about creating table and setting some parameter to tune table that you know will be big. something about size of segments created that should be larger for large table. do I remember well? What's this parameter? and how do I choose the right setting?
something about increasing the Storage (Initial ---) setting
usn_work
PCTFREE?
INITRANS for ITL?
Initial extent size?
dsdel
mastro: http://www.ulrik.de/defstorage.html
mastro
dsdel, thank you very much i'll read it carefully
usn_work, I don't really know. i red something about tuning the performance for big tables (with a lot of rows) that said something about creating the table you know will became huge with some parameter that will make it perform better
usn_work, i'm new to oracle :)
dsdel
mastro: indexes && stats are important also :)
mastro
dsdel, hum.. i need to understand better this before applying it.
dsdel, can i apply those parameter after a while the table has been created?
dsdel
just scroll down :)
but perhaps it's better to expdp/impdp it to avoid fragmentation
or move to antoher tablespace
mastro
dsdel, thanks for your suggestion
« prev next »