Oracle database Installation and configuration (i)

http://blog.csdn.net/phacker2010/article/details/7190126

1. Installation

Oracle version:Oracle DatabaseG Release 2 (10.2.0.1)

：

Http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201winsoft.html

Installation settings:

1) The global database name here is the name of the database you created, which is used when accessing data and creating "local Net service name";

2) The database password is used in places such as login and create local NET service name.

2. Create "Local NET service name"

1) Run the Network Configuration Assistant tool through "program"-"oracle-oradb10g_home1"-"configuration and porting Tools"-"Net config Assistant":

2) Select "Local Net Service name Configuration":

3) "Net Service name" Here we enter the "Global database name" When installing the database:

4) Host name we enter the IP address of this machine:

5) Test database connection, username/password: system/database password ("Database password" entered during installation):

Default User name/Password error:

Change login, enter the correct username/password:

Test success:

3. Plsql Developer Connection Test

Enter the correct user name/password:

Successful login:

Database

4. CREATE TABLE Space

Open the Sqlplus tool:

SQL code

1. Sqlplus/nolog

To connect to a database:

SQL code

1. Conn/as SYSDBA

To create a table space:

SQL code

1. Create tablespace camds datafile ' D:\oracle\product\10.2.0\oradata\camds\camds.dbf ' size 200m autoextend on next 10m maxs Ize Unlimited;

5. Create a new user

Run the p/l SQL Developer tool and log in as DBA (user name: System):

1) New "user":

2) Set the user name, password, default tablespace (using the table space created above), and temporary tablespace:

3) Set role permissions:

4) Set "System Permissions":

5) After clicking on the app, the "Apply" button is dimmed and the new user is created successfully:

6) New User login test:

Enter the user name/password for the new user:

New user "Testcamds" successfully landed:

6. Import and Export database

Run the cmd command first, go to command-line mode, and go to the following directory: D:\oracle\product\10.2.0\db_1\BIN "There are exp.exe files in this directory"

1) Import

Command syntax:

SQL code

1. Imp userid/[email protected] File=path/file Fromuser=testcamds Touser=userid

Command instance:

SQL code

1. Imp testcamds/[email protected] File=c:\testcamds Fromuser=testcamds Touser=testcamds

Import Results:

2) Export:

Command syntax:

SQL code

1. Exp Userid/[email protected] File=path/file Owner=userid

Command instance:

SQL code

1. Exp Testcamds/[email protected] File=c:\testcamds Owner=testcamds

Import Results:

Create a temporary table space

Create temporary tablespace zfmi_temp
Tempfile ' D:\oracle\oradata\zfmi\zfmi_temp.dbf '
Size 32m
Autoextend on
Next 32m MaxSize 2048m
Extent management Local;

The Tempfile parameter must have


Create a data table space

Create Tablespace ZFMI
Logging
DataFile ' D:\oracle\oradata\zfmi\zfmi.dbf '
Size 100m
Autoextend on
Next 32m MaxSize 2048m
Extent management Local;

The datafile parameter must have


Delete User and user-owned objects

Drop user ZFMI cascade;

The Cascade parameter is a cascade to delete all objects of the user, often encountered such as the user has an object without this parameter user can not delete the problem, so the habitual add this parameter


Delete Table space
Premise: Before deleting a tablespace, make sure that the tablespace is not being used by another user before you delete the table space

Drop tablespace ZFMI including contents and datafiles cascade onstraints;

Including contents delete the contents of the table space, if you delete the table space before the table space has content, without this parameter, the table space is not deleted, so the habitual addition of this parameter
including datafiles deleting a data file in a tablespace
Cascade constraints also delete foreign key references for tables in tablespace


If you delete a tablespace file before you delete the table space, the workaround:

If you delete the data file for a tablespace before you clear the tablespace, the database will not start and close properly.
You can use the following method to recover (this method has been validated in oracle9i):
In the following procedure, filename is a data file that has been deleted, and if there are multiple, it needs to be executed multiple times; Tablespace_name is the name of the corresponding tablespace.
$ sqlplus/nolog
Sql> Conn/as sysdba;
If the database is already started, you need to perform the following line first:
sql> Shutdown Abort
Sql> Startup Mount
sql> ALTER DATABASE datafile ' filename ' offline drop;
sql> ALTER DATABASE open;
Sql> drop tablespace tablespace_name including contents;

Create a user and specify a tablespace

Create user Zfmi identified by ZFMI
Default tablespace zfmi temporary tablespace zfmi_temp;

Identified by parameter must have


Grant message user all permissions to the DBA role

GRANT DBA to Zfmi;


Granting permissions to Users

Grant Connect,resource to ZFMI; (DB2: Specify all permissions)


Import Export command:

Oracle data import and export imp/exp is equivalent to Oracle data restore and backup. The EXP command can export data from a remote database server to a local DMP file, and the IMP command can import the DMP file from a local to a distant database server. With this feature you can build two identical databases, one for testing and one for formal use.

Execution environment: can be executed in SQLPLUS.EXE or DOS (command line),
DOS can be executed due to the installation of directory Ora81bin in Oracle 8i is set to the global path,
This directory has EXP.EXE and IMP.EXE files that are used to perform import and export.
Oracle is written in Java, SQLPLUS. EXE, EXP. EXE, IMP. EXE these two files are likely to be packaged after the class file.
SQLPLUS. EXE calls EXP.EXE, IMP. EXE to complete the import and export function.

The following is an example of importing and exporting.
Data export:
1 full export of database test, user Name System Password Manager exported to D:daochu.dmp
Exp System/[email protected] file=d:daochu.dmp full=y
2 Exporting the system user in the database to the SYS user's table
Exp System/[email protected] file=d:daochu.dmp owner= (System,sys)
3 Exporting Tables Inner_notify, Notify_staff_relat in the database
Exp Aichannel/[email protected] file= d:datanewsmgnt.dmp tables= (Inner_notify,notify_staff_relat)

4 Export the field filed1 in table table1 in the database with data beginning with "00"
Exp System/[email protected] file=d:daochu.dmp tables= (table1) query= "where filed1 like ' 0% '"

The above is commonly used for the export, for compression, both with WinZip to the DMP file can be very good compression.
It can also be implemented by adding compress=y to the above command.

Import of data
1 Import the data from the D:DAOCHU.DMP into the test database.
Imp System/[email protected] File=d:daochu.dmp
Imp aichannel/[email protected] full=y file=d:datanewsmgnt.dmp ignore=y
There may be a problem, because some tables already exist, and then it is an error, and the table is not imported.
Add Ignore=y to the back.
2 Import the table table1 in D:daochu.dmp
Imp system/[email protected] file=d:daochu.dmp tables= (table1)

Basically, the above import and export is enough. In many cases, you must first delete the table and then import it.

Attention:
The operator must have sufficient permissions, and it will prompt for insufficient permissions.
Database, you can connect to the. You can use tnsping test to get the database test to connect.

Appendix I:
Adding permissions to the import data to the user
First, start sql*puls
Second, landing with System/manager
Third, create user username identified by password (this step can be omitted if the user has already been created)
IV, GRANT create User,drop user,alter USER, create any VIEW,
DROP any view,exp_full_database,imp_full_database,
Dba,connect,resource,create SESSION to User name
V, run-cmd-into the directory where the DMP file is located,
Imp Userid=system/manager full=y file=*.dmp
or imp Userid=system/manager full=y file=filename.dmp

Example execution:
F:workoracle_databackup>imp userid=test/test full=y file=inner_notify.dmp

Screen display
Import:release 8.1.7.0.0-production on Thu February 16 16:50:05 2006
(c) Copyright: Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0-production
With the partitioning option
Jserver Release 8.1.7.0.0-production

Export files created by export:v08.01.07 through a regular path
Import is completed in ZHS16GBK character set and ZHS16GBK NCHAR character set
The export server uses the UTF8 NCHAR character set (possible ncharset conversions)
. Importing Aichannel objects into Aichannel
. . Importing table "Inner_notify" 4 rows are imported
Prepare to enable constraints ...
The import was terminated successfully, but a warning appears.


Appendix II:
Oracle does not allow direct changes to the table owner, which can be achieved with export/import.
Build Import9.par First,
Then, use the command as follows: Imp Parfile=/filepath/import9.par
Example Import9.par content is as follows:
Fromuser=tgpms
TOUSER=TGPMS2 (Note: Users who change the owner of the table from Fromuser to Touser,fromuser and Touser can be different)
Rows=y
Indexes=y
Grants=y
Constraints=y
buffer=409600
File==/backup/ctgpc_20030623.dmp
Log==/backup/import_20030623.log

Oracle database Installation and configuration (i)