实用 SQL 命令

1. 登陆数据库

sqlplus /nolog
conn heatmeter/heatmeter@orcl
conn sys/orcl as sysdba

2. 查看数据库名称

select name from v$database;

3. 数据库导入导出

exp water/water@orcl file=c:\water.dmp owner=(water);

imp water/water@orcl file=c:\waterexp.dmp full=y;

imp water/water@orcl file=c:\waterexp.dmp table=(tabale1) full=y;

4. 删除某个用户的所有表

drop user water cascade;

5. 断开某用户的所有连接

select username,sid,serial# from v$session where username='WATER10';
alter system kill session'22,1';
drop user water320000 cascade;

6. 查询用户记录最多的10条

select rownum, b.*
  from (select d.client_id, count(d.client_id) cnt
          from detail d
         group by client_id
         order by cnt desc) b
 where rownum <= 10

 7. 分析重复数据的个数

select * from (
select client_id,submeter,count(*) cnt from meterhx group by client_id,submeter
) where cnt > 1