oracle_dba常用sql脚本

oracle dba 日常维护操作手册命令总结Oracle DBA日常维护操作手册命令总结：Oracle数据库管理员（DBA）在日常维护数据库时，需要掌握一些常用的命令和操作。

以下是一些重要的Oracle DBA维护命令的总结：1. 数据库连接和认证命令：- sqlplus：用于连接Oracle数据库的命令行工具。

可以使用该命令连接到数据库并执行SQL语句。

- sqlldr：用于将数据从外部文件加载到Oracle数据库表中。

- exp和imp：用于导出和导入数据库对象和数据。

2. 数据库启动和关闭命令：- startup：启动Oracle数据库实例。

- shutdown：关闭Oracle数据库实例。

3. 数据库备份和恢复命令：- RMAN（Recovery Manager）：用于备份和恢复Oracle数据库。

- backup：用于创建数据库备份。

- restore：用于从备份文件中恢复数据库。

4. 数据库性能和监控命令：- tkprof：用于分析和优化SQL查询语句的性能。

- awrreport和ashreport：用于生成数据库性能报告和分析。

- v$表名：用于查看数据库的不同性能指标。

5. 数据库对象管理命令：- create：用于创建数据库对象，如表、索引、视图等。

- alter：用于修改数据库对象的结构，例如修改表结构、添加索引等。

- drop：用于删除数据库对象。

6. 用户和权限管理命令：- create user：用于创建数据库用户。

- alter user：用于修改数据库用户的属性。

- grant和revoke：用于授予和收回用户的权限。

7. 数据库存储管理命令：- create tablespace：用于创建数据库表空间，用于存储数据库对象。

- alter tablespace：用于修改表空间的属性。

- alter database：用于修改数据库的参数和属性。

以上仅是一些常用的Oracle DBA日常维护操作命令的总结，实际使用中还有更多的命令和技术需要掌握。

ORACLE深入第一章ORACLEDBA常用语句和脚本ORACLE深入系列，翻译自Thomas Kyte 的 Expert Oracle Database Architecture我的BLOG一, 常用到的设置环境参数的语句设置SCOTT/TIGER的DEMO运行@ORACLE_HOME/sqlplus/demo/demobld.sql (响应的demodrop.sql.是DROP SCOTT的脚本)做一个登陆用的login.sqldefine _editor=viset serveroutput on size 1000000 使DBMS_OUTPUT有效.set trimspool on SPOOL不会以定长来控制,而是以空格来控制set long 5000 LONG或CLOG 显示的长度set linesize 100set pagesize 9999 每9999行后打印HEADcolumn plan_plus_exp format a80 autotrace后explain plan output的格式column global_name new_value gnameset termout offdefine gname=idlecolumn global_name new_value gnameselect lower(user) || ’@’ || substr( global_name, 1,decode( dot, 0, length(global_name), dot-1) ) global_name from (select global_name, instr(global_name,’.’) dot from global_name );set sqlprompt ’&gname>’set termout onset trimspool on; 去除重定向（spool）输出每行的拖尾空格，缺省为off得到username@dbname的提示符. scott@WWMDB>二, 常用到的DBA脚本Runstats 比较两个作相同事情的方法的优劣点。

Oracle 正常SQL监控SQL1.监控事例的等待：select event,sum(decode(wait_time,0,0,1)) prev, sum(decode(wait_time,0,1,0)) curr,count(*) from v$session_waitgroup by event order by 4;2.回滚段的争用情况：select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where n=n; 3.监控表空间的I/O比例：select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbwfrom v$filestat f,dba_data_files dfwhere f.file#=df.file_id4.监空文件系统的I/O比例：select substr(a.file#,1,2) "#”,substr(,1,30) "name”,a.status,a.bytes,b.phyrds,b.phywrtsfrom v$datafile a,v$filestat bwhere a.file#=b.file#5.在某个用户下找所有的索引：select user_indexes.table_name, user_indexes.index_name,uniqueness, column_namefrom user_ind_columns, user_indexeswhere user_ind_columns.index_name = user_indexes.index_nameand user_ind_columns.table_name = user_indexes.table_nameorder by user_indexes.table_type, user_indexes.table_name,user_indexes.index_name, column_position;6.进程监控：select distinct p.spid unix_process,s.terminal,to_char(s.logon_time,'YYYY/MON/DD HH24:MI') Logon_Time, ernamefrom v$process p, v$session swhere p.addr=s.paddr order by 27.监控SGA中字典缓冲区的命中率select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio", (1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100 "Hit ratio"from v$rowcachewhere gets+getmisses <>0group by parameter, gets, getmisses;8.监控SGA中共享缓存区的命中率，应该小于1%select sum(pins) "Total Pins", sum(reloads) "Total Reloads",sum(reloads)/sum(pins) *100 libcachefrom v$librarycache;select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache;9.显示所有数据库对象的类别和大小select count(name) num_instances ,type ,sum(source_size) source_size , sum(parsed_size)parsed_size ,sum(code_size) code_size ,sum(error_size) error_size, sum(source_size) +sum(parsed_size)+sum(code_size) +sum(error_size) size_required from dba_object_sizegroup by type order by 2;10.监控SGA中重做日志缓存区的命中率，应该小于1%SELECT name, gets, misses, immediate_gets, immediate_misses,Decode(gets,0,0,misses/gets*100) ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100) ratio2FROM v$latch WHERE name IN ('redo allocation', 'redo copy');11.监控内存和硬盘的排序比率，最好使它小于.10,增加sort_area_sizeSELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');12.监控当前数据库谁在运行什么SQL语句SELECT osuser, username, sql_text from v$session a, v$sqltext bwhere a.sql_address =b.address order by address, piece;13.监控字典缓冲区SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;后者除以前者，此比率小于1%,接近0%为好。

oracle 维护管理常用sql 1、查询数据库表空间select * from dba_tablespaces;2、查询数据库用户select * from dba_users;3、查询数据库控制文件select * from v$controlfile;4、查询数据库日志文件select * from v$logfile;5、查询数据库数据文件select *from v$datafile;6、查看数据库字符集；select * from nls_database_parameters7、查看数据库所有的表select * from all_tables;8、查看数据库select * from v$database9、查询系统时间select sysdate from dual；10、查询用户的权限select * from dba_role_privs11、查询数据库的性能视图select * from v$parameter12、查询用户的所可以访问的表select * from user_tables;13、查看数据库实例信息select * from v$instance;14、查看某表的索引select * from user_indexes where table_name='表名' 15、查看sql语句执行信息select * from v$sqlstats;hostname //查看主机名hostid //查看主机IDifconfig -a //ip地址netstat -nruname -m/usr/platform/sun4u/sbin/prtdiag -v/usr/platform/sun4u/sbin/eepromdf -kswap -sswap -lformat(ctrl+d退出）iostat -c 1 5vmstat 2 5/usr/ucb/ps -auxps -efacat /var/adm/messages vxdisk listvxdg listvxinfo -g scpdgvxprint -g scpdg -htvxstat -g scpdguname -Xuname -iprstat -a (按q键退出)cat /etc/inet/inetd.conf cat /etc/inet/ipnodescat /etc/inet/netmaskscat /etc/inet/networkscat /etc/inet/servicesls -l /etc/hostname.*|wc -l cat /etc/path_to_instcat /etc/inet/ntp.confcat /etc/inet/ntp*cluster* cat /etc/vfstabcat /etc/syslog.conf prtconf -vcat /etc/passwd cat /etc/rmtabcat /etc/dfs/dfstabscstatscstat -pscstat -pvscstat -pvvscconf -pscconf -pvscconf -pvvscrgadm -p scrgadm -pv scrgadm -pvv。

oracle用户权限检查1. 系统权限记录在dba_sys_privs 视图中：select grantee, privilege, admin_optionfrom dba_sys_privswhere grantee = 'USERNAME' ;2. 角色权限记录在dba_role_privs 视图中：select grantee, granted_role, admin_optionfrom dba_role_privswhere grantee = 'USERNAME' ;3. 对象权限记录在dba_tab_privs 视图中，虽然视图名看起来像是表权限，但是实际包含了包，存储过程等等对象级的权限：select grantee, owner || '.' || table_name obj_name, privilege, grantablefrom dba_tab_privswhere grantee = 'USERNAME';4. 其他还有部分权限，如表空间的quota权限，记录在dba_ts_quota中：select username,tablespace_name,bytes / 1024 / 1024 "used space (m)",decode(decode(max_bytes, -1, 0, max_bytes) / 1024 / 1024,0,'unlimited',max_bytes / 1024 / 1024) "max space(m)",blocks used_blocks,max_blocksfrom dba_ts_quotaswhere username = 'USERNAME';select * from session_privs; 当前用户拥有的权限SELECT * FROM DBA_SYS_PRIVS; 查询每个用户的权限锁的检查select b.owner,b.object_name,l.SESSION_ID,DECODE(L.LOCKED_MODE,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',TO_CHAR(L.LOCKED_MODE)) MODE_HELD,ERNAMEfrom dba_objects b, v$locked_object l, V$SESSION Swhere b.object_id = l.object_idand l.SESSION_ID = s.SID查看一个长查询的进度SELECT *FROM ( select username,opname,sid,serial#,context,b.sql_text,sofar,totalwork,round(sofar/totalwork*100,2) "% Complete",elapsed_secondsfrom v$session_longops , v$sql bwhere sql_hash_value=b.hash_valueand sql_address = addressand totalwork <> 0)WHERE "% Complete" <> 100 ;查看ACTIVE SESSION的等待事件select a.event,,sum(decode(wait_Time, 0, 0, 1)) "Prev",sum(decode(wait_Time, 0, 1, 0)) "Curr",count(*) "Tot"from v$session_wait a, v$session bwhere a.sid = b.sidand b.status = 'ACTIVE'group by a.event-- having count(*) > 10order by 4;在某个用户下找所有的索引select user_indexes.table_name, user_indexes.index_name,uniqueness, column_namefrom user_ind_columns, user_indexeswhere user_ind_columns.index_name = user_indexes.index_nameand user_ind_columns.table_name = user_indexes.table_nameorder by user_indexes.table_type, user_indexes.table_name,user_indexes.index_name, column_position;通过DBMS_METADATA包得到对象的DLL语句a.获取单个的建表和建索引的语法,其他对象类似：select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual;select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;b.获取一个SCHEMA下的所有建表的语法，以scott为例，其他对象类似：SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name, u.owner) || ';'FROM DBA_TABLES uwhere owner = 'SCOTT' ;查看回滚段名称及大小select segment_name,tablespace_name,r.status,(initial_extent / 1024) InitialExtent,(next_extent / 1024) NextExtent,max_extents,v.curext CurExtentFrom dba_rollback_segs r, v$rollstat vWhere r.segment_id = n(+)order by segment_name;查看表空间的名称及大小select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_sizefrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespace_name;查看数据库的版本Select versionFROM Product_component_versionWhere SUBSTR(PRODUCT, 1, 6) = 'Oracle';查看数据库的创建日期和归档方式Select Created, Log_Mode From V$Database;查看还没提交的事务select * from v$locked_object;select * from v$transaction;查找object为哪些进程select p.spid,s.sid,s.serial# serial_num,ername user_name,a.type object_type,s.osuser os_user_name,a.owner,a.object object_name,decode(sign(48 - command),1,to_char(command),'Action Code #' || to_char(command)) action, p.program oracle_process,s.terminal terminal,s.program program,s.status session_statusfrom v$session s, v$access a, v$process pwhere s.paddr = p.addrand s.type = 'USER'and a.sid = s.sidand a.object = 'EMP'order by ername, s.osuser怎样计算一个表占用的空间的大小？select owner,table_name,NUM_ROWS,BLOCKS * AAA / 1024 / 1024 "Size M",EMPTY_BLOCKS,LAST_ANALYZEDfrom dba_tableswhere table_name = 'XXX';注意:执行以上语句前要先对表做统计分析select sum(a.bytes) / 1024 * 1024 "size"from dba_extents awhere a.segment_name = 'GOV_FDDBR'select a.bytes / 1024 * 1024 "size", (a.blocks * 8192) / 1024 * 1024 "da"from dba_segments awhere a.segment_name = 'GOV_FDDBR'如何查看最大会话数SELECT * FROM V$PARAMETER WHERE NAME LIKE 'proc%';SQL>SQL> show parameter processesNAME TYPE VALUE------------------------------------ ------- ------------------------------aq_tm_processes integer 1db_writer_processes integer 1job_queue_processes integer 4log_archive_max_processes integer 1processes integer 200这里为200个用户。

oracle执行带参数sql脚本Oracle带参数的sql语句脚本转Oracle存储过程要在Oracle中执行带参数的SQL脚本，可以使用PL/SQL块或存储过程来实现。

首先，创建一个PL/SQL块，其中包含需要执行的SQL语句和参数。

例如：```DECLAREmy_param VARCHAR2(10) := 'param_value';BEGIN--执行SQL语句EXECUTE IMMEDIATE 'SELECT * FROM my_table WHERE column= :param' USING my_param;--可以在这里添加其他SQL语句或逻辑COMMIT;END;```在上面的例子中，我们声明了一个变量`my_param`并赋予了一个值。

然后，我们使用`EXECUTE IMMEDIATE`语句执行了一条SELECT语句，并使用`USING`子句将参数传递给SQL语句。

如果你想将带参数的SQL脚本转换为Oracle存储过程，你可以将以上代码封装在一个存储过程中。

例如：```CREATE OR REPLACE PROCEDURE my_procedure (my_param IN VARCHAR2)ISBEGIN--执行SQL语句EXECUTE IMMEDIATE 'SELECT * FROM my_table WHERE column= :param' USING my_param;--可以在这里添加其他SQL语句或逻辑COMMIT;END;```在上述存储过程中，我们定义了一个接受一个输入参数`my_param`的存储过程。

然后，我们使用`EXECUTE IMMEDIATE`语句执行SQL语句，并使用`USING`子句将参数传递给SQL语句。

你可以根据实际需求修改以上示例代码，并根据需要传递不同的参数来执行带参数的SQL脚本。

I一些小技巧A)dba常用工具TOAD 功能之一：获取重建表的脚本putty：连接ssh的服务器的工具Xmanager:这个恐怕不用说了，不过使用的机会并不多，主要是做数据库安装和升级的时候Ultraedit:ultraedit的最大优点是打开的文件变化时能够捕捉到变化，并重新更新。

因此经常使用ultraedit来读取netterm的session log文件Cygwin:一个可以在WINDOWS下模拟LIUNX的软件，在这个软件里可以在WINDOWS下使用LINUX的命令，比如dd,awk，gc++等等，直接在windows下用awk调用ass分析systemstate dump是十分有用的，有时候需要写个简单的c程序，也可以用cygwin来调试Wincvs，可能听说的朋友比较少，cvs是著名的文档版本管理软件，用来管理文档的Firefox+scrapboo:知识库收集工具，在网上看到喝什么好的文档，立即拉到知识库里B)制作sql脚本的注意事项01复制脚本时可能会出现全角空格，全角空格会导致脚本执行失败解决方法：用word查找全角空格并替换为半角，全角空格的代码是^u1228802用vi作为sqlplus编辑器，最后不能以“；”分号结尾。

应该以“/”作为结束符号C)表重建的方法首先通过工具取出建表的相关脚本（使用TOAD或者类似的工具）然后将表RENAME（包含所有索引）然后重建表再将数据用INSERT /*+ APPEND */ SELECT...的方法从原表中导入数据。

D)控制文件和数据文件头中的SCN相关信息以及在数据库启动检查中的作用控制文件中存在4种与SCN有关的信息：●system checkpoint SCN●datafile CNT●datafile checkpoint SCN (在数据库启动过程的一系列检查中不起作用)●datafile stop SCN数据文件头中保存了：●datafile CNT●datafile checkpoint SCN (区别于控制文件，被称为start SCN，实际上跟控制文件中的datafile checkpoint SCN始终保持一致)各类SCN信息的作用：system checkpoint SCN：系统检查点SCN，表示整个数据库位于逻辑时钟的哪个时间点上。

--查看表空间大小select * from dba_free_space;--查看回滚段信息select * from dba_free_space;--取走索引未用的空间alter index index_name deallocate unused;--给索引分配空间alter index index_name allocate extent( size 11n K datafile ' .dbf'); --整理索引碎片alter index index_name coalesce;--查看索引的有效性analyze index index_name validate structure;--监视索引是不是使用alter index index_name monitoring usage;--停止监控alter index index nomonitoring;--sga 总大小show parameter sga_max_size;show parameter sga_target;--查看当前sga去分配和使用情况select * from v$sgastat;---显示sga区的信息select * from v$sga;--或者show parameter sga;--显示表空间中所有的数据表select * from all_tables where tablespace_name='USERS';--查看数据表所在的表空间select * from user_tables where table_name='scott.emp';show parameter cache_size;alter system set db_keep_size =4M;--共享池大小show parameter shared_pool_size;--日志缓冲区show parameter log_buffer;--高速缓冲区建议select * from v$db_cache_advice;alter system set db_cache_advice=on|off|ready;--当前进程信息select * from v$process;--各动态组件调整的时间和调整类型信息select * from v$sga_dynamic_components;--分页查询select * from (select a1.* ,rownum rn from (select * from scott.emp ) a1 where rownum <=3) where rn>=1;--判断是否使用了spfile--方法一select name ,value from v$parameter where name='spfile';--方法二show parameter spfile;--方法三如果返回值是0则使用的是pfile 否则是spfileselect count(*) from v$spparameter where value is not null;--控制j警报文件位置的初始化参数是background_dump_destshow parameter background_dump_dest;--改变oracle默认的时间格式alter session set nls_date_format ='yyyy-mm-dd';--现实当前数据库名称select * from global_name;select * from v$instance;--获取scn号：system commit number 系统该编号select dbms_flashback.get_system_change_number from dual;show user;--查看用户的状态select username ,account_status from dba_users;--用户解锁alter user xdb account unlock;。

ORACLE DBA常用脚本及命令(一)1、查看表空间的名称及大小select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_sizefrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespace_name;2、查看表空间物理文件的名称及大小select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name;3、查看回滚段名称及大小select segment_name, tablespace_name, r.status,(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,max_extents, v.curext CurExtentFrom dba_rollback_segs r, v$rollstat vWhere r.segment_id = n(+)order by segment_name ;4、查看控制文件select name from v$controlfile;5、查看日志文件select member from v$logfile;6、查看表空间的使用情况select sum(bytes)/(1024*1024) as free_space,tablespace_namefrom dba_free_spacegroup by tablespace_name;SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE CWHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;7、查看数据库库对象select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;8、查看数据库的版本Select version FROM Product_component_versionWhere SUBSTR(PRODUCT,1,6)='Oracle';9、查看数据库的创建日期和归档方式Select Created, Log_Mode, Log_Mode From V$Database;10、查看当前所有对象SQL> select * from tab;11、建一个和a表结构一样的空表SQL> create table b as select * from a where 1=2;SQL> create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;12、察看数据库的大小，和空间使用情况SQL> col tablespace format a20SQL> select b.file_id 文件ID,b.tablespace_name 表空间,b.file_name 物理文件名,b.bytes 总字节数,(b.bytes-sum(nvl(a.bytes,0))) 已使用,sum(nvl(a.bytes,0)) 剩余,sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比from dba_free_space a,dba_data_files bwhere a.file_id=b.file_idgroup by b.tablespace_name,b.file_name,b.file_id,b.bytesorder by b.tablespace_name/dba_free_space --表空间剩余空间状况dba_data_files --数据文件空间占用情况13、查看现有回滚段及其状态SQL> col segment format a30SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;14、查看数据文件放置的路径SQL> col file_name format a50SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; 15、显示当前连接用户SQL> show user16、把SQL*Plus当计算器SQL> select 100*20 from dual;17、连接字符串SQL> select 列1||列2 from 表1;SQL> select concat(列1,列2) from 表1;18、查询当前日期SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;19、用户间复制数据SQL> copy from user1 to user2 create table2 using select * from table1;20、视图中不能使用order by，但可用group by代替来达到排序目的SQL> create view a as select b1,b2 from b group by b1,b2;21、通过授权的方式来创建用户SQL> grant connect,resource to test identified by test;SQL> conn test/test一、ORACLE的表的分类：1、REGULAR TABLE：普通表，ORACLE推荐的表，使用很方便，人为控制少。