Oraclle Database Build table space and build user, delete table space

1. Oracle creates table spaces
   

Note the point:

1. If it is opened in a tool such as PL/SQL, directly modify the following code [ Italic bold Section ] to execute

2. Ensure that the path exists, such as "D:\oracle\oradata\Oracle9i\", which is the path where you want to save the file

/* divided into four steps */

/* Step 1th: Create a temporary tablespace */

Create temporary tablespace user_temp

Tempfile ' D:\oracle\oradata\Oracle9i\user_temp.dbf '

Size 50m

Autoextend on

Next 50m maxsize 20480m

Extent management Local;

/* Step 2nd: Create a data table space */

Create tablespace user_data

Logging

datafile ' D:\oracle\oradata\Oracle9i\user_data.dbf '

Size 50m

Autoextend on

Next 50m maxsize 20480m

Extent management Local;

/* Step 3rd: Create the user and specify the tablespace username to the user name you want to create, password the required password */

Create user username identified by password

Default tablespace user_data

Temporary tablespace user_temp;

/* Step 4th: Grant permissions to users */

Grant CONNECT,RESOURCE,DBA to username;

2. Delete table spaces and users
You can offline it first
Alter tablespace XX offline;
Delete the data files on the disk together
Drop tablespace xxx including contents and datafiles;

To delete a user:
Drop User xxx;
If the user's schema has objects, need to add cascade parameter, that is, drop user xxx cascade;

3. Import and Export data
Import of data:

(1) Import the data from the D:\DAOCHU.DMP into the test database.
Imp System/[email protected] File=d:\daochu.dmp
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)

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) 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 table table1, table2 in the database
Exp System/[email protected] file=d:\daochu.dmp tables= (table1,table2)
(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% ' \"
Note: The above is commonly used for export, for compression I do not care, with WinZip to the DMP file can be very good compression.
But add compress=y to the upper order.

Oraclle Database Build table space and build user, delete table space