Home » RDBMS Server » Backup & Recovery » Backing up from a Schema
Backing up from a Schema [message #179519] Tue, 27 June 2006 16:17 Go to next message
JenniferBabes
Messages: 18
Registered: March 2006
Location: Student
Junior Member

HI all. I recently posted a question about backing and recovering. I am thankful that I got it through successfully.

Now some guysfrom work here created a schema to the database. I am a newbie so I'mnow researching about schemas. I know how to select from a schema and view tables from a schema. But I want to know how to export and import from and to a schema on separate machines.

I already know how to export adn import a database or a user to another machine.

Does anyone knows?

With thanks.

Jennifer
Re: Backing up from a Schema [message #179523 is a reply to message #179519] Tue, 27 June 2006 16:42 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you know how to export user, then you know how to export schema as well. Schema is, shortly, user + his database objects.
Re: Backing up from a Schema [message #180902 is a reply to message #179519] Wed, 05 July 2006 15:11 Go to previous messageGo to next message
JenniferBabes
Messages: 18
Registered: March 2006
Location: Student
Junior Member

Ok. I got the export to end successfully. I selected the tables to export, and identified each table as Schema.Tablename.

Now it is the import I am getting some trouble with. I don't know if I dont understand what a schema is. What I think a schema is, is like a partition in the database. The name of their schema is SchemaSystem. What I want to do, is take these tables, and copy it to my oracle database at home, with the same schema. First I don't know how to create a schema. The little research I did implied that you create a schema with an already created user. However, they told me their schema is created without a user but again they arent telling me how it was done.

I want to copy the tables from my work database to my home database. The problem I am seeing is that I don't know how to create that schema in my home database and import from the dmp file.

I'll be looking for any replies.


With thanks,
Jennifer.
Re: Backing up from a Schema [message #181044 is a reply to message #179519] Thu, 06 July 2006 08:27 Go to previous messageGo to next message
JenniferBabes
Messages: 18
Registered: March 2006
Location: Student
Junior Member

Ok I am so confused now. At home I was trying to solve this problem and I at home I have a user called temp, and I tried from the system user: Select * from temp.tablename;

And this SQL statement worked!

Now from my readings, I'm getting that most peolpe are saying that schemas and users are the same thing. Is that true? Is it that there isnt any difference between them?


Confused,
Jennifer
Re: Backing up from a Schema [message #181336 is a reply to message #181044] Sat, 08 July 2006 16:45 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As a privileged user (SYS, for example), you can create a user in your database:

SYS> create user new_user identified by his_password;

Now grant some privileges in order for this user to be able to work; previous Oracle versions had CONNECT and RESOURCE roles which contained almost all necessary privileges; now, in recent Oracle database versions, those roles are here for backward compatibility. It is suggested that one should create his own roles and adjust them to his needs.

As an example, let as grant just those two roles:

SYS> grant connect, resource to new_user;

Now connect to this user:

SYS> connect new_user/his_password

Now you can (of course, depending on granted privileges) create tables, write queries, create indexes, triggers ... a lot of things. Creating database objects will turn our user into a [/i]schema[/i] ; although people usually mix terms "user" and "schema", I believe there's no much harm in it.

As you already have an export file taken from the work, you can now import those objects into newly created user. At the OS prompt issue such a command:

OS> imp new_user/his_password FILE=work.dmp

and that'll do it; you'll get a warning message that export was done by another user, but don't pay much attention to it - it is a normal condition. Finally, import should end with a line as "import finished successfully with warnings".

You might need to recompile views, packages, procedures, functions and triggers before having functional copy of your work schema at your home. Also, if there were some database links, synonyms etc. which used objects that are not accessible in your home database, you may expect some difficulties.
Re: Backing up from a Schema [message #184405 is a reply to message #179519] Wed, 26 July 2006 07:46 Go to previous message
JenniferBabes
Messages: 18
Registered: March 2006
Location: Student
Junior Member

Thank you LIttle Foot. Your post solved my problem. Thank you - Jennifer
Previous Topic: not enough disk space error
Next Topic: Regarding IMPORT ....
Goto Forum:
  


Current Time: Sun Nov 24 11:37:30 CST 2024