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

Source: Internet
Author: User


    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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.