精华内容
下载资源
问答
  • ORACLE检测语句大全

    2021-05-06 03:48:04
    查看数据库归档模式SELECT NAME,LOG_MODE,OPEN_MODE FROM V$DATABASE;查看回收站中对象SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE FROM RECYCLEBIN;清空回收站中对象PURGE RECYCLEBIN;还原回收站中的对象FLASHBACK ...

    查看数据库归档模式

    SELECT NAME,LOG_MODE,OPEN_MODE FROM V$DATABASE;

    查看回收站中对象

    SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE FROM RECYCLEBIN;

    清空回收站中对象

    PURGE RECYCLEBIN;

    还原回收站中的对象

    FLASHBACK TABLE "BIN$GOZUQZ6GS222JZDCCTFLHQ==$0" TO BEFORE DROP RENAME TO TEST;

    闪回误删除的表

    FLASHBACK TABLE AAA TO BEFORE DROP;

    闪回表中记录到某一时间点

    ALTER TABLE TEST

    ENABLE ROW MOVEMENT; FLASHBACK TABLE TEST TO TIMESTAMP TO_TIMESTAMP('2009-10-15 21:17:47','YYYY-MM-DD HH24:MI:SS'); 查看当前会话 SELECT

    SID,SERIAL#,USERNAME,PROGRAM,MACHINE,STATUS FROM V$SESSION;

    查看DDL锁

    SELECT * FROM DBA_DDL_LOCKS WHERE OWNER = 'BOOK';

    检查等待事件

    SELECT SID, http://doc.docsou.comERNAME, EVENT,

    WAIT_CLASS, T1.SQL_TEXT FROM V$SESSION A, V$SQLAREA T1 WHERE WAIT_CLASS <> 'Idle'

    AND A.SQL_ID = T1.SQL_ID;

    检查数据文件状态

    SELECT FILE_NAME,STATUS FROM DBA_DATA_FILES;

    检查表空间使用情况

    SELECT UPPER(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)",

    D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",

    F.TOTAL_BYTES "空闲空间(M)", F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME,

    ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(

    MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE

    GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(

    DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D

    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC;

    收缩表空间

    ALTER TABLESPCE TS_AJ_DATA COALESCE;

    增加表空间大小

    SELECT T.TABLESPACE_NAME, T.FILE_NAME, T.BYTES / 1024 / 1024 / 1024 FROM DBA_DATA_FILES T

    WHERE T.TABLESPACE_NAME = 'TS_AJ_DATA';

    ALTER TABLESPACE TS_AJ_DATA ADD DATAFILE '/DATA/TS_AJ_DATA05_10G.DBF' SIZE 10000M

    AUTOEXTEND OFF;

    检查不起作用的约束

    SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE, STATUS FROM

    DBA_CONSTRAINTS WHERE STATUS = 'DISABLE';

    检查发生坏块的数据库对象

    SELECT TABLESPACE_NAME, SEGMENT_TYPE, OWNER, SEGMENT_NAME

    FROM DBA_EXTENTS WHERE FILE_ID = < AFN > AND < BLOCK > BETWEEN BLOCK_ID AND

    BLOCK_ID + BLOCKS - 1;

    检查无效的数据库对象

    SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS ='INVALID';

    查看语句执行进度

    SELECT SE.SID, OPNAME, TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,

    ELAPSED_SECONDS ELAPSED, ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR)

    REMAIN_TIME, SQL_TEXT

    FROM V$SESSION_LONGOPS SL, V$SQLAREA SA,

    V$SESSION SE WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE AND SL.SID = SE.SID

    AND SOFAR != TOTALWORK ORDER BY START_TIM

    展开全文
  • oracle 检测脚本on linux

    2021-05-04 03:38:24
    ">>$LOG else oracle_sid_asm=`ps -ef|grep pmon|grep "_"|awk '{print $8}'|cut -d_ -f3|grep -v "+"|head -n 1`## get ORACLE_SID export ORACLE_SID=$oracle_sid_asm export ORACLE_HOME=$oracle_home export ...

    Normal

    0

    7.8 pt

    0

    2

    false

    false

    false

    MicrosoftInternetExplorer4

    #!/bin/bash

    # This script is used for monitoring

    database servers including the following items daily

    #1 Disk space usage;

    #2 Database space usage;

    #2.1 ASM_disgroup USAGE;

    #2.2 Data size status and tablespace usage and Top 5 wait event within 7

    days;

    #2.3 Database Error log info;

    #

    #Author:Ben

    #Create Date: 2011-02-18

    #Create monitor log directory:

    SCRIPT_BASE=/home/oracle/monitor

    if [ ! -d $SCRIPT_BASE ]; then

    mkdir$SCRIPT_BASE

    fi

    LOGDIR=$SCRIPT_BASE/log

    if [ ! -d $LOGDIR ]; then

    mkdir $LOGDIR

    fi

    DATE=`date +%Y-%m-%d`

    Time=`date +%X`

    LOG=$LOGDIR/$DATE.log

    Servername=`hostname`

    echo "">$LOG

    echo "========$Servername monitor log

    is as below=======">>$LOG

    echo "">>$LOG

    echo "========Monitor time is

    $DATE-$Time.======= ">>$LOG

    ####### 1 Check the filesystem usage and

    log if usage is over 90%###########

    echo ''>>$LOG

    echo '1 Filesystem usage:'>>$LOG

    Filesystem=`df -h|awk '{print $6}'|grep -v

    Filesystem|grep -v none|grep -v ":/"`

    for i in `df -h|awk '{print $5}'|grep -v

    Use|grep -v "/"|cut -d% -f1`

    do

    if

    [ $i -gt 90 ]; then

    echo "Warning:The usage of

    $Filesystem has been $i%">>$LOG

    fi

    done

    echo "`df

    -h`">>$LOG

    echo ''>>$LOG

    ####### 2 Check database space

    usage###########

    ###2.1 ASM###

    echo ''>>$LOG

    echo "2 Database space

    usage:">>$LOG

    oraclesidnum=`ps -ef|grep pmon|grep

    "_"|awk '{print $8}'|cut -d_ -f3|grep -v "+"|wc -l`## count the instance number

    oracle_home=`grep -v ^[#] /etc/oratab|grep

    -v '^$' |grep -v '+'|grep -v '*'|awk -F :'{print $2}'|head -n 1`## get

    ORACLE_HOME

    ####Check if use ASM

    asm=`ps -ef|grep pmon|grep

    "_"|awk '{print $8}'|cut -d_ -f3|grep"+"`

    if [ "$asm" = "" ];then

    echo "oracle storage is not

    using ASM!">>$LOG

    else

    oracle_sid_asm=`ps -ef|grep pmon|grep "_"|awk '{print $8}'|cut

    -d_ -f3|grep -v "+"|head -n 1`## get ORACLE_SID

    export ORACLE_SID=$oracle_sid_asm

    export ORACLE_HOME=$oracle_home

    export PATH=$oracle_home/bin:$PATH

    echo ''>>$LOG

    echo " 2.1 ASM_DISGROUP usage:">>$LOG

    sqlplus -s "/ as sysdba"

    accordingly

    spool '$LOG'app

    set linesize 180

    set feedback off

    select GROUP_NUMBER,NAME,TOTAL_MB/1024

    G,FREE_MB/1024 AG from V$ASM_DISKGROUP;

    spool off

    exit

    EOF

    fi

    ####Data size status and tablespace usage

    and Top 5 event ####

    echo ''>>$LOG

    echo " 2.2 Data size status and

    tablespace usage and top 5 event">>$LOG

    #Ora_version=`grep "Oracle

    Database" $LOG|awk '{print $3}|cut -dg -f1'`

    for i in `ps -ef|grep pmon|grep

    "_"|awk '{print $8}'|cut -d_ -f3|grep -v "+"`

    do

    export ORACLE_SID=$i

    export ORACLE_HOME=$oracle_home

    export PATH=$oracle_home/bin:$PATH

    Sdate=`date +%b%d%R`

    spoolfile=$LOGDIR/$Sdate.$i.txt

    echo " Instance $i:">>$LOG

    sqlplus -s "/ as sysdba"

    <>$spoolfile##Need to change accordingly

    spool '$LOG'app

    set linesize 180

    set feedback off

    col VALUE for a60

    col file_name for a60

    col event_name for a30

    show parameter db_name;

    SELECT VALUE FROM v$parameter WHERE name

    like '%background_dump_dest%';

    select * from v$flash_recovery_area_usage;

    select thread#,max(sequence#) from

    v$archived_log group by thread#;

    selectdbf.AllocateSize,

    de.UsedSize,

    (de.UsedSize/dbf.AllocateSize) * 100 Per_Used,

    (dbf.AllocateSize-de.UsedSize) FreeSize

    from

    (SELECT

    SUM(bytes)/1024/1024/1024 AllocateSize

    FROM dba_data_files files) dbf,

    (SELECT SUM(bytes)/1024/1024/1024 UsedSize

    FROM dba_extents) de;

    SELECT

    D.TABLESPACE_NAME,SPACE

    "SUM_SPACE(M)",

    SPACE-NVL(FREE_SPACE,0)

    "USED_SPACE(M)",

    FREE_SPACE "FREE_SPACE(M)",

    ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)

    "USED_RATE(%)"

    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

    WHERED.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

    order by TABLESPACE_NAME;

    SELECT

    D.TABLESPACE_NAME,SPACE1

    "Total",SPACE "SUM_SPACE(M)",

    SPACE-NVL(FREE_SPACE,1)

    "USED_SPACE(M)",

    SPACE1-(SPACE-NVL(FREE_SPACE,0))"FREE_SPACE(M)",

    ROUND((SPACE-NVL(FREE_SPACE,1))/SPACE1*100,2)

    "USED_RATE(%)"

    FROM

    (SELECT

    TABLESPACE_NAME,

    ROUND(SUM(decode(MAXBYTES,0,BYTES,MAXBYTES))/(1024*1024),2)

    SPACE1,

    ROUND(SUM(BYTES)/(1024*1024),2) SPACE

    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

    WHERED.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

    order by TABLESPACE_NAME;

    select Day, Event_name, Total_wait from (

    select day, event_name,

    sum(event_time_waited) total_wait,

    row_number() over (partition by day order

    by sum(event_time_waited) desc) rn from (

    SELECTto_date(to_char(begin_interval_time,'dd/mm/yyyy'),'dd/mm/yyyy')

    day,s.begin_interval_time, m.*

    FROM (SELECT ee.instance_number, ee.snap_id, ee.event_name,

    ROUND

    (ee.event_time_waited/1000000) event_time_waited,

    ee.total_waits,

    ROUND ((ee.event_time_waited *

    100)/et.total_time_waited,

    1

    ) pct,

    ROUND

    ((ee.event_time_waited/ee.total_waits)/1000

    ) avg_wait

    FROM (SELECT ee1.instance_number, ee1.snap_id, ee1.event_name,

    (ee1.time_waited_micro-ee2.time_waited_micro) event_time_waited,

    (ee1.total_waits-ee2.total_waits)

    total_waits

    FROM dba_hist_system_event

    ee1 JOIN dba_hist_system_event ee2

    ON ee1.snap_id =

    ee2.snap_id + 1

    AND ee1.instance_number

    = ee2.instance_number

    AND ee1.event_id =

    ee2.event_id

    AND ee1.wait_class_id

    <> 2723168908

    AND

    (ee1.time_waited_micro-ee2.time_waited_micro) > 0

    UNION

    SELECT st1.instance_number,

    st1.snap_id,

    st1.stat_name

    event_name,

    (st1.VALUE-st2.VALUE)

    event_time_waited,

    1 total_waits

    FROM dba_hist_sys_time_model

    st1 JOIN dba_hist_sys_time_model st2

    ON st1.instance_number

    = st2.instance_number

    AND st1.snap_id =

    st2.snap_id + 1

    AND st1.stat_id =

    st2.stat_id

    AND st1.stat_name = 'DB

    CPU'

    AND

    (st1.VALUE-st2.VALUE) > 0

    ) ee

    JOIN

    (SELECT et1.instance_number,

    et1.snap_id,

    (et1.VALUE-et2.VALUE)

    total_time_waited

    FROM

    dba_hist_sys_time_model et1 JOIN dba_hist_sys_time_model et2

    ON et1.snap_id =

    et2.snap_id + 1

    AND et1.instance_number

    = et2.instance_number

    AND et1.stat_id =

    et2.stat_id

    AND et1.stat_name = 'DB

    time'

    AND

    (et1.VALUE-et2.VALUE) > 0

    ) et

    ON ee.instance_number =

    et.instance_number

    AND ee.snap_id = et.snap_id

    ) m

    JOIN

    dba_hist_snapshot s ON m.snap_id = s.snap_id

    ) group by day ,event_name

    order by day desc, total_wait desc

    )where rn < 6;

    spool off

    exit

    dbsize

    echo ''>>$LOG

    ##############Check alert log######

    echo ''>>$LOG

    echo " 2.3 Error from alert.log

    file:">>$LOG

    echo " Error from Instance $i:

    ">>$LOG

    dbname=`grep 'db_name' $spoolfile|awk

    '{print $3}'`

    Bdumppath=`grep "/$dbname/"

    $spoolfile`

    cd $Bdumppath

    Alertfile=alert_$i.log

    Todate=`date +'%b'`##get abbreviated month name

    Errmsg=`grep "$Todate"

    $Alertfile|grep "ORA-" `

    if [ !-n $Errmsg ];then

    echo "There is no error in instance $i">>$LOG

    else

    echo "Error message is:">>$LOG

    echo "$Errmsg">>$LOG

    echo ''>>$LOG

    fi

    rm -rf $spoolfile

    done

    cd $LOGDIR

    find . -name '*.log' -mtime +7 -exec rm -rf

    {} ;

    #find . -name '*.txt' -mtime +3 -exec rm

    -rf {} ;

    #######Mail to Ben

    cat $LOG |mail -s "Seatadvisor:

    $Servername Daily Monitor Result"jerry.mao@bestit.com[@more@]

    展开全文
  • SQL> create table t1(id number,name varchar2(10));Table created.SQL> create table t2(id number,name varchar2(10));Table created.SQL> insert into t1 values(1,'wh');1 row created.SQL>...

    SQL> create table t1(id number,name varchar2(10));

    Table created.

    SQL> create table t2(id number,name varchar2(10));

    Table created.

    SQL> insert into t1 values(1,'wh');

    1 row created.

    SQL> insert into t1 values(2,'wp');

    1 row created.

    SQL> insert into t1 values(3,'wj');

    1 row created.

    SQL> insert into t2 values(1,'wh');

    1 row created.

    SQL> insert into t2 values(2,'wr');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select * from t1;

    ID NAME

    ---------- ----------

    1 wh

    2 wp

    3 wj

    SQL> select * from t2;

    ID NAME

    ---------- ----------

    1 wh

    2 wr

    SQL> (

    2  select id,name,count(*)

    3  from t1

    4  group by id,name

    5  minus

    6  select id,name,count(*)

    7  from t2

    8  group by id,name

    9  )

    10  union all

    11  (

    12  select id,name,count(*)

    13  from t2

    14  group by id,name

    15  minus

    16  select id,name,count(*)

    17  from t1

    18  group by id,name

    19  )

    20  ;

    ID NAME         COUNT(*)

    ---------- ---------- ----------

    2 wp                  1

    3 wj                  1

    2 wr                  1

    在这里你可能会举得select语句中的count(*)列可有可无,那么我们再向t2表中增加一条同样的记录试试看。

    SQL> insert into t2 values(1,'wh');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select * from t2;

    ID NAME

    ---------- ----------

    1 wh

    2 wr

    1 wh

    SQL> (

    2  select id,name,count(*)

    3  from t1

    4  group by id,name

    5  minus

    6  select id,name,count(*)

    7  from t2

    8  group by id,name

    9  )

    10  union all

    11  (

    12  select id,name,count(*)

    13  from t2

    14  group by id,name

    15  minus

    16  select id,name,count(*)

    17  from t1

    18  group by id,name

    19  )

    20  ;

    ID NAME         COUNT(*)

    ---------- ---------- ----------

    1 wh                  1

    2 wp                  1

    3 wj                  1

    1 wh                  2

    2 wr                  1

    这个时候就看出来区别了,如果t2中存在两条一样的数据,而t1中只存在相应的一条记录,而你又没有使用count(*),那么这一点不同就看不出来了。0b1331709591d260c1c78e86d0c51c18.png

    展开全文
  • oracle检测回滚剩余时间操作oracle数据库时,有一个大事务要回滚,非常慢,所以要看一下需要多少时间才能回滚完。 在网上找到这样的SQL语句:select * from v$transaction;但我执行的时候返回:ORA-00942: 表或视图...

    oracle检测回滚剩余时间

    操作oracle数据库时,有一个大事务要回滚,非常慢,所以要看一下需要多少时间才能回滚完。 在网上找到这样的SQL语句:

    select * from v$transaction;

    但我执行的时候返回:ORA-00942: 表或视图不存在

    原来是这样滴:普通用户下视图v$不可见,conn /as sysdba一下就好了。

    再次执行返回:未选定行

    下面找的的一段可以看回滚还要用多长时间。

    when transaction will finish rollback

    -------------------------------------------------------------------------------

    --

    -- Script: rolling_back.sql

    -- Purpose: to predict when transactions will finish rolling back

    -- For: 9.0+

    --

    -- Copyright: (c) Ixora Pty Ltd

    -- Author: Steve Adams

    --

    -------------------------------------------------------------------------------

    @save_sqlplus_settings

    set serveroutput on

    set feedback off

    prompt

    prompt Looking for transactions that are rolling back ...

    prompt

    declare

    cursor tx is

    select

    s.username,

    t.xidusn,

    t.xidslot,

    t.xidsqn,

    x.ktuxesiz

    from

    sys.x$ktuxe x,

    sys.v_$transaction t,

    sys.v_$session s

    where

    x.inst_id = userenv('Instance') and

    x.ktuxesta = 'ACTIVE' and

    x.ktuxesiz 1 and

    t.xidusn = x.ktuxeusn and

    t.xidslot = x.ktuxeslt and

    t.xidsqn = x.ktuxesqn and

    s.saddr = t.ses_addr;

    user_name varchar2(30);

    xid_usnnumber;

    xid_slot number;

    xid_sqnnumber;

    used_ublk1 number;

    used_ublk2 number;

    begin

    open tx;

    loop

    fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;

    exit when tx%notfound;

    if tx%rowcount = 1

    then

    sys.dbms_lock.sleep(10);

    end if;

    select

    sum(ktuxesiz)

    into

    used_ublk2

    from

    sys.x$ktuxe

    where

    inst_id = userenv('Instance') and

    ktuxeusn = xid_usn and

    ktuxeslt = xid_slot and

    ktuxesqn = xid_sqn and

    ktuxesta = 'ACTIVE';

    if used_ublk2 < used_ublk1

    then

    sys.dbms_output.put_line(

    user_name ||

    '''s transaction ' ||

    xid_usn || '.' ||

    xid_slot || '.' ||

    xid_sqn ||

    ' will finish rolling back at approximately ' ||

    to_char(

    sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,

    'HH24:MI:SS DD-MON-YYYY'

    )

    );

    end if;

    end loop;

    if user_name is null

    then

    sys.dbms_output.put_line('No transactions appear to be rolling back.');

    end if;

    end;

    /

    prompt

    @restore_sqlplus_settings

    展开全文
  • oracle检测回滚剩余时间操作oracle数据库时,有一个大事务要回滚,非常慢,所以要看一下需要多少时间才能回滚完。 在网上找到这样的SQL语句:select * from v$transaction;但我执行的时候返回:ORA-00942: 表或视图...
  • oracle检测锁,释放锁

    2021-05-01 04:43:14
    oracle检测锁,释放锁检测锁:SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME,B.OS_USER_NAME,B.PROCESS,B.LOCKED_MODE,C.MACHINE,C.STATUS,C.SERVER,C.SID,C.SERIAL#,C...
  • SQL> create table t1(id number,name varchar2(10));Table created.SQL> create table t2(id number,name varchar2(10));Table created.SQL> insert into t1 values(1,'wh');1 row created.SQL>...
  • Checking kernel parametersChecking for semmsl=250; found semmsl=250. PassedChecking for semmns=32000; found semmns=32000. PassedChecking for semopm=100; found semopm=32....<<&l...
  • # 以下描述来自Oracle 11gR2 Document1. 行链接The row is too large to fit into one data block when it is first inserted.In row chaining, Oracle Database stores the data for the row in ...
  • 检测oracle数据库坏块的办法:1、使用DBV(DB File Verify)工具;2、使用RMAN(Recovery Manager)工具;DBV(DB File Verify)工具:外部命令,物理介质数据结构完整性检查;只能用于数据文件(offline或online),不支持...
  • SELECT A.OWNER,A.SEGMENT_NAME,round((B.NUM_ROWS * B.AVG_ROW_LEN)/1024/1024/1024,2) LEN,round(SUM(A.BYTES)/1024/1024/1024,2) LEN_REAL,round(SUM(A.BYTES)/1024/1024/1024 -(B.NUM_ROWS * B.AVG_ROW_LEN)/102...
  • for SID in $(ps -fu oracle | grep pmon | grep -v grep | cut -d_ -f3- | sort); do export ORACLE_SID=$SID source ~/.bash_profile row=`sqlplus -silent '/ as sysdba' << EOSQL set pagesize 0 ...
  • 连接oracle数据库的几种方式: 语法: cx_Oracle.connect('username','pwd','IP/HOSTNAME:PORT/TNSNAME') import cx_Oracle db1=cx_Oracle.连接数据库的几种方式:语法:cx_Oracle.connect('username','pwd','IP/...
  • oracle 实现自增auto_increament在mysql中,实现字段自增,只用给他设置为auto_increase就ok了这是简单的傻子啊(但是多数据库共享,分布式计算就不适用了,呵呵)在oracle中没有这样便捷的功能,所以只能用sequence加...
  • oracle死锁问题一直困扰着我们,下面就教您一个oracle死锁的检查方法,如果您之前遇到过oracle死锁方面的问题,不妨一看。一、数据库死锁的现象程序在执行的过程中,点击确定或保存按钮,程序没有响应,也没有出现...
  • oracle之字段自增

    2021-04-30 07:42:34
    oracle中,当我们要让主键或者序号自增,然而oracle中没有像mysql那样有自增简单语句。但是我们可以通过序列与触发器来实现主键或者序列自增。创建表create table bank(userId varchar2(10),userName varchar2(10)...
  • Oracle程序包

    2021-05-02 05:43:48
    Oracle程序包程序包用于将逻辑相关的PL/SQL块或元素(变量、常量、过程、函数等)组织在一起,作为一个完整的单元存储在数据库中,用包名称来标识程序包。程序包类似于面向对象中的类。程序包由两部分构成:规范...
  • 本知识点仅适用于Oracle 9i以上的版本。查看表的数据变动情况请使用SQL语句:select * from user_tab_modifications;user_tab_modifications表的字段如下,从这个表中能够轻松找到表中做的修改,删除,增加记录的...
  • 我正在使用oracle linux ... el5。我正在尝试安装oracle 11g。我必须安装一些数据包用于预安装任务。例如:compat-libstdc ++ - 33-3.2.3我下载了rpm文件并试图安装,但是我得到了依赖性检查失败。我有一个更高版本...
  • oracle查看SQL性能

    2021-05-03 05:47:59
    --查看CPU消耗高的SQLselect a.sid,a.serial#,spid,status,substr(a.program,1,40) prog,a.machine,a.terminal,osuser,value/60/100 value,sq.sql_textfrom v$session a,v$process b,v$sesstat c,v$sqlarea sqwhere ...
  • 检测Oracle数据库状态

    2021-05-04 04:17:21
    检测Oracle数据库状态Oracle启动后,为检测Oracle服务的运行是否正常,可以执行ps命令检查相关的Oracle服务进程,也可以通过Oracle所提供的命令进行检查。具体介绍如下所示。(1)执行ps命令查看数据库进程情况。ps -...
  • Applies to:Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]Linux x86-64Linux x86Purposeoratop is a utility similar to the unix OS utility top which gives a overview o...
  • oracle功能检测sql语句

    2021-05-06 03:47:44
    1. 监控事例的等候select event,sum(decode(wait_Time,0,0,1)) "Prev",sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"from v$session_Waitgroup by event order by 4;2. 回滚段的争用情况select name, waits, ...
  • 16年关系型数据库管理,从oracle 9i 、10g、11g、12c到Mysql5.5、5.6、5.7、8.0 到TiDB获得3个OCP、2个OCM;运维路上不平坦,跌过不少坑、熬过许多夜。把工作笔记整理出来分享给大伙儿,希望帮到大家少走弯路、少...
  • Oracle检查命中率的SQL

    2021-05-03 05:53:43
    1. 慢冲区掷中率: 缓冲区命中率表现在不须要举行磁盘... Oracle SQL语句优化技术分析_2596 SQL Server 2000企业版安装_2835 Download_T-SQL_2558 SQL Server 2000数据库的事务日志文件过..._2920 oracle与sql比较_2682
  • Oracle中,有些表由于频繁的插入和删除数据,导致高水位过高,表的碎片也很高,如何判断呢?drop table test purge;create table test as select * from dba_objects;insert into test select * from test;insert ...
  • 很简单,当数据库重新启动时,Oracle只需从控制文件中读出检查点位置,检查点位置中记录有重做记录编号,根据此编号,Oracle可以很快的定位到日志文件中的重做记录n,它读出重做记录n中的重做数据,将用户的修改操作...
  • ORACLE_RAC测试报告.doc

    2021-05-03 03:03:38
    ORACLE_RAC测试报告目 录1测试配置21.1硬件配置21.2软件配置22测试项目22.1RAC节点或实例加入22.1.1正常关闭后第一实例启动22.1.2异常关闭后第一实例启动22.1.3第二节点启动32.1.4第二集群启动32.1.5第二实例启动...
  • Oracle轻量级实时监控工具-oratop适用于oracle单机、oracle RAC、oracle ADG支持的数据库版本:11gR2 (11.2.0.3, 11.2.0.4)12cR1 (12.1.0.1, 12.1.0.2, 12.2.0.1)18c、19c、20c ...本文包括两部分内容一:翻译官方...
  • Oracle阻塞(锁等待)查询主要查询v$lock、v$session视图。v$lock中的id1,id2为锁定的对象标识,block为阻塞数目。脚本show_blocker.sql相关代码如下:col block_msg for a80select c.terminal||' ('''||a.sid||','||c...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 96,686
精华内容 38,674
关键字:

oracle检测

友情链接: 200507120927.rar