oracle 常用操作/常用查询

1. 用户创建/删除

create user ims identified by huawei default tablespace users;
grant dba to ims;
drop user ims cascade;

2. 用户修改密码/解锁

alter user scott account unlock identified by tiger;

3. dmp导入/导出

--导出数据库的全部数据
exp ims/huawei@IMSSPG file=/home/oracle4spg/all.dmp full=y
--导出指定用户的数据
exp ims/huawei@IMSSPG file=/home/oracle4spg/ims_scott.dmp owner=ims,scott
--导出指定的表
exp ims/huawei@imsspg file=/home/oracle4spg/tables.dmp tables=ims.ims_business_type

--导入全部数据
imp ims/huawei@IMSSPG file=/home/oracle4spg/all.dmp full=y
--导入指定的表
imp ims/huawei@IMSSPG file=/home/oracle4spg/all.dmp tables=ims.ims_business_type
--导入指定的用户
imp ims/huawei@IMSSPG file=/home/oracle4spg/all.dmp fromuser=ims,scott
--将一个用户所属的数据导入另一个用户
imp ims/huawei@IMSSPG file=/home/oracle4spg/all.dmp fromuser=ims touser=scott

4. 数据字典-常用查询

select username from all_users;--查询所有用户
select * from user_role_privs;--查询当前用户角色
select table_name from user_tables;--查询用户的表
select table_name from all_tables;--查询用户可访问的表

5. insert语句--子查询

insert into dept(id,name) select deptno, dname from dept;

6. 删除重复记录

delete from emp2 t1 where exists (select * from emp2 t2 where t1.id>t2.id and t1.ename=t2.ename);

7. 查看数据库的连接的实例名

select * from  v$instance;

8. 已使用 TNSNAMES 适配器来解析别名

oracle4spg@omu10:/root> tnsping imsspg

TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 19-SEP-2012 06:58:10

Copyright (c) 1997, 2007, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle4spg/oradb/11g/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (address=(protocol=tcp)(host=172.40.128.88)(port=1531)) (LOAD_BALANCE = no) (FAILOVER = yes)) (CONNECT_DATA = (SERVICE_NAME = IMSSPG)))
OK (0 msec)
oracle4spg@omu10:/root>