oracle常用SQL语句

学习链接
http://zhaonjtu.iteye.com/category/22029
http://pub.itpub.net/category/18452/42386/
http://www.iteye.com/topic/805815
http://dbajun.iteye.com/blog/135331

一 删除表中重复的数据
1  引用:http://java.mblogger.cn/layout/posts/10525.aspx
  

   selete from a a where a.rowid!=(select max(rowid) from a b 
   where a.bm=b.bm and a.mc=b.mc);
   

2 引用: http://www.cnd8.com/news/news/30539.htm
 

  create table 临时表 as(select distinct * from 正式表);
  truncate table 正式表;
  insert into 正式表 (select * from 临时表);
  drop table 临时表;
  commit;
  

二 表,列相关
--0 创建表
-- 删除表: drop table david;

create table DAVID
(
  ID   VARCHAR2(40),
  NAME VARCHAR2(40)
)

-- 1 修改表空间
--语法 alter   table  tablename move tablespace tablespacename;

alter   table david  move  tablespace  mytest;

--2 修改列名
--语法 alter table tablename  rename column old_columnname to newcolumnname;

alter table david rename column name to david_name;

--3 增加列
--,修改,删除
--语法 alter table tablename add (column datatype [default value][null/not null],….);

alter table david add (age VARCHAR2(1) default 1 not null);

--4 修改列
--语法 alter table tablename modify (column datatype [default value][null/not null],….);

alter table david modify(age number default 0); 

--5 删除列
--语法 alter table tablename drop (column);

alter table david drop (age);

--6 修改列属性
--语法 alter table 表名 modify （字段名 新属性）

alter table david modify (name char(10));

--7 修改列的长度
--语法 alter table v_table modify (v_field varchar(10),...)

alter table david modify (name char(20));

--8 修改列默认值
--语法 alter table table modify (columnname default 1);

alter table david modify (name default 'david jack');

--9 给表加注释
--语法  comment on table [tablespace.]tablename  IS '注释内容';

comment on table david is '这是测试表2'

--

comment on table mytest.david is '这是测试表2'

--10 修改表的列的注释
--语法 comment on column  表名.列名 is '注释内容';

comment on column david.name is '姓名'

--11 设置某一个字段为无用状态
--语法 alter table 表名 set unuesed column 字段名
--说明 set unused不会真地删除字段。清楚掉字典信息（撤消存储空间），不可恢复

alter table david set unused column name;

--12 添加主键
--语法 alert table 表名 add constraint pkKey primary key（字段列表）

alter table david add constraint pkkey primary key(id);

--13 修改表名
--语法 alter table  原表名 rename  to  新表名;

alter table  david rename  to TEST1;

14 获取表的列名称

select column_name from dba_tab_columns where owner='用户名' and table_name='表名'

15 查看当前用户的缺省表空间

select username,default_tablespace from user_users;

16 查看当前用户的角色

select * from user_role_privs

17 查看当前用户的系统权限和表级权限

select * from user_sys_privs;
select * from user_tab_privs;

18 查看用户下所有的表

select * from user_tables;

19 查看名称包含log字符的表

select object_name,object_id from user_objects where instr(object_name,'LOG')>0;

20 查看序列号，last_number是当前值

select * from user_sequences

21 查看函数和过程的状态

select object_name,status from user_objects where object_type='FUNCTION';
select object_name,status from user_objects where object_type='PROCEDURE';

22 查看函数和过程的源代码

select text from all_source where owner=user and name=upper('&plsql_name');

23 查看表空间使用率

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1

以下为转载：http://dbajun.iteye.com/blog/135331
1.查看所有用户：

 select * from dba_users;
  select * from all_users;
  select * from user_users;

2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：

 select * from dba_sys_privs;
  select * from user_sys_privs;

3.查看角色(只能查看登陆用户拥有的角色)所包含的权限

select * from role_sys_privs;

4.查看用户对象权限：
 

select * from dba_tab_privs;
  select * from all_tab_privs;
  select * from user_tab_privs;

5.查看所有角色：

 select * from dba_roles;

6.查看用户或角色所拥有的角色：
 

select * from dba_role_privs;
  select * from user_role_privs;

7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

select * from V$PWFILE_USERS

注意：
1、以下语句可以查看Oracle提供的系统权限

select name from sys.system_privilege_map

2、查看一个用户的所有系统权限(包含角色的系统权限)

    select privilege from dba_sys_privs where grantee='DATAUSER'  
    union  
    select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='DATAUSER' );