Create Oracle Tablespace under Linux

From: http://blog.sina.com.cn/s/blog_62192aed01018aep.html

1. Login Server

2. See if disk space is large enough df-h

-H more Current disk space and usage to display in a more readable manner

[Email protected] ~]# df-h

Filesystem Size used Avail use% mounted on

/DEV/SDA1 2.9G 2.3G 521M 82%/

None 209M 0 209M 0%/dev/shm

/dev/sda2 4.5G 1.8G 2.5G 42%/u01

/dev/sde1 512M 80M 433M 16%/OCFS

The-h parameter is the same on the-H root, but at the time of the radical conversion, the capacity is converted by 1000 instead of 1024.

[Email protected] ~]# df-h

Filesystem Size used Avail use% mounted on

/DEV/SDA1 3.1G 2.4G 546M 82%/

None 219M 0 219M 0%/dev/shm

/dev/sda2 4.8G 1.9G 2.7G 42%/u01

/dev/sde1 537M 84M 454M 16%/OCFS

-K displays disk usage in units

[Email protected] ~]# df-k

Filesystem 1k-blocks used Available use% mounted on

su-oracle Switch to Oracle User (a user name for Linux)

3 , create a new folder in the/home/oracle/oradata directory, the subsequent creation of table space needs to use

Cd/home/oracle/oradata

MKDIR ABC

4 , sqlplus "/As SYSDBA" (log in as DBA, Super User of System)

5 . Create a temporary table space

Before you create a user, you must first create a temporary tablespace and two tablespace for the database tablespace, otherwise the default tablespace is not good.

Create temporary tablespace abc_temp tempfile '/home/oracle/oradata/abc/abc_temp.dbf ' size 1024m autoextend on next 100m m Axsize 10240m extent management local;

Description

1) abc_temp temp Table space Name

2)/home/oracle/oradata/abc the place where the database files are stored, usually after the database is installed, there are control files, data files and log files in the folder, plus the name of the table space to create +DBF (data file)

3) Initial size of 1024m table space

4) Auto-grow size of 100m table space

5) Maximum size of 10240m table space

6 . Create a data table space

Create tablespace ABC logging datafile '/home/oracle/oradata/abc/abc.dbf ' size 1024m autoextend on next 100m maxsize 10240m Extent management Local;

7 . Create user and specify table space

Create user ABC identified by ABC default tablespace ABC temporary tablespace abc_temp;

Note: Create Standardtable.sql creates a table

8 . Grant permissions to users

Grant DBA to ABC; (All DBA permissions are granted to ABC users)

9 . Delete the user and all the objects of the user

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

The following error occurred while removing the Oracle user nim:

sql> drop user Nim cascade;

Drop User Nim Cascade

* ERROR on line 1th:

ORA-00604: A recursive SQL Layer 1 error occurred

ORA-24005: You must use DBMS_AQADM. Drop_queue_table Delete a team list

Processing method:

Execute this statement first: Alter session set events ' 10851 Trace name context Forever,level 2 ';

Re-execution: drop user NIM cascade; Delete User Nim

10. 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 Nimeng including contents and datafiles cascade constraints;

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 the data file for a tablespace before you clear the tablespace, the database will not start and close properly. You can use the following methods to recover:
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;

one , export/import

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) Export the system user in the database and the SYS user's table
Exp System/[email Protected] File=d:/daochu.dmp owner= (System,sys)
3) Export the tables in the database inner_notify, Notify_staff_relat
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

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 from d:daochu.dmp to 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.

Add the Import data permission to the user

1) Start Sql*puls
2) Login with System/manager
3) Create user username identified by password

4) 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

5) Enter 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

Create Oracle Tablespace under Linux