Create Oracle Tablespace under Linux

1, Login server

2. See if the disk space is big enough df-h

-H more Current disk space and usage to be displayed in a more readable way

[Root@rac1 ~]# 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 in the radical, 1000 instead of 1024 is used for capacity conversion

[Root@rac1 ~]# 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

[Root@rac1 ~]# Df-k

FileSystem 1k-blocks Used Available use% mounted on

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

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

Cd/home/oracle/oradata

MKDIR ABC

4, sqlplus "/As SYSDBA" (DBA Login to the database, the system's super users)

5, create temporary table space

Before you create a user, you must create a temporary tablespace and a database table space of two tablespaces, otherwise the system default table space 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 temporary table space name

2/home/oracle/oradata/abc Storage of database files, is usually installed after the database has control files, data files and log files folder, plus to create the table space name +dbf (data file)

3 The initial size of the 1024m tablespace

4 100m table space automatic growth size

5 10240m table Space Maximum size

6, create the 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 table creation

8, give the user the right to grant

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

9, delete users and all the users of the object

Drop user ZFMI cascade;

The cascade parameter is a cascading deletion of all objects of the user, often encountering problems that users cannot delete if they have an object without this parameter, so it is customary to add this parameter

Delete Oracle User NIM The following error occurred:

sql> drop user Nim cascade;

Drop User Nim Cascade

* ERROR is on line 1th:

ORA-00604: Recursive SQL Layer 1 error occurred

ORA-24005: You must use DBMS_AQADM. Drop_queue_table Delete Team List

Treatment mode:

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 the table space
Prerequisite: Before deleting a tablespace, make sure that the tablespace is not used by another user before deleting it

Drop tablespace Nimeng including contents and datafiles cascade;

Including contents delete the contents of the tablespace, if there is content in the tablespace before the table space is deleted, but not with this parameter, the table space is not deleted, so it is customary to add this parameter
including datafiles delete data files in a tablespace
Cascade constraints Delete foreign key references for tables in tablespace

If you delete the data file for the table space before you clear the table space, the database will not start and shut down properly. You can recover by using the following methods:
In the following procedure, filename is a data file that has been deleted, and if there are more than one, it needs to be executed multiple times; Tablespace_name is the name of the corresponding table space.
$ sqlplus/nolog
Sql> Conn/as sysdba;
If the database is already started, you need to first execute the following line:
sql> Shutdown Abort
Sql> Startup Mount
sql> ALTER DATABASE datafile ' filename ' offline drop;
sql> ALTER DATABASE open;
Sql> drop tablespace tablespace_name including contents;

11. Export/Import

Export
1 The database TEST completely exported, user Name System Password Manager exported to D:daochu.dmp
Exp system/manager@test file=d:/daochu.dmp full=y
2 Export the table of the system user and the SYS user in the database
Exp system/manager@test file=d:/daochu.dmp owner= (System,sys)
3 Export the table inner_notify and Notify_staff_relat in the database
Exp AICHANNEL/AICHANNEL@TESTDB2 file= D:/datanewsmgnt.dmp tables= (Inner_notify,notify_staff_relat)

4 Export the fields in the table table1 in the database filed1 with the data beginning with "00"
Exp system/manager@test file=d:daochu.dmp tables= (table1) query= "where filed1 like ' 0% '"

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

Import

1 Import the data from the D:DAOCHU.DMP into the TEST database.

Imp system/manager@test file=d:daochu.dmp
Imp aichannel/aichannel@hust full=y file=d:datanewsmgnt.dmp ignore=y
There may be something wrong with it, because some tables already exist, and then it complains, and the table is not imported. Just add ignore=y to the back.

2 Import the table Table1 in D:daochu.dmp into imp system/manager@test file=d:daochu.dmp tables= (table1)

Basically, the import export above is sufficient. In many cases, you should first delete the table completely and then import it.

Attention:
The operator must have sufficient permissions, and the permission is not sufficient for it to prompt.
Database can be connected to. You can use tnsping test to get the database test to connect.

12, to the user to increase the import of data rights operations

1) Start Sql*puls
2) to System/manager Landing
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

The following are used by personal creation:

Create temporary tablespace shiyu_temp tempfile '/u01/app/oracle/oradata/orcl/shiyu_temp.dbf ' size 50m autoextend on Next 50m maxsize 20480m extent management local;
Create tablespace shiyu logging datafile '/u01/app/oracle/oradata/orcl/shiyu.dbf ' size 1024m autoextend on next 100m m Axsize 10240m extent management local;
Create user Shiyu identified by shiyu_com default tablespace shiyu temporary tablespace;
Grant DBA to Shiyu;