oracle脚本创建大表等-优快云博客

本文介绍了使用SQL脚本批量插入大数据表的高效方法，并详细解释了ORACLE RAC（Real Application Clusters）在Linux环境下的裸设备绑定配置过程，包括使用raw设备和cluvfy工具进行验证。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

big_table.sql
-------------------------------------------------------------------------------------------
declare
    l_cnt number;
    l_rows number := &1;
begin
    insert /*+ append */
into big_table
select rownum, a.*
   from all_objects a
where rownum <= &1;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
loop
     insert /*+ APPEND */ into big_table
     select rownum+l_cnt,
            OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
            OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
            TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
       from big_table
      where rownum <= l_rows-l_cnt;
     l_cnt := l_cnt + sql%rowcount;
     commit;
end loop;
end;

_allow_resetlogs_corruption
_allow_terminal_recovery_corruption 修改成TURE强制打开数据库---风险

ORACLE_RAC_LINUX裸设备绑定的方法在/etc/.....的文件内容
KERNEL=="sdb1", WNER="root" GROUP="oinstall", MODE="0640"
KERNEL=="sdb2", WNER="crs" GROUP="oinstall", MODE="0640"
KERNEL=="sdb3", WNER="oracle" GROUP="oinstall", MODE="0660"
./runcluvfy.sh stage -pre crsinst -n srac1,srac2 –verbose

ACTION=="add",KERNEL=="sdb1",RUN+="/bin/raw /dev/raw/raw1 %N"
ACTION=="add",KERNEL=="sdb2",RUN+="/bin/raw /dev/raw/raw2 %N"
ACTION=="add",KERNEL=="sdb3",RUN+="/bin/raw /dev/raw/raw3 %N"
dd if=/dev/zero f=/dev/raw/raw1 bs=1M count=256
自己写的小脚本插入表数据
declare
    n_loop int;
begin
    n_loop := 0;
    loop
        insert into tabD values('123456789','hsinanweiesttest','87837847','abcde000vds','abcdefgsdfgsedssdev',
         'abcdefgsdfgdvdssdev','asssfgsdfgsvdssddv','abcssssssddev','abcd1123sdev','abc1sb4sdfvdssdfv',n_loop);
         commit;
        n_loop := n_loop + 1;
    exit when n_loop>2000000;
    End loop;
end;