Oralce常用SQL指令集

转：http://www.javaeye.com/topic/431907

 

一、系统的默认用户 

 

1. sys;//系统管理员，拥有高权限     
2. system;//本地管理员，次高权限     
3. scott;//普通用户，密码默认为tiger,默认未解锁   

二、登陆 

1. sqlplus / as sysdba;//登陆sys帐户     
2. sqlplus sys as sysdba;//同上     
3. sqlplus scott/tiger;//登陆普通用户scott  

三、管理用户 

1. create user zhangsan;//在管理员帐户下，创建用户zhangsan     
2. alert user scott identified by tiger;//修改密码  

四，授予权限 
1、默认的普通用户scott默认未解锁，不能进行那个使用，新建的用户也没有任何权限，必须授予权限 

1. /*管理员授权*/    
2. grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限     
3. grant unlimited session to zhangsan;//授予zhangsan用户使用表空间的权限     
4. grant create table to zhangsan;//授予创建表的权限     
5. grante drop table to zhangsan;//授予删除表的权限     
6. grant insert table to zhangsan;//插入表的权限     
7. grant update table to zhangsan;//修改表的权限     
8. grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)    

2、oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的，需要互相授权 

1. /*oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的*/    
2. grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限     
3. grant drop on tablename to zhangsan;//授予删除表的权限     
4. grant insert on tablename to zhangsan;//授予插入的权限     
5. grant update on tablename to zhangsan;//授予修改表的权限     
6. grant insert(id) on tablename to zhangsan;     
7. grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update     
8. grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限    

五、撤销权限 

1. 基本语法同grant,关键字为revoke  

六、查看权限 

1. select * from user_sys_privs;//查看当前用户所有权限     
2. select * from user_tab_privs;//查看所用用户对表的权限    

七、操作表的用户的表 

1. /*需要在表名前加上用户名，如下*/    
2. select * from zhangsan.tablename    

八、权限传递 
即用户A将权限授予B，B可以将操作的权限再授予C，命令如下： 

1. grant alert table on tablename to zhangsan with admin option;//关键字 with admin option     
2. grant alert table on tablename to zhangsan with grant option;//关键字 with grant option效果和admin类似    

九、角色 
角色即权限的集合，可以把一个角色授予给用户 

1. create role myrole;//创建角色     
2. grant create session to myrole;//将创建session的权限授予myrole     
3. grant myrole to zhangsan;//授予zhangsan用户myrole的角色      
4. drop role myrole;删除角色     
5. /*但是有些权限是不能授予给角色的，比如unlimited tablespace和any关键字*/    

 

 

 

 

查找数据库中所有字段  以对应的表 
select C.column_name,C.TABLE_NAME from dba_tab_columns C where owner=‘’

查每个科目class 分数scro前三名 
select id, name, class, scro 
   from (select row_number() over(partition by class order by scro desc)  cnt, 
                id, 
                name, 
                class, 
                scro 
           from student) a 
  where a.cnt <= 3; 

查找排序后的前三行 
  select * 
  from (select rw.*, rownum 
          from (select * 
                  from student d 
                 where d.class = 'b' 
                 order by d.scro desc) rw 
         where rw.id >= 1 
         order by rw.class desc) n 
where rownum <= 3 


表复制 
insert into table_a (id,name,age) select b.id,b.name,b.age from table_b; 

--删除表数据的触发器 
CREATE OR REPLACE PROCEDURE delete_data 
IS 
BEGIN 
   delete from test ; 
   COMMIT; 
EXCEPTION 
   WHEN NO_DATA_FOUND 
   THEN 
      NULL; 
   WHEN OTHERS 
   THEN 
      RAISE; 
END delete_data; 


--定时删除  每隔5分钟执行一次的计划 
DECLARE 
  X NUMBER; 
BEGIN 
  SYS.DBMS_JOB.SUBMIT 
  ( job       => X 
   ,what      => 'delete_data;' 
   ,next_date => to_date('25/08/2008 00:00:00','dd/mm/yyyy hh24:mi:ss') 
   ,interval  => 'sysdate+1/24/12' 
   ,no_parse  => FALSE 
  ); 
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); 
COMMIT; 
END; 

--查看当前oracle计划 
select job,next_date,next_sec,failures,broken from user_jobs; 

--删除一个job 
begin 
dbms_job.remove(46);--46为job号 
end; 

--给新表插入旧表对应字段的所有数据 
insert into aaa(id,name) select b.id,b.name from bbb; 


文章搜索：     【点击打包该文章】 

select * from v$version; 查看服务器版本
select * from all_users;                ##查看所有用户 
select name from v$database;             ##查看当前数据库 
database test;                    ##进入test数据库 
select * from v$instance;                ##查看所有的数据库实例 
shutdown immediate                ##关闭数据库 
alter user sys identified by new_password;        ##更改用户密码 
select username,password from dba_users; ##查看当实例中的用户和密码 
show parameter control_files;           ## 查看控制文件; 
select member from v$logfile;        ##查看日志文件 
show parameter ;                 ## 查看数据库参数 
select * from user_role_privs;        ##查看当前用户的角色 
select username,default_tablespace from user_users; ##查看当前用户的缺省表空间 
alter user system identified by [password]        ##修改用户的密码 
ALTER USER "SCOTT" ACCOUNT UNLOCK                 ##解锁SCOTT用户 
show parameter processes;                    ##查看最大会话数 

 select username,sid,serial# from v$session;  查看当前会话连接



select * from all_tables;  ##查看当前库的所有数据表
 

查看表结构：desc all_tables; 

创建用户并赋予权限 
###----------------------------创建用户并赋予权限------------------------------------####- 
create user mpss 
  identified by "mpss12" 
  default tablespace TS_MPSS_DATA 
  temporary tablespace TEMP; 
  
  给用户赋予权限 
grant connect to mpss; 
grant resource,create session to mpss;  开发角色 
grant create procedure to dbuser； ＃这些权限足够用于开发及生产环境 



给用户授权 
grant dba to spms;--授予DBA权限 
grant unlimited tablespace to lxg;--授予不限制的表空间 
grant select any table to lxg;--授予查询任何表 
grant select any dictionary to lxg;--授予 查询 任何字典 

删除用户 
drop user mpss cascade; 


建表空间 

CREATE TABLESPACE "TS_MPSS_DATA" 
    LOGGING 
    DATAFILE '/mpss/data/ts_mpss_data.dbf' SIZE 1024M 
    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO 

建立临时表空间

CREATE 
    TEMPORARY TABLESPACE "SWVIP" TEMPFILE '/app/oracle/oradata/ 
    sworacle/SWVIP.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM 
    SIZE 1M 



查看表空间 
###----------------------------查看表空间大小------------------------------------####- 
SELECT D.TABLESPACE_NAME "Name", 
TO_CHAR(((((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024)) /(A.BYTES / 1024 / 1024))*100,'99,990.9')  "used(%)", 
TO_CHAR((DECODE(F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024),'999,990.9') "Free (M)" 
FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F 
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME 
AND F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME; 
###--------------------------------------------------------------------------------------####- 

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", 
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)" 
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS 
FROM DBA_DATA_FILES 
GROUP BY TABLESPACE_NAME) D, 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE 
FROM DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME) F 
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
UNION ALL --if have tempfile 
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)", 
NVL(FREE_SPACE,0) "FREE_SPACE(M)" 
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS 
FROM DBA_TEMP_FILES 
GROUP BY TABLESPACE_NAME) D, 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE, 
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE 
FROM V$TEMP_SPACE_HEADER 
GROUP BY TABLESPACE_NAME) F 
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ; 



  查看表空间物理文件的名称及大小; 
  
###--------------------表空间物理文件的名称及大小------------------------####- 
  select tablespace_name, file_id, file_name, 
    round(bytes/(1024*1024),0) total_space 
    from dba_data_files 
    order by tablespace_name; 
###------------------------------------------------------------------------------------####-   
    

查看数据文件放置的路径 
###------------------------------------------------------------------------------------####-   

SQL> col file_name format a50 
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; 
###------------------------------------------------------------------------------------####-   


 查看数据库库对象 

   select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status; 


扩表空间 
###------------------------------------------------------------------------------------####- 
alter tablespace G000 add datafile '/dev/vgbilling/rg000_lv03' SIZE 7500m; 
给表G000增加一个7500m的逻辑卷'/dev/vgbilling/rg000_lv03' 
###------------------------------------------------------------------------------------####- 

检查被长时间锁的对象 
###------------------------------------------------------------------------------------####- 

SQL>select a.session_id,a.process,a.locked_mode,b.object_name,b.object_type,b.status from v$locked_object a,dba_objects b where a.object_id=b.object_id; 
###------------------------------------------------------------------------------------####- 

--创建视图给hy用户（用leon用户） 

 

create or replace view view_tableName as 

select column。。。 from table; 

 

-- Grant/Revoke object privileges 

grant select, update on RES_XIM_CARD to hy; 

 

--创建同义词 

create synonym RES_XIM_CARD for YY.RES_XIM_CARD ; 

sys用户登陆 
创建表空间： 
SQL> create tablespace lmsstemp datafile 'F:/ORADATA/LMSS/LMSSTEMP01.DBF' SIZE 1 
024M extent management local; 

给表空间增加数据文件 
alter tablespace lmsstemp add datafile 'F:/ORADATA/LMSS/LMSSTEMP02.DBF' SIZE 1 
024M; 

更改表空间为自动扩展 
SQL> alter database datafile 'F:/ORADATA/LMSS/LMSSTEMP01.DBF' autoextend on; 

查看表空间信息 
SQL> select file_name,tablespace_name,autoextensible from dba_data_files;