Home » SQL & PL/SQL » SQL & PL/SQL » how to create schema in this book
how to create schema in this book [message #407779] Thu, 11 June 2009 09:30 Go to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Could you please help me in creating this schema so I can practice the examples in this book SQL jumpstart in this link http://www.scribd.com/doc/4056842/Oracle-SQL-Jumpstart-With-Examples in the end of the book there is ready tables with schema if you could help me how can I create it. Thanks a lot for your help
Re: how to create schema in this book [message #407780 is a reply to message #407779] Thu, 11 June 2009 09:33 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Search.

By
Vamsi
Re: how to create schema in this book [message #407782 is a reply to message #407779] Thu, 11 June 2009 09:54 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
I need a help please how to do it. I searched a lot with no benefit. If you could help I'll be so glad. Thanks a lot
Re: how to create schema in this book [message #407795 is a reply to message #407782] Thu, 11 June 2009 12:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So you want to create a schema.

Vamsi kasina posted a link to the documentation where the "create schema" command is explained with examples.

If you don't tell us why you are not able to follow that examples, and what the exact problem is that you are facing, I don't think anyone will be able to help you any further.
Re: how to create schema in this book [message #407836 is a reply to message #407779] Thu, 11 June 2009 17:25 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Thanks Thomas and thanks Vamsi for your contribution with me. you are really try to help me but the problem while I'm studying from the book the all the Tables are not in the Oracle database to practice the examples in the book. So the author mentioned to create the all the tables from the appendix and I putted the link in my first post to the book if someone want to see all the tables the author wrote in his book.

I tried to specify each tables in specific file and I tried to execute but with no avail. Actually they are using something for creating all the tables in a way which is in advanced level.

I'm really very sorry when I said create schema in my first post but I meant how to create the whole tables from that book. I'm attaching some files I made it from his book if you could look at them if they are correct to load it or wrong because it gave me error when I load them.


The link of the book in my first post. Thanks and I appreciate your help.
Re: how to create schema in this book [message #407852 is a reply to message #407836] Thu, 11 June 2009 23:19 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member


Quote:
because it gave me error when I load them.



what Error?
SQL> @ d:\CREATEUSER.sql
SP2-0606: Cannot create SPOOL file "log/CREATEUSER.LOG"
DROP USER MUSIC CASCADE
          *
ERROR at line 1:
ORA-01918: user 'MUSIC' does not exist


CREATE USER MUSIC IDENTIFIED BY MUSIC
                                *
ERROR at line 1:
ORA-01031: insufficient privileges


GRANT CONNECT,RESOURCE TO MUSIC
*
ERROR at line 1:
ORA-01031: insufficient privileges


GRANT UNLIMITED TABLESPACE TO MUSIC
*
ERROR at line 1:
ORA-01031: insufficient privileges


not spooling currently


Are they like above specified?

Always post your test case here so that members will help you.
Read post guide lines.
Post DDL,DML of your post.

Regards
Sriram
Re: how to create schema in this book [message #407927 is a reply to message #407779] Fri, 12 June 2009 07:04 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Thanks Ramoradba for your help. yes you are right this error come to me when I issue it from SQL*Plus but if I execute this file from iSQL in the browsed is different error come to me.

So how to solve this problem. Really Thanks a million for your help.

and the rest of the SQL files are also giving me the same error.
By the way, the three SQL files I attached in my previous post are not completed so you can return to the book so you can find in the end the whole database the author used.
http://www.scribd.com/doc/4056842/Oracle-SQL-Jumpstart-With-Examples
Re: how to create schema in this book [message #407933 is a reply to message #407927] Fri, 12 June 2009 07:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The link to Scribd still gives me an "There was an error converting this document.", so I still can't see what's in there.

Which error of the different ones in ramoradbas post do you get ?

When you get the "insufficient privileges", then you run the script with the wrong user.

If you get the "Cannot create SPOOL file", then you need to change the spool file to a directory/filename that is supported by your platform.

The best thing would be to start to learn how to set up the database first.

Also, if you run something and get errors, post a FORMATTED COPY AND PASTE of your COMPLETE SQL*Plus session when you get any errors.

Re: how to create schema in this book [message #407989 is a reply to message #407779] Fri, 12 June 2009 14:07 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Thanks a lot Thomas and everybody.

"When you get the "insufficient privileges", then you run the script with the wrong user."

what you mean wrong user, actually I created a user and I gave a full grant privilege to it.
=============================

"If you get the "Cannot create SPOOL file", then you need to change the spool file to a directory/filename that is supported by your platform."

how to change the spool file to a directory.

=============================

There are a lot of tables the author posted in the end of the book. I feel sorry about that link and that is true it's not working. I'm posted here a link of the book so you can download it and look to the end of the book in the appendix to see the whole database of tables.
http://kewlshare.com/dl/e57468d37d04/LiP-Oracle_SQL_Jumpstart_with_Examples.rar.html

I'm also attaching here a picture that show different error in different environment.
./fa/6343/0/
  • Attachment: SQL1.png
    (Size: 57.15KB, Downloaded 1124 times)
Re: how to create schema in this book [message #407998 is a reply to message #407989] Fri, 12 June 2009 16:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
what you mean wrong user, actually I created a user and I gave a full grant privilege to it.


No, you didn't. The user you run the script under doesn't have the privileged to create new users, You have to run the script with a user that has DBA privileges.


Re: how to create schema in this book [message #408003 is a reply to message #407779] Fri, 12 June 2009 19:09 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Thanks Thomas for your contribution.


I used this statement in Sys

SQL> grant sysdba to william;

Grant succeeded.
===========

but nothing happened same error displayed again. what should I do.

Re: how to create schema in this book [message #408004 is a reply to message #408003] Fri, 12 June 2009 19:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Not "sysdba". "dba".

They are two different things.

Any you still have to change the UNIX paths names in the script to windows path names, it seems.
Re: how to create schema in this book [message #408068 is a reply to message #407779] Sat, 13 June 2009 10:08 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Hi Thomas

I didn't understand what you meant. I read the website you gave me but it was talking about sysdba & sysoper and I know that sysdba is the most power privilege.

Could you please explain more. Thanks Thomas.
Re: how to create schema in this book [message #408070 is a reply to message #407779] Sat, 13 June 2009 10:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm

There is a difference between PRIVILEGE & ROLE.

DBA not equal SYSDBA
Re: how to create schema in this book [message #408094 is a reply to message #408070] Sat, 13 June 2009 16:57 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
- Change he path names in the script from the obvious UNIX ones to Windows ones

- Run the script with a user that has DBA privileges. (and thus can create new users)

Re: how to create schema in this book [message #408095 is a reply to message #407779] Sat, 13 June 2009 20:20 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Thanks Thomas and Blackswan for your help. I really I feel sorry about asking so many questions.

Could you please specify more on how to change the path names in the script from the obvious UNIX ones to Windows ones
Re: how to create schema in this book [message #408136 is a reply to message #408095] Sun, 14 June 2009 12:10 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I would suggest that you take some basic courses on how your operating system works, before trying to learn oracle.

The first error in the script already TELLS you that a file log/CREATEUSER.log can't be created.

So is there a directory "log" in the directory from which you run the script? Is log/CREATEUSER.log a valid way in Windows to specify a directory and file?
Re: how to create schema in this book [message #408340 is a reply to message #407779] Mon, 15 June 2009 18:29 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Hi Thomas, I feel really sorry because I'm tiring you with me.

By the way, I have experience in computer since 2001 and I studied a course that talk how operating system works. But the problem I didn't get to understand you well, that's it. sorry for that.

When you talk about Unix here yes I don't have experience with Unix.

"So is there a directory "log" in the directory from which you run the script? Is log/CREATEUSER.log a valid way in Windows to specify a directory and file?"


I'm also asking you again when you talked about director which directory you meant. I'm executing the file from drive D: so the file that I'm executing doesn't have any log. Could you please be explain more.

Thanks Thomas for your help, I know I'm tiring you with me but I have no other choice.
Re: how to create schema in this book [message #408341 is a reply to message #407779] Mon, 15 June 2009 18:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=free+online+windows+tutorial
Re: how to create schema in this book [message #408342 is a reply to message #407779] Mon, 15 June 2009 18:46 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
hi blackswan
the problem I don't understand you.


even though "Ramoradba" you have a problem when you execute the file you got the same problem. so tell me if you solve it.

Please explain well so others can understand.
Re: how to create schema in this book [message #408344 is a reply to message #407779] Mon, 15 June 2009 19:14 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Ok,
what is your real problem with the spool command. You work in Window and running your script from drive D. So why don't you just change the spool to create the file somewhere else?

Something like SPOOL d:\CREATEUSER.LOG
Re: how to create schema in this book [message #408350 is a reply to message #407779] Mon, 15 June 2009 20:05 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Thanks Kaeluan for your contribution and helping.

I did as you said to me the same command
spool d:\createuser.log

The problem still there, and I just want to tell you there is no log file with this name createuser.log in d or on my computer.

You know usually I can execute any file that has a table except what is in the book, I tried to copy the database table to a file and I name all the database to specific tables as I gave some of them in my first post of this thread.

This is the book that I want to create the tables and the author put all the creating tables in the Appendix, take a look.
http://kewlshare.com/dl/e57468d37d04/LiP-Oracle_SQL_Jumpstart_with_Examples.rar.html

Actually the author should make the tables easy to create.

Thanks
Re: how to create schema in this book [message #408351 is a reply to message #407779] Mon, 15 June 2009 20:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Actually the author should make the tables easy to create.
It easy to CREATE TABLE. It is just basic SQL.

I suggest you email the author & complain that you did not get your monies worth from his lousy book.

By the way, how much did you pay for this book?
Re: how to create schema in this book [message #408357 is a reply to message #407779] Mon, 15 June 2009 21:03 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Ok, i think you just have no idea about what the spool command is doing.

I know that you probably don't have a file createuser.log on your D drive.

SPOOL allow you to send the output of sqlplus in file. So all command that you will issue after the spool command will appear in the log file with the result of the command. This allow you to keep a log of the command that was executed.

The following exemple will create a log file

SQL> spool c:\createuser.log
SQL> select 1 from dual;

         1
----------
         1

SQL> spool off
SQL> 
Re: how to create schema in this book [message #408364 is a reply to message #407779] Mon, 15 June 2009 21:25 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Ok, i just tried your script to create the user.
I had to fix it because QUOTA unlimited on temp tablespace is not allowed in 10gR2. so here is the script

SPOOL C:\ora_10g\CREATEUSER.LOG;
DROP USER MUSIC CASCADE;
CREATE USER MUSIC IDENTIFIED BY MUSIC
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
GRANT CONNECT,RESOURCE TO MUSIC;
GRANT UNLIMITED TABLESPACE TO MUSIC;
SPOOL OFF;


And the result is
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

NF@dev > connect system@dev
Enter password: **********
Connected.
SYSTEM@dev > @createuser.sql
DROP USER MUSIC CASCADE
          *
ERROR at line 1:
ORA-01918: user 'MUSIC' does not exist



User created.


Grant succeeded.


Grant succeeded.

SYSTEM@dev > 


and the result of the log file is
DROP USER MUSIC CASCADE
          *
ERROR at line 1:
ORA-01918: user 'MUSIC' does not exist 


User created.


Grant succeeded.


Grant succeeded.

Re: how to create schema in this book [message #408506 is a reply to message #407779] Tue, 16 June 2009 08:08 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Thanks Kaeluan for your help. Yes you are right, I don't have a much of idea what spool do exactly more than record the SQL outputs, but what is the benefit of using it I have no idea. Actually I took a course in SQL before but the instructor didn't explain a lot of things in SQL that's why my experience is not that's much.


I changed the script you wrote it for me according to my Oracle database directory which is installed on drive D:\

SPOOL D:\Oracle\CREATEUSER.LOG;
DROP USER MUSIC CASCADE;
CREATE USER MUSIC IDENTIFIED BY MUSIC
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
GRANT CONNECT,RESOURCE TO MUSIC;
GRANT UNLIMITED TABLESPACE TO MUSIC;
SPOOL OFF;


And this is the result is saying there is not insufficient privileges for this user, even though I granted this user to be sysdba like this syntax from sys user
grant sysdba to william;

check this result
SQL> @ d:\createuser.sql
DROP USER MUSIC CASCADE
          *
ERROR at line 1:
ORA-01918: user 'MUSIC' does not exist


CREATE USER MUSIC IDENTIFIED BY MUSIC
                                *
ERROR at line 1:
ORA-01031: insufficient privileges


GRANT CONNECT,RESOURCE TO MUSIC
*
ERROR at line 1:
ORA-01031: insufficient privileges


GRANT UNLIMITED TABLESPACE TO MUSIC
*
ERROR at line 1:
ORA-01031: insufficient privileges
Re: how to create schema in this book [message #408509 is a reply to message #408004] Tue, 16 June 2009 08:22 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
ThomasG wrote on Sat, 13 June 2009 01:32
Not "sysdba". "dba".

They are two different things.


Re: how to create schema in this book [message #408512 is a reply to message #407779] Tue, 16 June 2009 08:29 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
As you can see in my script i was logged as SYSTEM to create the user. I never tried creating one from another user than SYSTEM. I can not try it at work but will give it a try at home tonight.
Re: how to create schema in this book [message #408563 is a reply to message #407779] Tue, 16 June 2009 20:17 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Ok, so here is my result

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

NF@dev > connect system@dev
Enter password: **********
Connected.
SYSTEM@dev > grant dba to nf;

Grant succeeded.

SYSTEM@dev > connect nf@dev
Enter password: ******
Connected.
NF@dev > @createuser.sql
DROP USER MUSIC CASCADE
          *
ERROR at line 1:
ORA-01918: user 'MUSIC' does not exist

User created.

Grant succeeded.

Grant succeeded.


Everything work fine. You need to log in as system to grant the role DBA to your user. After this you need to reconnect on your user and you run the script.

Hope it will work for you too.
Re: how to create schema in this book [message #409035 is a reply to message #407779] Thu, 18 June 2009 18:36 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Thanks Mr.Kaeluan for your help. Your help are really highly appreciated and also the other contributors as well.

now when I execute this file it's working fine. But I'm stuck in the other files which is the main file that calls musicmaster.sql this file will execute all the files that in .sql so it will help instead executing each file.

I'm uploading the whole schema for this book in the attachment, actually I collected all from the book.

when I execute this file musicmaster.sql it asked me this question that I have no idea what shall I write

SQL> @ d:\SQL\musicmaster.sql
WHAT IS THE DATABASE OR NETWORK NAME? (SUCH AS: ORADB10)

what shall I write after this message.


Thanks a lot.
  • Attachment: SQL.rar
    (Size: 14.43KB, Downloaded 126 times)
Re: how to create schema in this book [message #409038 is a reply to message #407779] Thu, 18 June 2009 19:52 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=online+sql+tutorial+free+oracle
Re: how to create schema in this book [message #409039 is a reply to message #407779] Thu, 18 June 2009 21:24 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
the database name is the name that you have to enter when you start SQL Plus. You enter username, password and host string. Host String = Database name.

What the mastermusic.sql script is trying to do is connect on the SYSTEM account to run the createuser.sql script. So you need to know the password for the SYSTEM account. After creating the user MUSIC it will connect on this user and run all other script to build the environment.

Hope it help
Re: how to create schema in this book [message #409218 is a reply to message #409039] Sat, 20 June 2009 10:46 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
Host String = Database name.



Are you sure?Question


Hoststring <> Database name.
we can also specify the connect_identifier as an hoststring.

Regards
Sriram
Re: how to create schema in this book [message #409238 is a reply to message #407779] Sat, 20 June 2009 22:23 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
I know that Host String is not the database name or the instance name but for him when prompted for Host String when starting SQL Plus it will be the same value than prompted when running the script so it will be more easy for him to understand what he should enter when prompted
Re: how to create schema in this book [message #409258 is a reply to message #407779] Sun, 21 June 2009 05:22 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Thanks Mr.Kaeluan for your support.

hi blackswan, listen do u have a tutorial for Forms, because now I'm trying to create a project now. actually I did but the people with me doesn't like it I don't know why so I have to improve it a lot. Once I finish from the latest one I'll upload it here for everyone, but u know using property palette is a little bit difficult to know each others functions.

Anyway I'll return to my main problem.

Mr.Kaeluan I did as you said and really thanks a lot, but you know when I executed the file it was reading from all SQL files but it was fast but I could see and error while implementing the whole files, I don't if there is any problems or not. I made a query for some tables but 4 tables doesn't have values inside inside them, and the rest when I checked the the content of tables it doesn't seem it has a lot of attributes of values inside them, because when I made the SQL files I noticed there are a lot of insert in the tables. Could you please take a look to my picture in the attachment.
./fa/6379/0/
Thanks
  • Attachment: sqloutput.png
    (Size: 24.28KB, Downloaded 820 times)
Re: how to create schema in this book [message #409259 is a reply to message #409258] Sun, 21 June 2009 05:27 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So there is an SQL file we don't have, an *possible* Error we didn't see and an empty table.

Congratulations, you just won the mystery of the day contest.
Previous Topic: trigger has :new is a collection type
Next Topic: updating table 2
Goto Forum:
  


Current Time: Sun Dec 11 02:02:44 CST 2016

Total time taken to generate the page: 0.08284 seconds