1. 用户创建/删除
2. 用户修改密码/解锁
3. dmp导入/导出
4. 数据字典-常用查询
5. insert语句--子查询
6. 删除重复记录
7. 查看数据库的连接的实例名
8. 已使用 TNSNAMES 适配器来解析别名
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>