自己总结的一些用过的sql语句

时间:2015-02-11 19:34 来源:linux.it.net.cn 作者:IT

1 查看用户的状态，默认表空间、临时表空间

select u.username,u.account_status,u.default_tablespace,u.temporary_tablespace from dba_users u;

2 给账户scott解锁并且设置密码为root

alter user scott account unlock;

alter user scott identified by root ;

3 查看用户的表

select * from user_tables;

4 查看数据的的编码
select * from v$nls_parameters
select * from v$nls_parameters where parameter='NLS_CHARACTERSET';
select value from nls_database_parameters t where t.PARAMETER='NLS_NCHAR_CHARACTERSET'

5 设置exp命令导出编码
export NLS_LANG=AMERICAN_AMERICA.UTF8

6 删除用户以及用户的对象

drop user scott cascade;

7 授权给用户
GRANT IMP_FULL_DATABASE to "SCOTT"
GRANT DBA TO "SCOTT";

8 收回权限

revoke dba from "SCOTT";

9 创建directory目录并给用户授予权限

create directory backup as "/backup"

grant read,write on directory backup to system;

10 创建dblink

create database link toorcl
connect to scott identified
by root using 'orcl'

11 impdp 导入命令

impdp system/root dumpfile=scott.dump logfile=scott2.log full=y

12 expdp 导出命令

expdp parfile=scott.par

userid=system/SCOTT@to_gpecdev
directory=backup
dumpfile=scott.dump
logfile=scott.log
schemas=scott

13 授予权限
GRANT UNLIMITED TABLESPACE TO "SCOTT";
GRANT CREATE DATABASE LINK TO "SCOTT";
GRANT CREATE ANY TABLE TO "SCOTT";
GRANT CREATE ANY TRIGGER TO "SCOTT";
GRANT CREATE ANY VIEW TO "SCOTT";
GRANT CREATE DATABASE LINK TO "SCOTT";
GRANT CREATE PROCEDURE TO "SCOTT";
GRANT CREATE TABLE TO "SCOTT";
GRANT CREATE TABLESPACE TO "SCOTT";
GRANT CREATE TRIGGER TO "SCOTT";
GRANT DROP ANY TABLE TO "SCOTT";
GRANT INSERT ANY TABLE TO "SCOTT";
GRANT UPDATE ANY TABLE TO "SCOTT";
GRANT CONNECT TO "SCOTT";
GRANT RESOURCE TO "SCOTT";
GRANT IMP_FULL_DATABASE to "SCOTT"
GRANT DBA TO "SCOTT";
(责任编辑：IT)