oracle相关sql汇总

1. --oracle 数据库相关操作整理
   --一  关于expdp和impdp     
   --使用EXPDP和IMPDP时应该注意的事项：
   --EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。
   --EXPDP和IMPDP是服务端的工具程序，只能在ORACLE服务端使用，不能在客户端使用。
   --IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于EXPDP导出的文件，而不适用于EXP导出文件。
   --expdp或impdp命令时，可暂不指出用户名/密码@实例名 as 身份，然后根据提示再输入，如：
      expdp schemas=myuser dumpfile=expdp.dmp DIRECTORY=dumpdir;
   --1、创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system/SYSDBA等管理员创建。
   create directory dumpdir as '/u01/app/oracle/admin/alsdevdb/dpdump/';
   --或这个使用原先存在的逻辑目录，使用这个语句查询
   select * from Dba_Directories;
   --2、查看管理理员目录（同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）
   select * from dba_directories;
   --3、给myuser用户赋予在指定目录的操作权限，最好以system/SYSDBA等管理员赋予。
   grant read,write on directory dumpdir to myuser;
   --二、导出数据
   --1)按用户导
   expdp myuser/myuser@orcl schemas=myuser dumpfile=expdp.dmp DIRECTORY=dumpdir;
   --2)并行进程parallel
   expdp myuser/myuser@orcl directory=dumpdir dumpfile=myuser1.dmp parallel=40 job_name=myuser1
   --3)按表名导
   expdp myuser/myuser@orcl TABLES=table1,table2 dumpfile=expdp.dmp DIRECTORY=dumpdir;
   --4)按查询条件导
   expdp myuser/myuser@orcl directory=dumpdir dumpfile=expdp.dmp Tables=table1 query='WHERE t_No=20';
   --5)按表空间导
   expdp system/manager DIRECTORY=dumpdir DUMPFILE=tablespace.dmp TABLESPACES=tablespace1,tablespace2;
   --6)导整个数据库
   expdp system/manager DIRECTORY=dumpdir DUMPFILE=full.dmp FULL=y;
   --三、还原数据
   --1)导到指定用户下
   impdp myuser/myuser DIRECTORY=dumpdir DUMPFILE=expdp.dmp SCHEMAS=myuser;
   --2)改变表的owner
   impdp newUser/newUser DIRECTORY=dumpdir DUMPFILE=expdp.dmp TABLES=myuser.dept REMAP_SCHEMA=myuser:newUser;
   --3)导入表空间
   impdp system/manager DIRECTORY=dumpdir DUMPFILE=tablespace.dmp TABLESPACES=tablespace1;
   --4)导入数据库
   impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
   --5)追加数据
   impdp system/manager DIRECTORY=dumpdir DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION
   
   --四 附加说明     
   --并行操作(PARALLEL) 
   --可以通过 PARALLEL 参数为导出使用一个以上的线程来显著地加速导出。每个线程创建一个单独的转储文件，
   --因此参数 dumpfile 应当拥有和并行度一样多的项目。您可以指定通配符作为文件名，而不是显式地输入各个文件名，例如： 
   expdp ananda/abc123 tables=CASES directory=dumpdir dumpfile=expCASES_%U.dmp parallel=4 job_name=Cases_Export 
   --注意:dumpfile 参数拥有一个通配符 %U，它指示文件将按需要创建，格式将为expCASES_nn.dmp，其中nn 从 01 开始，然后按需要向上增加。 
   --在并行模式下，状态屏幕将显示四个工作进程。（在默认模式下，只有一个进程是可见的）所有的工作进程同步取出数据，并在状态屏幕上显示它们的进度。 
   --分离访问数据文件和转储目录文件系统的输入/输出通道是很重要的。否则，与维护 Data Pump 作业相关的开销可能超过并行线程的效益，并因此而降低性能。
   --并行方式只有在表的数量多于并行值并且表很大时才是有效的。 
   --数据库监控 
   --还可以从数据库视图获得关于运行的 Data Pump 作业的更多信息。监控作业的主视图是 DBA_DATAPUMP_JOBS，
   --它将告诉您在作业上有多少个工作进程（列 DEGREE）在工作。 
   --另一个重要的视图是 DBA_DATAPUMP_SESSIONS，当它与上述视图和 V$SESSION 结合时将给出主前台进程的会话 SID。 
   select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr; 
   --这条指令显示前台进程的会话。更多有用的信息可以从警报日志中获得。当进程启动时，MCP 和工作进程在警报日志中显示如下： 
   kupprdp:master process DM00 started with pid=23, OS id=20530 to execute - SYS.KUPM$MCP.MAIN('CASES_EXPORT', 'ANANDA'); kupprdp:worker process DW01 started with worker id=1, pid=24, OS id=20532 to execute - SYS.KUPW$WORKER.MAIN('CASES_EXPORT', 'ANANDA'); kupprdp:worker process DW03 started with worker id=2, pid=25, OS id=20534 to execute - SYS.KUPW$WORKER.MAIN('CASES_EXPORT', 'ANANDA'); 
   --它显示为数据泵操作启动的会话的 PID。您可以用以下查询找到实际的 SID： 
   select sid, program from v$session where paddr in (select addr from v$process where pid in (23,24,25)); 
   --PROGRAM 列将对应警报日志文件中的名称显示进程 DM select sid from v$px_session where qcsid = 23; 
   （为主进程）或 DW （为工作进程）。如果一个工作进程使用了并行查询，比如说 SID 23，您可以在视图 V$PX_SESSION 中看到它，并把它找出来。它将为您显示从 SID 23 代表的工作进程中运行的所有并行查询会话： 
   --从视图 V$SESSION_LONGOPS 中可以获得其它的有用信息来预测完成作业将花费的时间。 
   select sid, serial#, sofar, totalwork from v$session_longops where opname = 'CASES_EXPORT' and sofar != totalwork; 
   --列 totalwork 显示总工作量，该列的 sofar 数量被加和到当前的时刻 — 因而您可以用它来估计还要花多长时间。 
   --五 oracle 10g 和11g的互相导入和导出    1) 可以用10g的client连接11个导出11g的数据库，即可导入10g   2)用expdp,impdp,如：
   --在11g服务器上，使用expdp命令备份数据
   
   EXPDP USERID='myuser/myuser@cuc as sysdba' schemas=sybj directory=DATA_PUMP_DIR dumpfile=aa.dmp logfile=aa.log version=10.2.0.1.0
   
   --在10g服务器上，使用impdp命令恢复数据
   
   --准备工作：1.建库2.建表空间3.建用户并授权4.将aa.dmp拷贝到10g的dpdump目录下
   
   IMPDP USERID='myuser/myuser@cucf as sysdba' schemas=sybj directory=DATA_PUMP_DIR dumpfile=aa.dmp logfile=aa.log version=10.2.0.1.0
   
   
   
   --创建数据库
   CREATE DATABASE databasename; 
   --删除数据库
   drop database databasename;
   
   select * from dba_data_files;--查看数据文件路径
   --表空间加数据文件
   alter tablespace DSCS_DATA add datafile '/home/oracle/app/oracle/oradata/orcl/DSCS_DATA01.DBF' size 1024M autoextend on next 100M maxsize unlimited;
   --创建表空间
   create tablespace tablespaceName datafile '/dataName.dbf' size 200M autoextend on next 10M maximize unlimited;
   
   --删除表空间
   drop tablespace schooltbs[including contents and datafiles];
   --3. 查询表空间基本信息
   select * from DBA_TABLESPACES;
   
   --查看是否锁表
   select l.session_id sid,
          s.serial#,
          l.locked_mode     锁模式,
          l.oracle_username 登录用户,
          l.os_user_name    登录机器用户名,
          s.machine         机器名,
          s.terminal        终端用户名,
          o.object_name     被锁对象名,
          s.logon_time      登录数据库时间
     from v$locked_object l, all_objects o, v$session s
    where l.object_id = o.object_id
      and l.session_id = s.sid
    order by sid, s.serial#;
   --解锁引号中'sid,serial'
   ALTER system kill session '136,4322'; 
   
   --查看锁表原因
   select l.session_id sid, 
          s.serial#, 
          l.locked_mode, 
          l.oracle_username, 
          s.user#, 
          l.os_user_name, 
          s.machine, 
          s.terminal, 
          a.sql_text, 
          a.action from v$sqlarea a, v$session s, v$locked_object l 
   where l.session_id = s.sid 
      and s.prev_sql_addr = a.address 
   order by sid, s.serial#;
   
   --查看被锁的表：
    select p.spid,
           c.object_name,
           b.session_id,
           b.oracle_username,
           b.os_user_name
      from v$process p, v$session a, v$locked_object b, all_objects c
     where p.addr = a.paddr
       and a.process = b.process
       and c.object_id = b.object_id;
       
   
   --查看表空间的大小
   select  ddf.TABLESPACE_NAME,sum(ddf.BYTES)/1024/1024 from dba_data_files ddf group by ddf.TABLESPACE_NAME;  
   --查看剩余表空间的大小
   select dfs.TABLESPACE_NAME,sum(dfs.BYTES)/1024/1024 from dba_free_space dfs where dfs.TABLESPACE_NAME='DSCS_DATA' group by dfs.TABLESPACE_NAME;
   
   
   -- 创建用户
   create user  myuser identified by myuser default tablespace tsp1 temporary tablespace temp;
   -- 更改用户
   alter user myuser identified by 123 default tablespace users;
   --锁定用户
   alter user myuser account lock|unlock;
   --删除用户
   drop user myuser cascade;--删除用户模式
   
   --oracle数据库中的角色
   --connect,dba,select_catalog_role,delete_catalog_role,execute_catalog_role,exp_full_database,imp_full_database,resource
   --授予连接服务器的角色
   grant connect to myuser;
   --授予使用表空间的角色
   grant resource to myuser with grant option;--该用户也有授权的权限
   -- 授予操作表的权限
   grant select,insert on user_tbl to myuser;--当前用户
   grant delete,update on u1.user_tbl to myuser;--系统管理员
   
   --数据定义语言（DDL）:create,alter,drop
   --数据操纵语言（DML）:insert,delete,update,select
   --数据控制语言（DCL）:grant,revoke
   --事务控制语言（TCL）:commit,savepoint,rollback
   
   
   --用于操作表的命令
   --create table
   --alter table
   --truncate table
   --drop table
   --修改表的命令
   alter table stu_table rename to stu_tbl;--修改表名
   alter table stu_tbl rename column stu_sex to sex;--修改列名
   alter table stu_tbl add (stu_age number);--添加新列
   alter table stu_tbl drop(deptname);--删除列
   alter table stu_tbl modify(stu_sex varchar2(2));--更改列的数据类型
   alter table stu_tbl add constraint pk_stu_tbl primary key(id);--添加约束
   
   
   --利用现有的表创建表
   create table stu_tbl_log as select id,stu_name,stu_age from stu_tbl;--
   --去重复
   select distinct stu_name from stu_tbl;
   --插入来自其他表中的记录
   insert into stu_tbl_log select id,stu_name,stu_age from stu_tbl;
   
   --minus是获取第一张表独有的数据
   --intersect是获取两张表中都有的数据
   --union是整合两张表的数据，都有的只显示一次
   --union all是纯粹的两张表数据整合
   
   
   
   --1.锁：数据库用来控制共享资源并发访问的机制。
   --锁的类型：行级锁，表级锁
   --行级锁：对正在被修改的行进行锁定。行级锁也被称之为排他锁。
   --在使用下列语句时，Oracle会自动应用行级锁：
   --insert,update,delete,select…… for update
   --select……for update允许用户一次锁定多条记录进行更新。
   --使用commit or rollback释放锁。
   --表级锁：
   --lock table user_tbl in mode mode;
   --表级锁类型：
   --行共享 row share
   --行排他 row exclusive
   --共享 share
   --共享行排他 share row exclusive
   --排他 exclusive
   --死锁：两个或两个以上的事务相互等待对方释放资源，从而形成死锁
   
   
   --系列
   --创建系列
   create sequence user_id_seq
   start with 1000
   increment by 1
   maxvalue 2000
   minvalue 1000
   nocycle
   cache 1000;--指定内存中预先分配的序号
   --访问序列：
   select user_id_seq.currval from dual;
   select user_id-seq.nextval from dual;
   --更改删除序列：
   alter sequence user_id_seq maxvalue 10000;--不能修改其start with 值
   drop sequence user_id_seq;
   
   
   --创建视图：
   create or replace view 
   user_tbl_view (vid,vname,vage)
   as select id,user_name,age from user_tbl
   [with check option]|[with read only];
   --创建带有错误的视图：
   create force view user_tbl_force_view as
   select * from user_table;--此时user_table可以不存在
   --创建外联接视图：
   create view user_stu_view as
   select u.id,u.user_name,u.password,s.ddress
   from user_tbl u,stu_tbl s
   where u.s_id(+)=s.id;--哪一方带有(+)，哪一方就是次要的
   --删除视图：
   drop user_stu_view;
   
   
   --索引
   --索引类型：
   --唯一索引，位图索引，组合索引，基于函数的索引，反向键索引
   --创建标准索引：
   create index user_id_index on user_tbl(id) tablespace schooltbs;
   --重建索引：
   alter index user_id_index rebuild;
   --删除索引：
   drop index user_id_index;
   --创建唯一索引：
   create unique index user_id_index on user_tbl(id);
   --创建组合索引：
   create index name_pass_index on user_tbl(user_name,password);
   --创建反向键索引：
   create index user_id_index on user_tbl(id) reverse;
   
   
   
   
   
   --用户：
   select * from dba_users;--查看有哪些用户
   select * from dba_data_files;--查看数据文件
   select * from dba_tablespaces;--查看表空间
   select tablespace_name,sum(bytes), sum(blocks) from dba_free_space group by tablespace_name;--//查看空闲表空间
   select * from dba_data_files where tablespace_name='RBS';--//表空间对应的数据文件
   select * from dba_segments where tablespace_name='USERS';--查看存储信息
   select * from dba_objects;--查看数据库对象
   --CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGE BODY、
   --PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。
   select * from dba_tables;--查看系统所有的表
   select extent_id,bytes from dba_extents where segment_name='CUSTOMERS' and segment_type='TABLE'
   order by extent_id;--//表使用的extent的信息。segment_type='ROLLBACK'查看回滚段的空间分配信息
   --列信息：
   select * from user_tab_columns where column_name='TableName';
   --索引：　
   select * from dba_indexes;//索引，包括主键索引
   select * from dba_ind_columns;//索引列
   select i.index_name, i.uniqueness, c.column_name
     from user_indexes i, user_ind_columns c
    where i.index_name = c.index_name
      and i.table_name = 'ACC_NBR';--联接使用
   --序列：
   select * from dba_sequences;
   --视图：
   select * from dba_views;
   select * from all_views;
   --text 可用于查询视图生成的脚本
   --聚簇：
   select * from dba_clusters;
   --快照：
   select * from dba_snapshots;--快照、分区应存在相应的表空间。
   --同义词：
   select * from dba_synonyms where table_owner='SPGROUP';
   --if owner is PUBLIC,then the synonyms is a public synonym.
   --if owner is one of users,then the synonyms is a private synonym.
   --数据库链：
   select * from dba_db_links;
   --在spbase下建数据库链
   create database link dbl_spnew connect to spnew identified by spnew using 'jhhx';
   
   insert into acc_nbr@dbl_spnew select * from acc_nbr where nxx_nbr='237' and line_nbr='8888';
   --触发器：
   select * from dba_triggers;
   --存储过程，函数从dba_objects查找。
   --其文本：
   select * from user_source where name='BOOK_SP_EXAMPLE';
   --建立出错：
   select * from user_errors;
   --oracle总是将存储过程，函数等软件放在SYSTEM表空间。
   alter table book_example disable constraint book_example_1;--暂停某个约束
   alter table book_example enable constraint book_example_1;--使用某个约束
   --（3）列约束可从表定义看出，即describe;表约束即主键和外键，可从dba_constraints和dba_cons_columns 查。
   select * from user_constraints where table_name='BOOK_EXAMPLE';--查看BOOK_EXAMPLE表的主键
   
   --查询某个表的主键  
   select *
     from user_cons_columns uc
    where uc.constraint_name =
          (select ut.constraint_name
             from user_constraints ut
            where ut.table_name = 'TF_LN_ARREARS_TABLE_TEMP'
              and ut.constraint_type = 'P'); 
   
   
   
   
   --相关表
   --dba_开头..... 
   select * from  dba_users;     -- 数据库用户信息 
   select * from  dba_segments;-- 表段信息 
   select * from  dba_extents;    --数据区信息 
   select * from  dba_objects ;--   数据库对象信息 
   select * from   dba_tablespaces ;--  数据库表空间信息 
   select * from  dba_data_files;--    数据文件设置信息 
   select * from  dba_temp_files;--   临时数据文件信息 
   select * from  dba_rollback_segs ;--  回滚段信息 
   select * from  dba_ts_quotas;--   用户表空间配额信息 
   select * from dba_free_space ;--数据库空闲空间信息       
   select * from dba_profiles;--   数据库用户资源限制信息  
   select * from dba_sys_privs ;--  用户的系统权限信息     
   select * from dba_tab_privs;-- 用户具有的对象权限信息   
   select * from dba_col_privs;-- 用户具有的列对象权限信息 
   select * from dba_role_privs;-- 用户具有的角色信息      
   select * from dba_audit_trail;-- 审计跟踪记录信息        
   select * from dba_stmt_audit_opts;-- 审计设置信息       
   select * from dba_audit_object;--   对象审计结果信息    
   select * from dba_audit_session;-- 会话审计结果信息     
   select * from dba_indexes;-- 用户模式的索引信息         
   --user_开头 
   select * from user_objects;--   用户对象信息                   
   select * from user_source;--   数据库用户的所有资源对象信息    
   select * from user_segments;--   用户的表段信息                
   select * from user_tables ;--   用户的表对象信息                      
   select * from user_sys_privs;--   当前用户的系统权限信息       
   select * from user_tab_privs;--   当前用户的对象权限信息       
   select * from user_col_privs;--   当前用户的表列权限信息       
   select * from user_role_privs ;--  当前用户的角色权限信息      
   select * from user_indexes;--   用户的索引信息                 
   select * from user_ind_columns;--用户的索引对应的表列信息      
   select * from user_cons_columns;--   用户的约束对应的表列信息  
   select * from user_clusters;--   用户的所有簇信息              
   select * from user_clu_columns;-- 用户的簇所包含的内容信息     
   select * from user_cluster_hash_expressions ;--  散列簇的信息  
   select * from user_tab_comments;--查询当前用户的所有用户的表，视图等
   select * from user_col_comments;--查询当前用户的表的列名和注释
   select * from user_tab_columns;--查询当前用户的表的列的结构信息
   select * from user_cons_columns ;--查询用户字段约束和字段对应关系
   select * from user_constraints ;--查看当前用户的表的约束
   --v$开头 
   select * from v$database;--数据库信息	
   select * from v$datafile;--数据文件信息
   select * from v$controlfile;--控制文件信息
   select * from v$logfile;--重做日志信息
   select * from v$instance;--数据库实例信息
   select * from v$log;--日志组信息
   select * from v$loghist;--日志历史信息
   select * from v$sga;--数据库SGA信息
   select * from v$parameter;--初始化参数信息
   select * from v$process;--数据库服务器进程信息
   select * from v$bgprocess;--数据库后台进程信息
   select * from v$controlfile_record_section;--控制文件记载的各部分信息
   select * from v$thread;--线程信息
   select * from v$datafile_header;--数据文件头所记载的信息
   select * from v$archived_log;--归档日志信息
   select * from v$archive_dest;--归档日志的设置信息
   select * from v$logmnr_contents;--归档日志分析的DMLDDL结果信息
   select * from v$logmnr_dictionary;--日志分析的字典文件信息
   select * from v$logmnr_logs;--日志分析的日志列表信息
   select * from v$tablespace;--表空间信息
   select * from v$tempfile;--临时文件信息
   select * from v$filestat;--数据文件的I/O统计信息
   select * from v$undostatUndo;--数据信息
   select * from v$rollname;--在线回滚段信息
   select * from v$session;--会话信息
   select * from v$transaction;--事务信息
   select * from v$rollstat;--回滚段统计信息
   select * from v$pwfile_users;--特权用户信息
   select * from v$sqlarea;--当前查询过的sql语句访问过的资源及相关的信息
   select * from v$sql与v$sqlarea;--基本相同的相关信息
   select * from v$sysstat;--数据库系统状态信息
   --all_开头 
    select * from   all_users;--   数据库所有用户的信息 
    select * from   all_objects;--   数据库所有的对象的信息 
    select * from   all_def_audit_opts;--   所有默认的审计设置信息 
    select * from   all_tables;--   所有的表对象信息 
    select * from   all_indexes;-- 所有的数据库对象索引的信息 
    select * from all_tab_comments;--查询所有用户的表，视图等
   --session_开头 
   select * from  session_roles;--   会话的角色信息 
   select * from  session_privs;--   会话的权限信息 
   --index_开头 
    select * from index_stats;--   索引的设置和存储信息 
   --虚表 
    select * from  dual;-- 系统伪列表信息Oracle基于SQL几个常用的几个系统表 
   --一个有用的例子:(查询表的创建者) 
   SELECT   owner   FROM     DBA_ALL_TABLES   WHERE   table_name=upper('表名');
   
   --Oracle数据库的系统参数都存储在数据库中，可以通过SQLPLUS，以用户SYSYTEM进行查询。
   select * from v$controlfile;--控制文件的信息；
   select * from  v$datafile;--数据文件的信息；
   select * from  v$log;--日志文件的信息；
   select * from  v$process;--处理器的信息；
   select * from v$session;--会话信息；
   select * from  v$transaction;--事务信息；
   select * from  v$resource;--资源信息；
   select * from  v$sga;--系统全局区的信息。
   --上面的视图名中的‘v$’,只是视图名字中的字符。类似于上面的视图或表还有很多，位于：
   --$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL文件中。
   select * from DBA_TABLES;--查询数据库表
   --显示了每个数据库表的列的信息。
   select * from DBA_TAB_COLUMNS;
   select * from  ALL_TAB_COLUMNS;
   select * from  USER_TAB_COLUMNS;
   --显示了模式对象的信息，包括表。
   select * from  DBA_OBJECTS;
   select * from  ALL_OBJECTS;
   select * from  USER_OBJECTS;
   --完整性约束 显示有关约束的一般信息。
   select * from  DBA_CONSTRAINTS;
   select * from  ALL_CONSTRAINTS;
   select * from  USER_CONSTRAINST;