数据库-oracle常用SQL语句整理（持续更新中）

--查询uuid
SELECT sys_guid() FROM dual;

--查询文件长度函数DBMS_LOB.GETLENGTH
SELECT DBMS_LOB.GETLENGTH(F."FILE") FROM T_EXPERT_FILE F WHERE ...

SELECT DBMS_LOB.GETLENGTH(F.filess) FROM T_EXPERT_FILE F WHERE ...

--复制表
create table newtable as select * from oldname where ...

--查询用户
SELECT * FROM all_users;

--查询函数
select * from dba_objects where object_type='FUNCTION';

--给某用户解锁
alter user cpist account unlock;

--查看job
SELECT * FROM Dba_Jobs;
SELECT * FROM dba_jobs WHERE log_user='CPIDBS';

--查看字符集
select userenv('language') from dual;  --SIMPLIFIED CHINESE_CHINA.ZHS16GBK;

--查询某个表所有的触发器
select   *   from   all_triggers where   table_name   =upper( 'chepb') AND owner=upper('cpixs');

--查询所有的存储过程
select * from user_objects where object_type='PROCEDURE';-- 一定要大写

--修改表名
alter table tableName_old rename to tableName_new

--添加列
alter table tableName add colName 字段类型

--修改列名
alter table taleName rename column colName to newColName

--修改列类型
alter table tableName modify colName 字段类型

--替换
从a中找到b,替换成c,若没有c表示从a中删掉b
SELECT REPLACE('a','b','c') FROM dual;

--表连接
select * from tableA cross join tableB;

--闪回
select * from meikxxb
--闪回过程
1，alter table meikxxb enable row movement;
2，flashback table meikxxb to timestamp to_timestamp ('2010-10-13 10:00:00','yyyy-mm-dd hh24:mi:ss');
3，alter table meikxxb disable row movement;

 

2014年07月31日更新

oracle用户的权限管理
-------------sys Login------------
1. 创建表空间及临时表空间
create tablespace csdn1 datafile 'csdn1' size 30m autoextend on;
create temporary tablespace csdn2 tempfile 'csdn2' size 30m autoextend on;

 

2. 创建用户指定表空间及临时表空间
create user csdn identified by csdn default tablespace csdn1 temporary tablespace csdn2;

 

3. 授予用户各种权利
grant create session to csdn;
grant unlimited tablespace to csdn;
grant connect to csdn;

grant resource to csdn;
grant create sequence to csdn;
grant create table to csdn;

 

4. 查询当前用户的权限
select * from user_sys_privs;

 

5. 撤销用户各种权限
revoke create table from csdn;
revoke create session from csdn;revoke create sequence to csdn;
revoke resource to csdn;revoke connect to csdn;
revoke unlimited tablespace to csdn;

 

6.通过角色来赋予用户各种权限
create user root identified by root default tablespace csdn1 temporary tablespace csdn2;
create role role1;grant create table to role1;
grant create session to role1;
grant connect to role1;grant resource to role1;