数据库常用SQL语句

数据库：select * from v$database检查数据库是否启动正常select * from gv$instance;检查数据库当前实例是否启动正常select * from v$instance查看gv视图，确认连接是否为负载均衡select inst_id,count(*) from gv$session group by (inst_id);当前实例select instance_name from v$instance检查监听$lsnrctllsnrctl>status用虚拟表检查数据库是否正常select * from dualselect * from dual@dblink如果显示两行记录，说明数据库都正常启动，如果只有一行，说明有一台停止服务了看HOST_NAME字段下面的值，就可以知道是哪一台停止服务了查询版本号SQL>select * from v$version;查询小版本号[oracle@rac1 ~]$./$RACLE_HOME/OPatch/opatch lsinv -bugs_fixed|grep PSU9952230    12419392  Fri Dec 16 21:22:41 CST 2011   DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010)10248542   12419392  Fri Dec 16 21:22:41 CST 2011   DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011)11724962   12419392  Fri Dec 16 21:22:41 CST 2011   DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011)12419392   12419392  Fri Dec 16 21:22:41 CST 2011   DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011)上面的显示表示PSU版本已经是10.2.0.5.4了！数据库里也可以查询到具体信息，（v$version里面不会显示这个小版本号的）具体如下：select * from registry$history;日期select to_char(sysdate,'YYYY-MM-DD') checkdate from dual;时间select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') indextime from dual;版本select * from v$version;实例select instance_name instname from v$instance;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.查看角色(只能查看登陆用户拥有的角色)所包含的权限sql>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_USERS8.SqlPlus中查看一个用户所拥有权限SQL>select * from dba_sys_privs where grantee='username';其中的username即用户名要大写才行。比如：SQL>select * from dba_sys_privs where grantee='TOM';9、Oracle删除指定用户所有表的方法select 'Drop table '||table_name||';' from all_tableswhere owner='要删除的用户名(注意要大写)';10、删除用户drop user user_name cascade;如：drop user SMCHANNEL CASCADE11、获取当前用户下所有的表：select table_name from user_tables;12、删除某用户下所有的表数据: select 'truncate table  ' || table_name from user_tables;13、禁止外键ORACLE数据库中的外键约束名都在表user_constraints中可以查到。其中constraint_type='R'表示是外键约束。启用外键约束的命令为：alter table table_name enable constraint constraint_name禁用外键约束的命令为：alter table table_name disable constraint constraint_name然后再用SQL查出数据库中所以外键的约束名：select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'14、ORACLE禁用/启用外键和触发器--启用脚本SET SERVEROUTPUT ON SIZE 1000000BEGINfor c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loopDBMS_OUTPUT.PUT_LINE(C.V_SQL);begin EXECUTE IMMEDIATE c.v_sql; exception when others then dbms_output.put_line(sqlerrm); end;end loop;for c in (select 'ALTER TABLE '||TNAME||' ENABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop dbms_output.put_line(c.v_sql); begin execute immediate c.v_sql;exception when others then dbms_output.put_line(sqlerrm); end;end loop;end;/commit;--禁用脚本SET SERVEROUTPUT ON SIZE 1000000BEGINfor c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loopDBMS_OUTPUT.PUT_LINE(C.V_SQL);begin EXECUTE IMMEDIATE c.v_sql; exception when others then dbms_output.put_line(sqlerrm); end;end loop;for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop dbms_output.put_line(c.v_sql); begin execute immediate c.v_sql;exception when others then dbms_output.put_line(sqlerrm); end;end loop;end;/commit; 查看当前实例env | grep SID查看数据库保留字SELECT * FROM V$RESERVED_WORDS查看数据库、实例、许可状态、数据库连接SELECT * FROM V$DATABASESELECT * FROM V$INSTANCESELECT * FROM V$LICENSESELECT * FROM V$DBLINK查看密码文件用户SELECT * FROM V$PWFILE_USERS查看资源限制SELECT * FROM V$RESOURCE_LIMIT查看数据库选项、对象参数SELECT * FROM V$OPTIONSELECT * FROM SYS.ARGUMENT$查看兼容性SELECT * FROM V$COMPATIBILITY查看数据库数据字典(表、视图、索引)SELECT * FROM V$FIXED_TABLESELECT * FROM V$FIXED_VIEW_DEFINITIONSELECT * FROM V$INDEXED_FIXED_COLUMN查看数据库NLS状态SELECT * FROM SYS.PROPS$查看表空间、控制、日志、数据文件及备份、读写状态SELECT * FROM V$TABLESPACESELECT * FROM V$CONTORLFILESELECT * FROM V$LOGFILESELECT * FROM V$DATAFILE 数据文件的存储划分位置SELECT * FROM V$BACKUPSELECT * FROM V$FILESTAT查看归档日志数、路径、进程SELECT * FROM V$ARCHIVE_LOGSELECT * FROM V$ARCHIVED_DESTSELECT * FROM V$ARCHIVE_PROCESSES查看回滚段名、状态SELECT * FROM V$ROLLNAMESELECT * FROM V$POLLSTAT数据字典及某些字段意义SELECT sum(decode(n.statistic#, 15, s.value,0)) UGA,sum(decode(n.statistic#, 20, s.value,0))/1024||'K' PGA,sum(decode(n.statistic#, 180, s.value,0)) Sore_In_Member,sum(decode(n.statistic#, 181, s.value,0)) Sore_In_Disk,sum(decode(n.statistic#, 182, s.value,0)) Sore_Of_Row,sum(decode(n.statistic#, 6, s.value,0)) User_Call,sum(decode(n.statistic#, 5, s.value,0)) User_Rollback,sum(decode(n.statistic#, 4, s.value,0)) User_Commit,sum(decode(n.statistic#, 3, s.value,0)) Current_Opened_Cursor,sum(decode(n.statistic#, 1, s.value,0)) Current_Logon,sum(decode(n.statistic#, 9, s.value,0)) Session_Logical_Read ,sum(decode(n.statistic#, 150, s.value,0)) Short_Table_Scans ,sum(decode(n.statistic#, 151, s.value,0)) Long_Table_ScansFROM V$SESSTAT s,V$STATNAME nWHERE s.STATISTIC# = n.STATISTIC#;select * from v$rollstat;select * from v$sgastat;select * from v$sysstat;select * from V$PROCESS;select * from V$THREAD;select * from V$TIMER;select * from V$OBJECT_DEPENDENCY;select * from V$ROWCACHE;SID：会话唯一标识TS#：表空间唯一标识FILE#：数据文件唯一标识RFILE#：？LATCT#：LATCHPADDR；进程地址SADDR：会话地址SQL_ADDRESS ：SQL语句地址PREV_SQL_ADDR；前一个SQL语句地址KADDR：锁地址ADDR：对象地址LADDR：LATCH修改密码1、有原密码SQL>password2、无原密码SQL>alter user sys identified by  新密码；ORCL =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = webserver)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )二、Oracle RAC常用的日志文件:1、    db的alertlog文件：$ORACLE_BASE/admin/orcl/bdump/$ORACLE_BASE/admin/ams/bdump/2、    oracle crs日志文件：$ORACLE_CRS_HOME/crs/log$ORACLE_CRS_HOME/css/log$ORACLE_CRS_HOME/evm/log$ORACLE_CRS_HOME/srvm/log3、查看日志SQL>show paramter dumpvalue中bdump目录地址alert_实例名.logtail -100 alert_ora92.log |moreview alert_ora92.log清空日志 清空文件cat /dev/null >alert_ora92.log当前用户select * from V$PWFILE_USERS;删除用户及所有对象drop user user_name cascade;查询权限select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SCOTT' );查询用户数据占用磁盘空间select round(sum(bytes)/1024/1024/1024)||'G' from dba_segments where owner not in('SCOTT','SYSTEM','SYS','EXFSYS','ORDSYS','SYSMAN');--数据库表空间总量大小select sum(MB)/1024 as GB from (select tablespace_name, sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name);--使用空间select sum(bytes)/1024/1024/1024 from dba_segments;查大表select Owner, Segment_name, Bytes / 1024 / 1024 || 'M' "Size"  From Dba_segmentsWhere Segment_type = 'TABLE'   and Tablespace_name like '%'   Order by Bytes desc没有dba权限是没办法查询系统表的，也就没法查询所有的表空间大小。如果是dba权限的话，或者授权给某个用户以下表的查看权限的话，可以通过以下语句查看：查看当前用户每个表占用空间的大小：Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name查看每个表空间占用空间的大小：Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name没有DBA的权限,只能查看你自己的表空间的大小等.只能查看v$以user开头的视图,也就是你自己用户下的一些相关的表空间,角色,对象等.例如楼上列举的哪些我给你解释一下他的哪些意思吧select * from USER_TABLESPACES;就是查看你自己能访问的表空间select * from USER_TAB_PARTITIONS;查看你自己用户下表分区select * from USER_EXTENTS;查看你自己用户下的区select * from USER_INDEXES;查看你自己用户所能访问的索引select * from USER_IND_PARTITIONS; 查看自己用户下的索引分区-------常用视图by oracle管理之道●  利用V$SQL查询出使用频率最高的 10 句SQL：select sql_text, executions    from (select sql_text,                     executions,                     rank() over(order by executions desc) exec_rank             from v$sql)   where exec_rank <= 10;  ●  利用V$SQL查询出最耗费磁盘读的前 10 句 SQL：select disk_reads, sql_text    from (select sql_text,          disk_reads,          dense_rank() over(order by disk_reads desc) disk_reads_rank               from v$sql)   where disk_reads_rank <= 10;  ●  利用V$SQL查询出最耗费内存的前 10 句逻辑读SQL语句：select buffer_gets, sql_text    from (select sql_text,           buffer_gets,           dense_rank() over(order by buffer_gets desc) buffer_gets_rank           from v$sql)   where buffer_gets_rank <= 10;  ●  利用V$SQLAREA 和V$SQLTEXT 查看消耗资源最多的 SQL语句，步骤如下。(1) 利用V$SQLAREA 查出消耗资源最多的SQL的 HASH_VALUE，语句如下。SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls  FROM V$SQLAREA  WHERE buffer_gets > 10000000 OR disk_reads > 1000000  ORDER BY buffer_gets + 100 * disk_reads DESC;  (2) 得到HASH_VALUE 后，再利用V$SQLTEXT查询出实际的SQL语句。select ADDRESS, HASH_VALUE, PIECE, SQL_TEXT    from v$sqltext   where hash_value = '2450129223'   order by PIECE;  ●  利用 V$SQLAREA 查询出磁盘读次数超过 100  000 的查询语句，也就是最耗资源的SQL语句，语句如下：select b.username "USER NAME",  a.disk_reads "DISK READ",  a.executions EXECUTIONS,  a.disk_reads / decode(a.executions, 0, 1, a.executions) "READ_EXEC%",  a.sql_text "SQL TEXT"    from v$sqlarea a, dba_users b   where a.parsing_user_id = b.user_id     and a.disk_reads > 100000   order by a.disk_reads desc;  ●  利用V$SQL和V$SQL_PLAN 查询出SQL语句的执行计划，步骤如下：(1) 利用V$SQL查出查询的 SQL语句的ADDRESS和 HASH_VALUE，语句如下：SELECT sql_text, address, hash_value FROM v$sql  WHERE sql_text like 'SELECT * FROM EMP %';  (2) 利用查出的ADDRESS 和 HASH_VALUE，配合 V$SQL_PLAN 相对应的字段，即可查询出此SQL语句的执行计划，语句如下；SELECT operation, options, object_name, cost FROM v$sql_plan  WHERE address = '32557612' AND hash_value =2128902247;  ●  利用 V$SESSION 的 SQL_HASH_VALUE 和 SQL_ADDRESS 字段，配合V$SQLTEXT 的 HASH_VALUE 和 ADDRESS，可查询出当前用户链接的 SQL 语句，步骤如下。(1) 利用V$SESSION 查询出SQL_HASH_VALUE 和SQL_ADDRESS，语句如下：SELECT USERNAME,            TERMINAL, PROGRAM,            SQL_HASH_VALUE,            SQL_ADDRESS, TYPE    FROM V$SESSION   WHERE TYPE NOT LIKE 'BACKGROUND';  (2) 利用查出的SQL_HASH_VALUE 和SQL_ADDRESS，再配合V$SQLTEXT相对应的字段即可，语句如下；SELECT *    FROM V$SQLTEXT   WHERE HASH_VALUE = '889124229'     AND ADDRESS = '000000008EBE3A38'   ORDER BY PIECE;  提示以上所介绍的 V$SQL、V$SQLTEXT、V$SQLAREA、V$SQL_PLAN 与 V$SESSION等都可相互配合使用，方式千变万化。-------常用视图by oracle管理之道-------------------表空间----------------表空间select * from dba_tablespaces;数据文件select * from dba_data_files;添加数据文件ADD DATAFILE  'D:\oracle\user3.dbf' SIZE 3M;临时表空间select * from dba_temp_files;改变数据文件大小ALTER DATABASE DATAFILE  'D:\oracle\user2.dbf' RESIZE 10M;允许数据文件自动扩展ALTER DATABASEDATAFILE 'D:\oracle\user2.dbf'AUTOEXTEND ON NEXT 1M MAXSIZE 10M;查看数据文件是否进行相应的更名的操作select file_name, tablespace_name from dba_data_files order by file_name;查看更新后信息select tablespace_name, status, allocation_type from dba_tablespaces;--表空间使用状态select a.tablespace_name "表空间名称",       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2) "占用率(%)",       round(a.bytes_alloc / 1024 / 1024, 2) "容量(M)",       round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) "空闲(M)",       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) "使用(M)",       to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') "采样时间"  from (select f.tablespace_name,               sum(f.bytes) bytes_alloc,               sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes          from dba_data_files f         group by tablespace_name) a,       (select f.tablespace_name, sum(f.bytes) bytes_free          from dba_free_space f         group by tablespace_name) b where a.tablespace_name = b.tablespace_name order by 2 desc;--表空间使用状态select a.file_id "FileNo",a.tablespace_name "Tablespace_name",round(a.bytes/1024/1024,4) "Total MB",round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,4) "Used MB",round(sum(nvl(b.bytes,0))/1024/1024,4) "Free MB",round(sum(nvl(b.bytes,0))/a.bytes*100,4) "%Free"from dba_data_files a, dba_free_space bwhere a.file_id=b.file_id(+)group by a.tablespace_name,a.file_id,a.bytes order by a.tablespace_name--查询表空间使用率select total.tablespace_name, round(total.MB,2) as Total_MB, round(total.MB-free.MB, 2) as Used_MB,　round((1-free.MB/total.MB)*100, 2) as Used_Pct from(select tablespace_name, sum(bytes)/1024/1024 as MB from dba_free_space group by tablespace_name) free,(select tablespace_name, sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name=total.tablespace_name;--表空间剩余自由空间情况：select tablespace_name,sum(bytes) 总字节数,max(bytes),count(*) from dba_free_space group by tablespace_name；--创建表空间create tablespace DCODS datafile '+ASMGSDCODS_HP' size 10g autoextend on; 打开自动增长--增加数据文件alter tablespace JK_SPACE add datafile '+ASMGSDCODS_HP'  size 5g autoextend on; 打开自动增长创建表空间 数据文件10m 自动扩展1m 最大不限制maxsize unlimited自动分配autoallocate(跟据autoextend大小自动分配-默认可不加,表空间属性allocation_type为system)create tablespace test1 datafile '+DCODS/gsdcods/datafile/xxx_index.1.1' size 10m autoextend on next 1m maxsize unlimited autoallocate;创建表空间 数据文件10m 自动扩展1m 最大不限制maxsize unlimited，分配为：uniform统一、正统，128K，create tablespace test2 datafile '+DCODS/gsdcods/datafile/xxx_index.2.2' size 10m autoextend on next 1m maxsize unlimited uniform;表空间的分配不可修改，可创建新的表空间，将数据块移动到新的表空间,正常情况行数据物理位置不允许改变，影响索引。select * from dba_tables where tables_name='表名'；  row_movement为disabled为不可移动移动表至新的表空间SQL>alter table name123 move tablespace new123tablespace;紧缩空间，使数据文件的空闲部分的空间腾出来。1.先激活movementalter table 表名 enable row movement;2.紧缩空间alter table 表名 shrink space;数据文件select * from dba_data_files;3.查询验证select * from dba_segments where tablespace_name='**表空间名称**'bytes的值是否变小。给表空间加数据文件SQL>alter tablespace XXX add datafile '+DCODS/gsdcods/datafile/xxx_index.1669.811332885' size 10m autoextend on next 1m maxsize 1024m;数据文件10M 自动扩展为ON  自动扩展大小为1M 最大值为1G，+DCODS为ASM名，gsdcods为实例名。查询表空间块(BYTES块大小并不准确_因为数据文件不足64M连续块时，数据会写到下一个数据文件)select * from dba_segments where tablespace_name='**表空间名称**'查询分配的块列表select * from dba_extents where segment_name=''**表名称**';删除表空间SGCIM_BUFF 及数据文件drop tablespace SGCIM_BUFF including contents and datafiles;看一下碎片情况SELECT   tablespace_name,           SQRT (MAX (blocks) / SUM (blocks))           * (100 / SQRT (SQRT (COUNT (blocks))))              FSFI    FROM   dba_free_spaceGROUP BY   tablespace_nameORDER BY   1;表空间碎片化程度分析（FSFI<30,破碎化程度高） 的语句select tablespace_name,       round(sqrt(max(blocks) / sum(blocks)) *             (100 / sqrt(sqrt(count(blocks)))),             2) FSFI,       (case         when sqrt(max(blocks) / sum(blocks)) *              (100 / sqrt(sqrt(count(blocks)))) > = 30 then          '正常'         when sqrt(max(blocks) / sum(blocks)) *              (100 / sqrt(sqrt(count(blocks)))) < 30 then          '表空间破碎化程度高，请整理'       end) Prompt  from dba_free_spacegroup by tablespace_nameorder by 2;LMT上进行碎片整理。ttitle -   center  'Database Freespace Summary'  skip 2comp sum of nfrags totsiz avasiz on reportbreak on reportset pages 999col tsname  format     a16 justify c heading 'Tablespace'col nfrags  format 999,990 justify c heading 'Free|Frags'col mxfrag  format 999,999 justify c heading 'Largest|Frag (MB)'col totsiz  format 999,999 justify c heading 'Total|(MB)'col avasiz  format 999,999 justify c heading 'Available|(MB)'col pctusd  format     990 justify c heading 'Pct|Used'select total.TABLESPACE_NAME tsname,       D nfrags,       C/1024/1024 mxfrag,       A/1024/1024 totsiz,       B/1024/1024 avasiz,       (1-nvl(B,0)/A)*100 pctusdfrom    (select sum(bytes) A,            tablespace_name            from dba_data_files            group by tablespace_name) TOTAL,    (select sum(bytes) B,            max(bytes) C,            count(bytes) D,            tablespace_name            from dba_free_space            group by tablespace_name) FREEwhere      total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)/select count(*),ts_name from dba_recyclebin group by ts_name;--连接数select count(*) from v$session;  select count(1) from v$session where username='ECIQ_OPERATION'执行一个SQL脚本SQL>@?/rdbms/admin/spcreate?表示oracle_home目录，.sql后辍可以不加。常用用户SQL表：select * from cat;select * from tab;select table_name from user_tables;视图：select text from user_views where view_name=upper('&view_name');索引：select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name;触发器：select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;快照：select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;同义词：select * from syn;序列：select * from seq;数据库链路：select * from user_db_links;约束限制：select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUSfrom user_constraints WHERE TABLE_name=upper('&TABLE_Name');本用户读取其他用户对象的权限：select * from user_tab_privs;本用户所拥有的系统权限：select * from user_sys_privs;用户：select * from all_users order by user_id;数据字典：select table_name from dict order by table_name;锁及资源信息：select * from v$lock;不包括DDL 锁数据库字符集：select name,value$ from props$ where name='NLS_CHARACTERSET';inin.ora 参数：select name,value from v$parameter order by name;SQL 共享池：select sql_text from v$sqlarea;数据库：select * from v$database控制文件：select * from V$controlfile;重做日志文件信息：select * from V$logfile;来自控制文件中的日志文件信息：select * from V$log;来自控制文件中的数据文件信息：select * from V$datafile;NLS 参数当前值：select * from V$nls_parameters;ORACLE 版本信息：select * from v$version;描述后台进程：select * from v$bgprocess;查看版本信息：select * from product_component_version;查询表结构select substr(table_name,1,20) tabname,substr(column_name,1,20)column_name,rtrim(data_type)||'('||data_length||')' from system.dba_tab_columnswhere owner='username'查询某个模式下面数据不为空的表declareCursor c is select TNAME from tab;vCount Number;table_nm Varchar2(100);sq varchar2(300);beginfor r in c looptable_nm:=r.TNAME;sq:='select count(*) from '|| table_nm;execute immediate sq into vCount;if vCount>0 thendbms_output.put_line(r.tname);end if;end loop;end;客户端主机信息SELECTSYS_CONTEXT('USERENV','TERMINAL') TERMINAL,SYS_CONTEXT('USERENV','HOST') HOST,SYS_CONTEXT('USERENV','OS_USER') OS_USER,SYS_CONTEXT('USERENV','IP_ADDRESS') IP_ADDRESSFROM DUAL消除重复行select  distinct zbfl2 from 指标名称  取前几位where t.organ_code like '26%' and length(t.organ_code)=4取前几行select * from AD_BCMK_HD_DATA  where rownum <=1select * from AD_BCMK_HD_DATA  where rownum =1三、oracle架构自动数据处理（Automatic Data Processing,ADP）磁盘阵列（Redundant Array of Inexpensive Disks,RAID）数据库管理系统（Database Management System,DBMS）数据库管理员（Datebase Administrator,DBA）结构化查询（Structured Query Language,SQL）数据定义语言（Data Definition Language,DDL）数据操作语言（Data Manipulation Language,DML）实例的5个必须进程：SMON,PMON,DBWR,LGWR,CKPT系统全局区(SGA)中包含了以下几个内存结构：共享池(shared pool)：库高速缓存(library cache)、数据字典高速缓存(data dictionary cache)。修改共享池：SQL>alter system set shared_pool_size = 30m;改变共享池大小受限于SGA_MAX_SIZE参数。数据库高速缓冲区(database buffer cache)修改高速缓冲区大小:SQL>alter system set db_chche_size = 600M scope=both;1.用命令来查看SGA_MAX_SIZE的值：SQL> show parameter------------------------------------ ----------- ------------------------------resource_limit                       boolean     FALSEresource_manager_plan                stringresumable_timeout                    integer     0rollback_segments                    stringserial_reuse                         string      disableservice_names                        string      gsyyjc, YYJCsession_cached_cursors               integer     20session_max_open_files               integer     10sessions                             integer     2205sga_max_size                         big integer 1536Msga_target                           big integer 1536MNAME                                 TYPE        VALUE------------------------------------ ----------- -------------------------简化显示(64G内存配置)SQL> show parameter ga;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------lock_sga                             boolean     FALSEmax_commit_propagation_delay         integer     0pga_aggregate_target                 big integer 10284Mpre_page_sga                         boolean     FALSEsga_max_size                         big integer 30912Msga_target                           big integer 30912M2.利用数据字典V$parameter 来获得参数SGA_MAX_SIZE的值SQL> col name for a20SQL> col value for a25SQL> select name,type,value from v$parameter where name = 'sga_max_size';NAME                       TYPE VALUE-------------------- ---------- -------------------------sga_max_size                  6 16106127363.查询系统中SGA当前分配的尺寸和单元大小：SQL>select component,current_size,granule_size from v$sga_dynamic_components;4.对应查看OEM DBconsole.5.查看系统全局区当前的分配情况。SQL> show sgaTotal System Global Area 1610612736 bytes oracle实例SGA总体Fixed Size                  2154808 bytes oracle启动固定内存Variable Size             983441096 bytes 可变内存Database Buffers          620756992 bytes 数据缓存Redo Buffers                4259840 bytes 日志缓存SGA最大可达到操作系统物理内存的70%。系统内存越多，这个比例也可以越高。在AIX UNIX中，这个最大值就是70%修改SGA32位系统SGA不能超过1.7G.备份dbs目录下所有文件SQL>alter system set sga_target=1024m scope=spfile;SQL>alter system set sga_max_size=1024m scope=spfile;#bc裸设备：disk-raid-raid group-lun(pv)-vg-lvMount –o cio 启用此功能，性能基本接近裸设备并发io 在磁盘阵列上才能看出效果，powerpath 系统必须安装此软件才能用磁盘阵列同步io 例如写入语句，必须将其写到磁盘上，才能返回执行下一步操作。异步io 插入语句后可以立即执行下一步操作，即使没有写到磁盘上，也可以执行下一个操作，会将上个命令放到系统的队列中去示例：设置数据库可用SGA区域的最大值为800MSQL>alter system set sga_target=800m scope=both;设置默认数据缓存为400MSQL>alter system set db_cache_size=400m scope=both;设置数据缓存保留池为120MSQL>alter system set db_keep_cache_size=120m scope=both;设置数据缓存循环池为140MSQL>alter system set db_recycle_cache_size=140m scope=both;    内存缓冲区顾问：(v$db_cache_advice)用来帮助获得调整数据库高速缓冲区的统计信息。查看缓冲区顾问状态是否开启：select id,name,block_size, advice_status from v$db_chche_advice;select * from v$db_chche_advice;SGA还包含large pool 和 javapool两个可选的内存结构。large pool 的主要目的是提高效率，是一个相对比较简单的内存结构，与shared pool不同的是它没有LRU队列。在多线程(MTS)或共享服务器(shared server)连接时，ORACLE服务进程的PGA的大部分区域(也称UGA)将放入large pool(stack space除外)。另外在大规模I/O及备份和恢复操作时可能使用该区。可以通过设置参数LARGE_POOL_SIZE的值来配置large pool的大小。该参数也是一个动态参数。重做日志缓冲区(redo log buffer)其他一些结构(锁和统计数据)等物理结构文件控制文件(control files):包含了维护和校验数据库一致性所需的信息。重做日志文件(redo log files):包含了当系统崩溃后进行恢复所需记录的变化信息。数据文件(data file):包含了数据库中真正的数据。其它文件初始化参数文件(parameter files)：定义了实例的特性，如系统全局区中一些内存的大小、DBWR的个数。密码文件(password files)：包含了数据库管理员或操作员用户在启动和关闭实例时所需的密码。虽然ORACLE数据库提供了相当完备的安全管理机制，但是在ORACLE数据库没有开启时如何验证要启动数据库的人是真正的数据库管理员或操作员呢？这就是ORACLE引入密码文件的原因。归档日志文件(archived redo log files)：是重做日志文件的脱机备份。在系统崩溃时可能需要这些文件。修改PGAPGA的值为4的整数倍内存区:(程序全局区PGA:program global area)私有内存区，不能共享，且只属于一个进程。它随着进程的创建而被分配，随着进程的终止而被回收。物化视图:物化视图是快照的功能升级，更为强大.物化视图是8i之后才有的，在原来snapshot 基础增加很多功能导出和导入（EXP、IMP）中使用MVDATA参数来实现实体视图数据的导出和导入oracle普通视图与物化视图的区别MV和View差别非常大，不是几句能说清MV是自动刷新或者手动刷新的，View不用刷新MV也可以直接update，但是不影响base table，对View的update反映到base table上MV主要用于远程数据访问，mv中的数据需要占用磁盘空间，view中不保存数据快照与物化视图....快照是用来做AWRRPT或者STSPACK的物化试图 是用来提高性能，预先准备数据，或者做查询重写。。。完全不一样#ipcs -sm 查看共享内存Du –sm TEST/ 查看test文件夹大小用 ps -ef|grep smon看你当前有多少个已经启动的实例，smon:系统监督进程。用 ps -ef|grep smon  oracle 26412     1  0  Mar 26  ?        30:48 ora_smon_bietl1  oracle 25708     1  0  Mar 26  ?         7:19 asm_smon_+ASM1  oracle 14235     1  0 18:09:37 ?         0:07 ora_smon_DRMT1  oracle  3618     1  0  Sep  7  ?        25:06 ora_smon_YYJC1  oracle 27811     1  0  Sep  6  ?        29:14 ora_smon_gsdcods1归档日志位置bietl1 +dcodsDRMT1 +dcodsyyjc1 /archiveloggsdcods1 /archivelog/gsdcods提交SQL> commit;当前连接数select count(*) from v$session;当前用户show user杀死连接进程查询所有会话select saddr,sid,serial#,username from v$session  杀进程sid与serial#alter system kill session '158,2697';查询哪些文件过大find /oracle -size +100000用du + sort 找一下，看看有没有log文件太大的~# du -sh /* | sort -nr# cd / && du -sh *# du --max-depth=1清除trc跟踪文件可以清除bdump,cdump,udump目录下的trc文件,但目录必须保留.rm /oracle/app/admin/DRMT/bdump/*.trcrm /oracle/app/admin/DRMT/udump/*.trcrm /oracle/app/admin/YYJC/bdump/*.trcrm /oracle/app/admin/YYJC/udump/*.trcrm /oracle/app/admin/bietl/bdump/*.trcrm /oracle/app/admin/bietl/udump/*.trcrm /oracle/app/admin/gsdcods/bdump/*.trcrm /oracle/app/admin/gsdcods/udump/*.trcls -ltotal 174 查看一下日志内容rm *.trc系统运行时trc文件不能直接删除.$cat /dev/null >delname$ cp /dev/null listener.log查询前台进程ps -ef|grep "LOCAL=NO"清理所有前台进程ps -ef|grep "LOCAL=NO" |awk '{ pring "kill -9 " $2'|sh查询后台ASM实例进程数ps -ef|grep ASM |grep -v grep|wc -lselect distinct process from v$session where username is not null;select process,count(*) from v$session where username is not null group by process;ps -ef|grep 1234 |grep -v grep清除异常的会话和进程(oracle dba手记一,111页)select 'ALTER SYSTEM KILL SESSION ''' || SID || ', '|| SERIAL# || ''';' from v$session where process in (19995,19969);清表TRUNCATE TABLE  bm_a003_new删除表Drop table 表名tablename查前十条数据select * from sg_cim.CSTCONSUMER where rownum <10 order by  timestamps  desc怎么可以快速做一个和原表一样的备份表?create table new_table as (select * from old_table);两表之间插入数据：insert  into bm_a003 (bm,mc) select bm,mc from dwb截取位数：update bm_a003 set bm=substring(rtrim(bm),3,8)将五位bm+01select  bm,bm='01'+substring(bm,0,7) from bm_a003_newupdate bm_a003_new set bm='01'+substring(bm,0,7)取前十条数据select * from AAA where rownum<=10查询出第5条数据和第10条数据之间：select first_name , rnum from ( select rownum rnum , first_namefrom s_emp where rownum <=10 ) where rnum between 5 and 10 ;group by 用法：select max(col001) bm ,max(col002) mc   into dwb  from a group by Col001用别名：update gzhkk set a.f004=b.e003 from gzhkk a , gzdkk b  where a.f001=b.e001放入临时表：select * into #lsb from c  where a003 is null查找重复数据：select max(name),count(id) from c group by idselect  max(name)  xm ,count(id) tj   into #lsb2 from c  group by idselect * from #lsb2 where tj>1count计数函数查两表之间不同  select DH,DXID,KMID,YWLXID,ZDNAME,MONEY1,MONEY2,SUM_J,SUM_D from FMIS0500.ZWDXJELJB2013@CWGK_YYJC minusselect DH,DXID,KMID,YWLXID,ZDNAME,MONEY1,MONEY2,SUM_J,SUM_D from ODS_YYJC_BUF.ZWDXJELJB;某日期之前之后select * from 表 where 字段>to_date('2001-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')今天星期几select to_char(sysdate,'DAY') from dual两日期之间select 字段.* from 表where 字段>=to_date('2014-4-10 00:00:00','yyyy-mm-dd hh24:mi:ss')  and 字段<=to_date('2014-4-10 23:59:59','yyyy-mm-dd hh24:mi:ss')防锁表，只锁行select t.*,t.rowid from csweb_01.W_USERS CUR_STATUS_CODE t where email='[e-mail]' for updatelike用法select * from jtppzk where kmbm like '40301%'查询归档日志的使用情况select * from v$flash_recovery_area_usage;查看归档模式archive log list查看归档日志大小show parameter db_recovery_file_dest_size;查看当前闪回区大小及使用情况select * from v$recovery_file_dest查看闪回区使用率select file_type,percent_space_used as used,percent_space_reclaimable as reclaimable,number_of_files as "number" from v$flash_recovery_area_usage;扩展闪回区大小SQL>alter system set db_recovery_file_dest_size=10G scope=both;增大闪回日志文件的最大大小alter system set DB_RECOVERY_FILE_DEST_SIZE=20G;oracle登录不上如果archive log模式下不能正常startup,则先恢复成noarchive log,startup成功后，再shutdown;shutdown immediate;startup mount;alter database noarchivelog;alter database open;shutdown immediate;再次startup以archive log模式shutdown immediate;startup mount;show parameter log_archive_dest;  alter database archivelog;archive log list;                 alter database open;重建密码文件orapwd重建下密码文件 顺便加个参数nosysdba=n查询索引select index_name,index_type from user_indexes;SQL> select comp_id,status,version from dba_registry;检查是否有无效的对象。Sql>select object_name,status from dba_objects where object_Name in ('SYS','SYSTEM') and status = 'INVALID';Sql>select count(*) from dba_objects where object_Name in ('SYS','SYSTEM') and status = 'INVALID';检查java pool,share pool 大于150M，OK。检查版本升级：SQL> select comp_id,status,version from dba_registry;执行编译失效对象：Sql>select count(*) from dba_objects where status = 'INVALID';如何查询某天的数据?    select * from table_name where trunc(日期字段)＝to_date('2003-05-02','yyyy-mm-dd');149. 如何在某个用户下找所有的索引？   select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name   from user_ind_columns, user_indexes   where user_ind_columns.index_name = user_indexes.index_name   and user_ind_columns.table_name = user_indexes.table_name   order by user_indexes.table_type, user_indexes.table_name,   user_indexes.index_name, column_position;如何知道使用CPU多的用户session?   11是cpu used by this session同义词授权grant select, insert, update, delete on 对方用户.对方同义词 to 我的用户;修改列名SQL> create table a (a number,b char(1));Table createdSQL> alter table a rename column a  to c;Table altered修改列并迁移列alter table CSTEUQCONSUMER add CONSNO_TMP number(16);修改原表，增加临时字段update CSTEUQCONSUMER set CONSNO_TMP=CONSNO,CONSNO=null;将数据从原字段更新到临时字段，清空原始字段数据alter table CSTEUQCONSUMER modify CONSNO varchar2(16);修改原始列字段类型update CSTEUQCONSUMER set CONSNO=CONSNO_TMP,CONSNO_TMP=null;将临时字段内数据更新到原始字段，并清空临时字段内数据。alter table CSTEUQCONSUMER drop column CONSNO_TMP;删除临时字段。查询表建立或修改时间SQL>select creatd,object_type,last_ddl_time,timestamp from dba_objects where object_name='字段名';RAC工具通过oracle免费工具IPD/OS对所有节点实时监控通过开源工具NMON对单节点实时监控修复RAC 单机识别数据文件alter system check datafiles；备份比例rman 1:6exp 1:4数据泵备份示例expdp dp/dp directory=dpdir dumpfile=fmis3000_tables.dmp tables=FMIS3000.COMPANYINFO,FMIS3000.XTBILL2010 flashback_scn=12368567404564 logfile=exp_fmis3000_tables.logexpdp dp/dp directory=dpdir dumpfile=three_tables.dmp tables=FMIS3000.COMPANYINFO,FMIS3000.ZWITEMSYW flashback_scn=12368558136616 logfile=three.logimpdp dp/dp directory=dpdir dumpfile=fmis3000_tables.dmp remap_schema=fmis3000:ods_yyjc_buf remap_tablespace=fmis3000:odsview,FMIS_LARGE:odsview,FMIS_LOB:odsview,FMISINDEX:odsview table_exists_action=append logfile=imp001_fmis3000_tables.logimpdp dp/dp directory=dpdir dumpfile=three_tables.dmp remap_schema=fmis3000:ods_yyjc_buf remap_tablespace=fmis3000:odsview table_exists_action=appendimpdp dp/dp directory=dpdir dumpfile=three_tables.dmp tables=fmis3000.zjzfdj remap_schema=fmis3000:ods_yyjc_buf remap_tablespace=fmis3000:odsview     impdp dp/dp directory=dpdir dumpfile=fmis3000_tables.dmp tables=fmis3000.zjzfgj remap_schema=fmis3000:ods_yyjc_buf remap_tablespace=fmis3000:odsview  impdp dp/dp directory=dpdir dumpfile=fmis3000_tables.dmp tables=fmis3000.zjzfdj remap_schema=fmis3000:ods_yyjc_buf remap_tablespace=fmis3000:odsview,FMIS_LARGE:odsview,FMIS_LOB:odsview,FMISINDEX:odsviewdblinksys用户给其它用户授权,允许其它用户建立DBlinkgrant create DATABASE LINK to IMS_YJ;1.在dmp导入成功之后，我们需要手工创建网站所需的数据链接  <1>.以system用户通过PL/SQL登录数据库，执行如下语句创建数据库链接：-- Create database linkdrop public database link CSWEB_GLOBAL_01.DB_LINK_BPM.REGRESS.RDBMS.DEV.US.ORACLE.COM;create public database link CSWEB_GLOBAL_01.DB_LINK_BPM.REGRESS.RDBMS.DEV.US.ORACLE.COM  connect to BPM  IDENTIFIED BY BPM_PWD  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ORCL) ))';另:  using '单引号里可以使用PL的连接串';        注意：红色部分需要根据各网省实际情况进行调整，其中 BPM_PWD为数据库bpm用户对应的密码，localhost为bpm用户数据库地址，orcl为该数据库实例名。   <2>.DB_LINK创建成功后，以csweb_global_01用户登录数据库，执行如下语句：       select * from pub_users;      如果查询成功，说明DB_LINK创建成功。   <3>.以csweb_global_01用户登录数据库,点击PL/SQL带叉的图标(编译无效对象)，在新页面点击执行，确保csweb_global_01用户下所有触发器（Trigger）都已编译通过。 2.----创建dblink ODS_YJcreate database link ODS_YJconnect to ods_yyjc_zhims identified by ods_yyjc_zhimsusing'(226 =conn  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.212.211.226)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = YYJC)    )  ))';3.dblink查询select * from name @dblinkname修改用户名SQL> ALTER DATABASE DEFAULT TABLESPACE mis_data;       注意：改过名字的用户，权限会继承，但是默认表空间不会继承，需要手工再设定默认表空间：SQL> ALTER USER OA identified by oa default tablespace MIS_DATA temporary tablespace TEMP;修改底层表 USER$更换用户名注：修改oracle用户名需要sys用户，或者给操作用户操作user$表的权限。SQL> grant select on user$ TO system;SQL> grant update on user$ to system;1、 查看用户的user#SQL> show userUSER is "SYSTEM"SQL> select user#,NAME from SYS.user$ WHERE NAME='TEST';      USER# NAME---------- ------------------------------        93 TEST2、修改用户名SQL> UPDATE USER$ SET NAME='新用户名' WHERE USER#=93;   已更新 1 行。注：单引号中的新用户名一定要用大写，如果是小写，下面会提示找不到该用户。3、提交完成SQL> COMMIT;4、修改系统检查点SQL> ALTER SYSTEM CHECKPOINT;5、修改新用户密码SQL> ALTER USER 新用户名 IDENTIFIED BY 新密码;6、如果提示新用户不存在，则刷新shared_poolSQL> ALTER USER 新用户名 IDENTIFIED BY 新密码           *ERROR 位于第 1 行:ORA-01918: 用户'新用户'不存在SQL> ALTER SYSTEM FLUSH SHARED_POOL;7、刷新shared_pool后重新修改用户密码SQL> ALTER USER 新用户 IDENTIFIED BY 新密码;8、测试连接SQL> CONN 新用户/新密码@orcl;9、查看新用户是否拥有原用户的对象SQL> SELECT * FROM TAB;3月替换为7月update ZBCJ_USER_XZ.ETL_INTERRUPT_XZ set start_time=replace(start_time,'-03-','-07-')日期范围 时间范围select count(*) from 表名 where 时间字段 between to_char('2011-03-24','yyyy-mm-dd') and to date('2011-03-25','yyyy-mm-dd');-- 不建议使用SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd') = '2006-09-21';select * from 表名 where to_char(时间字段，'yyyymmddhh24miss') between '20120908000000' and 20100908235959';日期比较select * from e_mp_day_energy t where  id = '411180434' and to_char (data_date,'yyyymmdd') > '20140621'and to_char (data_date,'yyyymmdd') <= '20140626' --查询PVP_E日期范围select cons_id, count(*) from v_pl_ct_excp_sum_x t where to_char(info_date,'yyyymmdd')between '20140701'and'20140731' group by cons_id having count(*)>1DBA必须熟悉的、最有用的DBA视图。dba_data_files                            关于数据库文件的信息dba_db_links                              数据库中的所有数据库链路dba_extents                               数据库中包括所有分区dba_free_space                          所有表空间中自由分区dba_indexes                              数据库中所有索引的描述dba_ind_columns                       在所有表及聚族上压缩索引的列dba_objects                               数据库中所有的对象dba_rollback_segs                      回滚段的描述dba_segments                           为所有数据库段分配的存储空间dba_sequences                         数据库中所有顺序书的描述dba_synonyms                         数据库中所有同义词dba_tables                               数据库中所有表的描述dba_tablespaces                      数据库中所有表空间的描述dba_tab_columns                     所有表描述、视图以及聚族的列dba_tab_grants                       数据库中对象所授的权限dba_tab_privs                         数据库中对象所授的权限dba_ts_quotas                        所有用户表空间限额dba_users                             关于数据库的所有用户信息dba_views                             数据库中所有视图