ITPub博客

SQL> create table t1(name varchar2(6),age number(3)); Table created. SQL> insert into t1 values('leo',28); 1 row created. SQL> commit 2 ; Commit complete. SQL> insert into t1 values('ken',29); 1 row created. SQL> commit; Commit complete. SQL> CREATE MATERIALIZED VIEW v1 as (select * from t1); Materialized view created. SQL> select * from v1; NAME AGE ------ ---------- leo 28 ken 29 SQL> i

使用DBMS_REPAIR包修复DECLAREisClean BOOLEAN;BEGINisClean := FALSE;WHILE isClean=FALSELOOPisClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID,DBMS_REPAIR.LOCK_WAIT);DBMS_LOCK.SLEEP(10);END LOOP;EXCEPTIONWHEN OTHERS THENRAISE;END; 参考网址：http://www.blogjava.net/decode360/archive/2009/07/30/292760.htmlhttp://www.cnblogs.com/rootq/archive/2009/02/05/1384800.htmlhttp://oracledba.spaces.live.com/blog/cns!57D0C396BA028F14!249.entry[@more@]

SQL> drop table sfcs_sn_site_statistics_arv; drop table sfcs_sn_site_statistics_arv * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified 1.用dba权限的用户查看数据库都有哪些锁 SQL> select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time; USERNAME SID SERIAL# LOGON_TIM ------------------------------ ---------- ---------- ---

1. set blatC:Documents and SettingsAdministrator>blat -profileBlat v2.6.2 w/GSS encryption (build : Feb 25 2007 12:06:19)Profile(s) for all users of this computer --SMTP: monitor.com "leo@monitor.com" 3 25C:Documents and SettingsAdministrator>2.set nbmail.cmd for netbackupC:Program FilesVeritasNetBackupbin>notepad nbmail.cmd @IF "%~4"=="" ( blat %3 -s %2 -t %1 -i NetBackup -server monitor.com -q ) ELSE ( blat %3 -s %2 -t %1 -i NetBackup -server monitor.co

---------------------------------收缩表空间---------------------------------1.查看表的使用状况select sum(bytes)/1024/1024/1024 Gb from user_segments where segment_name='WIP_WO';2.开启行迁移功能alter table wip_wo enable row movement;3.整理表空间内的碎片alter table wip_wo shrink space compact;4.收缩表空间alter table wip_wo shrink space;5.查看收缩后的表空间SQL> select sum(bytes)/1024/1024 M from user_segments where segment_name='WIP_WO';6.收缩index的空间SQL> alter index WIP_WO_INX1 shrink spa

-----------------------------------------------------导出与导入表数据 For Oracel-----------------------------------------------------1.建立导出目录SQL> create directory dmpdir as '/archivelog/dmpdir';Directory created.2.查询目录是否建立SQL> select * from dba_directories;OWNER DIRECTORY_NAME------------------------------ ------------------------------DIRECTORY_PATH--------------------------------------------------------------------------------SYS ORACLE_OCM_CONFIG_DIR/orahome/10.2/ccr/state

-----------------------------------------------------收缩undo tbs-----------------------------------------------------1.检查数据库表空间占用空间情况:SQL> select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_data_files group by tablespace_name union all select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_temp_files group by tablespace_name order by GB;[@more@] TABLESPACE_NAME GB------------------------------ ----------SMPERP_MV_IDX .48

********************************************************** unix 命令详解 ********************************************************** acctcom 搜索并显示进程记帐文件 ar 维护可移植的档案或库 at 定时执行某个命令 awk 模式扫描和处理语言 batch 对at命令的调用 banner 显示大尺寸字符 basename,dirname 给出路径名的若干部分 bc 任意精度的算术运算语言 bdiff 大型diff bfs 大文件扫描程序[@more@]********************************************************** unix 命令详

无论什么编程语言都离不开条件判断。SHELL也不例外。 if list then do something here elif list then do another thing here else do something else here fi [@more@] 无论什么编程语言都离不开条件判断。SHELL也不例外。 if list then do something here elif list then do another thing here else do something else here fi EX1:#!/bin/shSYSTEM=`uname -s` #获取操作系统类型，我本地是linuxif [ $SYSTEM = "Linux&q

Oracle listener静态及动态注册总结 简介：这是Oracle listener静态及动态注册总结的详细页面，介绍了和oracle,ORACLE,Oracle,10g有关的知识。[@more@]Oracle listener静态及动态注册总结简介：这是Oracle listener静态及动态注册总结的详细页面，介绍了和oracle,ORACLE,Oracle,10g有关的知识。　　一、什么是注册?　　注册就是将数据库作为一个服务注册到监听程序。客户端不需要知道数据库名和实例名，只需要知道该数据库对外提供的服务名就可以申请连接到数据库。这个服务名可能与实例名一样，也有可能不一样。　　在数据库服务器启

DBA：Linux 面向 DBA 的 Linux Shell 脚本简介 作者：Casimir Saternos 学习一些在 Linux 上安装、运行和维护 Oracle 数据库所需的基本 bash shell 脚本。[@more@]DBA：Linux 面向 DBA 的 Linux Shell 脚本简介 作者：Casimir Saternos 学习一些在 Linux 上安装、运行和维护 Oracle 数据库所需的基本 bash shell 脚本。 本文相关下载： 示例脚本 Oracle 数据库 10g 2005 年 11 月发表 大约 7 年前，Oracle 发布了 Linux 上的第一个商业

1. hang analyze$ sqlplus / as sysdba;SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 20 14:32:13 2010Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> oradebug setmypid;Statement processed.SQL> oradebug hanganalyze 3;Hang Analysis in /orahome/admin/STCSMES/udump/stcsmes_ora_19360.trcSQL> exitDisconnected from

1.查找死锁进程SQL> select object_name,machine,s.sid,s.serial# from v$locked_object l,dba_objects o ,v$session swhere l.object_id　=　o.object_id and l.session_id=s.sid; 2.从ORACLE中KILL死锁进程SQL> alter system kill session '3278,24961';alter system kill session '3278,24961'*ERROR at line 1:ORA-00031: session marked for kill3.查找在OS中的SPIDSQL> select spid, osuser, s.program 2 from v$session s,v$process p 3 where s.paddr=p.addr and s.sid=&sid;Enter value for sid: 3278old 3:

$ vi init*.ora加入以下事件:*.event='1349 trace name context forever, level 1048576'[@more@]$ vi init*.ora加入以下事件:*.event='1349 trace name context forever, level 1048576' Starting Captures produces trace: Cannot Resolve Kxidsqn For Lcr [ID 331558.1] Modified 28-JUL-2009 Type PROBLEM Status PUBLISHED In this Document Symptoms Cause Solution Applies to: Oracle Server - Enterprise EditionThis problem can occur on any platform. Symptoms Whe

某些Oracle错误，并不是总是伴随着产生trace文件，这些错误，对Oracle来说并不是严重的错误，比如像ORA-01555这样的错误。 我们可以设置一个事件，在发生错误时，产生一个Trace文件，事件通常使用下面的命令格式： alter system set events '<error_number> trace name errorstack level <level>'; alter session set events '<error_number> trace name errorstack level <level>'; alter system set events '<error_number> trace name errorstack level <level>'; alter session

了解如何将用户抱怨与系统活动报告程序 (SAR) 关联，并使用 SAR 日志针对趋势建立性能基准。SAR 是系统管理员的理想工具。它以周期性的间隔捕获系统性能度量。[@more@]使用 SAR 进行简单的系统监视SAR 将帮助您确定性能瓶颈文档选项<tr valign="top"><td width="8"><img alt="" height="1" width="8" src="//www.ibm.com/i/c.gif"/></td><td width="16"><img alt="" width="16" height="16" src=

1、SHOW命令： 显示rman配置： RMAN> show all; 2、REPORT命令： 2.1、RMAN> report schema 报告目标数据库的物理结构; 2.2、RMAN>report need backup days=3; 报告最近3天没有被备份的数据文件； 2.3、RMAN> report need backup days 3 tablespace users; 在USERS表空间上3天未备份的数据文件; 2.4、RMAN> report need backup incremental 3; 报告恢复数据文件需要的增量备份个数超过3次的数据文件; 2.5、RMAN> report need backup redundancy 2 d

一、list常用命令总结备忘 list命令列出控制文件、RMAN恢复目录中备份信息， 是我们对所有可见的数据库备份文件的一个最直观的了解的方法 list incarnation; list backup summary; list backup of database summary; list backup of tablespace summary; list backup of datafile n,n summary; list archivelog all summary;[@more@]一、list常用命令总结备忘 list命令列出控制文件、RMAN恢复目录中备份信息， 是我们对所有可见的数据库备份文件的一个最直观的了解的方法 list incarnation;

$ rman target / catalog rman/rman@orabak$RMAN> list backup of archivelog from logseq 4444 until logseq 4465;RUN{ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';set archivelog destination to '/ora103/archive/temp';restore archivelog from logseq 4444 until logseq 4465;RELEASE CHANNEL ch00;} [@more@]

cat $ORACLE_HOME/sidlistFAB1STBAPPSTB---------------------------------------------#!/bin/sh export ORACLE_HOME=/ora104/10.2for SID in `cat $ORACLE_HOME/sidlist`doexport ORACLE_SID=${SID}$ORACLE_HOME/bin/rman <<EOFconnect target /crosscheck archivelog all;delete noprompt expired archivelog all;delete noprompt archivelog until time 'sysdate-1';exit;EOFdone [@more@]