Some stuff commonly used in Oracle daily patrol and maintenance

Import and export operations for tables in the Oracle database:

Take the database under Windows as an example (in cmd mode):

To export a table:

1. Export the entire database

Exp Guide table username/password @ instance name file= ' E:\xxx.dmp ' full=y

2. Export single or multi-table

Exp Guide table username/password @ instance name file= ' E:\xxx.dmp ' tables=t1[(T1,T2,T3)]

3. Export a table under one or more users in the database

Exp Guide table username/password @ instance name file= ' E:\xxx.dmp ' owner= (System,sys)

4. Export the field filed1 in table table1 in the database with data beginning with "00"

Exp Guide table username/password @ instance name file= ' E:\xxx.dmp ' tables= (table1) query=\ "where filed1 like ' 0% ' \"

------------------------------------------

Import Table:

Import the data from the E:\XXX.DMP into a database.

IMP Guide table username/password @ instance name File=e:\xxx.dmp

IMP Guide table username/password @ instance name full=y file=e:\xxx.dmp ignore=y

Add Ignore=y in the back to ignore the import error, import directly.

2 Import the table table1 in E:\xxx.dmp

IMP Guide table username/password @ instance name file=e:\xxx.dmp tables= (table1)

----------------------------

Linux Direct Exp,imp According to the prompt operation, the effect is the same. If you pass the statement, you can now write a direct copy of the Emedit on the paste.

---------------------------

View version in database:

SELECT * from V$version;

View Oracle version information under Linux:

File $ORACLE _home/bin/oracle

-------------------------

Database server View Character set:

SELECT * from Nls_database_parameters;

Where Nls_characterset the following is the character set of the database server

-------------------------

View table space XXX usage:

Select/*+ ordered Use_merge (A, b) */

A.tablespace_name table space Name,

total/(1024*1024) Table space Size,

(Total-free)/(1024*1024) Table space usage size,

free/(1024*1024) Table space remaining size,

Round ((total-free)/total,4) *100 "Utilization%"

From (select Tablespace_name,sum (bytes) free from Dba_free_space

Group by Tablespace_name) A,

(select Tablespace_name,sum (bytes) Total from Dba_data_files

Group BY Tablespace_name) b

where a.tablespace_name = B.tablespace_name

and a.tablespace_name = ' xxx ';

-------------------------

To view the permissions that the current role XXX has:

SELECT * from Dba_sys_privs where grantee= ' XXX ';

-------------------------

View the table space quotas for user xxx. (-1 is unrestricted)

Select Tablespace_name,username,max_bytes from Dba_ts_quotas where username= ' XXX ';

-------------------------

Set the user MC's tablespace quota limit to 100M:

Alter user MC quota 100M on Tablespacname;

-------------------------

Set the table space quota for the user MC to unlimited:

ALTER user MC Quota unlimited on tablespacname;

-------------------------

Give the user MC the ability to configure an unlimited amount of table space:

Grant Unlimited tablespace to MC;

-------------------------

This article is from the "Knife Bao" blog, please be sure to keep this source http://mcluan.blog.51cto.com/11989648/1896263

Some stuff commonly used in Oracle daily patrol and maintenance