logs archiveIRC Archive / Freenode / #oracle / 2010 / July / 7 / 1
checkpoint
Is there a document which the oracle that speaks best operating system for the database for performance?
xiaoshen
hi all^
is there any command to generate the ddl for al tables, indexes, triggers and sequences?
xD
Dormir
take a look at dbms_metadata
xiaoshen
Dormir, what do u mean?
i'm a veryveryvery newbie here
TheBonsai
google for dbms_metadata examples (dbms_metadata.get_ddl)
it might be as easy as select dbms_metadata.get_ddl(....., table_name, .....) from user_tables;
i.e. it's integrated into a query, as a procedure or function call, and returns text from the query as sql result
MChizhov
hello
i am trying to refresh mat view
and i recieved next error
12:10:39 Oracle Dictionary: Access to DBA Views is required to view Rollback Segment data
how cfn i fix it?
*can
zabka
hi, i have a table with many countries. i wish to have a coulumn with an actual time zone. how/where can i get the actual zone for countries? is there any web service?
mator
zabka, wikipedia
zabka
mator ?
         

mator
http://en.wikipedia.org/wiki/Time_zone
hali
and one country can be in several time zones
like russia and the us
mator
and http://en.wikipedia.org/wiki/List_of_time_zones_by_country
yes yes
besides of DST
so it's up to user to select his timezone
TheBonsai
is it possible to connect to +ASM using SQL*Net at all? i know it has BLOCKED status. background: monitoring (and i wonder how the oracle grid control does it)
oh
(UR=A)
kaushal
hi
I installed oracle-instantclient-basic-10.2.0.4-1.i386.rpm on suse linux 9.3
how do i start the client and connect it to the database ?
I dont see any documentation
hali
install sqlplus too
gladier
depends on what application you're using - i fyou want raw access to the db so you can manipulate it - sqlplus. otherwise your website/lob application/etc should just launch the libs from the instantclient and pretty much just work once your tnsnames is setup
kaushal
hali: where can i get sqlplus ?
usn_work
do you have a client installed?
kaushal
I installed oracle-instantclient-basic-10.2.0.4-1.i386.rpm
usn_work
so you loose ;) install admin client, the instant one is just libraries
kaushal
ok
usn_work: Thanks
I dont see admin client here http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html
usn_work
maybe the "instantclient" in the url is the problem?
kaushal
usn_work: did not work
usn_work
kaushal, http://www.oracle.com/technology/software/products/database/index.html
andylockran
hey guys - I could do with some help in creating a new database in Oracle 10g
RJarett
dbca <press enter>
follow the wizard
andylockran
I have the XE setup, but don't understand the whole SID thing. I'm coming from a fairly basic MySQL environment
RJarett:-bash: dbca: command not found
RJarett
ok in XE you are limited to the 1 instance it created on install
andylockran
RJarett: so I can only ever have one DB setup on this instance?
         

RJarett
you can make new instances in XE but its a pain
it wasnt really ever meant for it
its just a tiny system
http://devtime.blogspot.com/2006/01/creating-oracle-xe-database-manually.html
andylockran
RJarett: what would be the recommended alternative - i.e. we've got a dev setup and want to also have a qa db
RJarett
dont use XE in anything related to production
andylockran
in MySQL I guess I could prefix the tables with dev_ and qa_ - is there an oracle equiv to that?
RJarett
or dev
use a new schema
aka a new user
make a dev user and a qa user
they will hold their objects in their own space in the db
but remember, XE has a limit of 4gb on disk
andylockran
so using a different user will in effect use a different dataset ?
RJarett
yes
but you can call between them if you grant permissions
see, coming from mysql, it makes people stupid.
pickup and intro to oracle book. learn about what an instance is
and how it deals with security
youre walking yourself into a wall of possible exploits if you come from a mysql background
DiscordianUK
Sage advice there
The two DBMSes are very different
andylockran
RJarett: thanks for the advice. We're developing for a third party, who are managing the live Oracle System - but we've had to setup a test just to check it functions... hence my lack of knowledge.
Thanks for your help.
gladier
andylockran: i've done a rough summary of the differences between mssql/mysql and oracle in my blog
in terms of terminology
on sec i'll grab a link
http://blog.gladier.net/?p=45
the oracle overview section
andylockran
looks great - just having a read now. Much appreciated.
volkerp
same here :-)
gladier
im still an oracle noob but im pretty sure its accurate
nobody has told me i'm completely wrong yet .. i shall take this as a good sign :P
volkerp
can/are tablespaces shared among databases?
RJarett
no
shared is a broad word
volkerp
may a tablespace be used by more than one database?
hali
we should define the term 'database' first
RJarett
not at the same time
hali
a database in oracle is not the same thing as 'a database' in mysql/sqlserver/sybase
volkerp
so the 'system' tablespace is always database unique?
hali
a schema user is what most rdbms engines call a database
RJarett
you can use transportable tablespaces to shift data from one DB to another using the dbf
hali
generally you should only have one database per machine or per cluster of machiens (in rac)
one database instance, then schema users in that
RJarett
i wouldnt go so far to say that...
but no its not like mysql or sqlserver where everything is a db of its own
zabka
http://www.techonthenet.com/oracle/functions/new_time.php are there the only avaiable zones, that are listed in the table?
RJarett
you can use offset based
SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES;
oracle uses ALL standards based zones
anything in the NIH based US Govt data catalog
almost 1400 zones
ftp://elsie.nci.nih.gov/pub/
but the only important ones are american
the rest of you can piss off
hali
cheers :)
RJarett
especially canada. freakin hording all the maple syrup
hali
we're not the ones fu*king around with DST changes
RJarett
where are you from?
hali
im in the UK
(sweden born)
RJarett
ahh. because you idiots changed how you did it in the 90s
gladier
hah.. better than using microsfot HMC which requries the provisoining servers to be in mountain standard time .. even though the servers are in australia
RJarett
71 72 81 84 95-97
all changed BST in UK
you guys change it more than most people change underwear
usn_work
smelly thing
andylockran
big up the UK.
ok - so if I setup a different user. how can I get them to have their own tablespace.. would appear like there is conflict with the current DB
gladier
erm .. each user is their own schema
andylockran
how to set schema?
gladier
ie tablename.username
so you can have two tables named FOOTABLE in the same tablespace under two different users - it will be sotred as FOOTABLE.user1 and FOOTABLE.user2
andylockran
yeah, that'd be good
gladier
as in
thats the default
andylockran
ORA-00955 is what I get when trying to insert a table with the same name, but using a different user
gladier
so when you create user1 and user2 - they are their own schema ... or if you're from the mssql/mysql world - each user is their own database
andylockran
hmm.. so why is there a conflict if this is a different user updating the db ?
I am using XE if that makes a difference
gladier
shouldn't do . but it's a question i have no clue how to answer/troubleshoot ... like i said earlier i'm an oracle noob
RJarett
do you know how to google?
andylockran
RJarett: I am googling now
RJarett
user1.table user2.table
and you need to be granted permissions
andylockran
RJarett: so I need to specifically rename the tables?
RJarett
you really should pickup an intro to oracle ebook
no
where the hell did i show 2 different table names?
andylockran
user1.table user2.table
RJarett
table and table
they look the same name to me
gladier
user[1}2] is the schema
andylockran
yeah, I get that.. but what I don't get is that when I'm using two different schemas, that there would be a conflict
RJarett
schemas are totally different
objects created by a schema are owned, and stores as that schema
just like you can have 2 files name wtfwhydontyougetthis.txt in windows in 2 user directories
andylockran
sure, I get that
and each user has their own schema
hence user1.table and user2.table
RJarett
if user 2 will select from user1.table, user1 must grant him permissions to it
andylockran
yup
understoof
d
usn_work
we should have a bot here telling this topic with three sentences on a keyword
I guess we hav the question at least twice a week
andylockran
where are the logs stored?
gladier
it is a complete paradigm shift of thinking since most people are used to mssql/mysql/pgsql/etc
RJarett
yeah because mysql and mssql makes people stupid
usn_work
yeah
gladier
oi :P i came form that land and still ahve to support proudcts that use them
andylockran
pft, I was stupid already so god help me
:)
RJarett: I still don't understand though, if user1 and user2 schemas are separate, then writing to user1 should have no affect on user2s stuff - and vice versa. so why do I get a name is already used by existing object when writing to a blank user1 schema
usn_work
you don't
RJarett
you screwed something up
select user from dual
are you really who you think you are?
andylockran
SQL> select user from dual;
USER
------------------------------
ZYMQA
sorry for the multi-line paste
which is who I expect to be
gladier
and then have you done something like tried to import the same data twice
andylockran
other user is Alex
import the same data twice.. explain why that'd be a problem
gladier
well you can have a script that goes "create table foo;" and then if you try to run the same command again you'll get that ORA error
andylockran
yup, that's exactly what I have..
but connecting using different users.
RJarett
doubtful
andylockran
CREATE SEQUENCE pte_clientspte_clients_ids START WITH 1 INCREMENT BY 1',
is the command
first time was run by alex user - second time by zymqa user
gladier
are you sure you were the right user when you created everything - select sequence_name from user_sequences; should show you all of the sequences for your current user
usn_work
drop it, do it again
show us all you do in a pastebin window
all commands, prompts AND error messages
andylockran
SEQUENCE_NAME
------------------------------
PTE_CLIENTSPTE_CLIENTS_IDS
usn_work
anything else is waste of time
gladier
ok so your sequence already exists
drop sequence PTE_CLIENTSPTE_CLIENTS_IDS;
usn_work
andylockran, drop it, do it again, show us all you do in a pastebin window, all commands, prompts AND error messages
gladier
you'll find that this wont affect the sequence for your other user - the whole two seperete schema thing
andylockran
hmm
« prev 1 2 next »