oracle 造数据

1、造大数据

  1、20W媒资（纯媒资） 200个产品（按次5 包月 各一半） 100个业务
 
  --每次查询前清空缓存：
10g以上：
alter system flush buffer_cache;
9i：
ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
-------创建媒资、业务、产品、栏目
--1、创建媒资
-- Create sequence
create sequence SEQ_IEPG_ASSET
minvalue 100000000
maxvalue 999999999999999
start with 100102000
increment by 1
cache 500;
create or replace procedure p_add_asset(x in number) is
begin
  for i in 1..x loop
  insert into T_IEPG_ASSET select SEQ_IEPG_ASSET.NEXTVAL resource_id,'天龙八部(南京)' asset_name,'' asset_en_name,'中文' ml_name,
'国语'  caption_name,'电影' asset_types ,'OR|18' origin_name,'英皇' issuer_name,1 track_type,18 chapters,
1 status,0 view_level,'' describ,'冯小刚' director,'李幼斌　萨日娜' leading_actor,''  screenwriter,
'中国金鸡奖'  prize,'N22221233;xiakexing(nanjing);XKX(NJ)' key_word ,'' remark,1  recommendation_level,
sysdate publish_date,1 user_recommend_level,sysdate create_time,sysdate effective_time,add_months(sysdate,15) expire_time,
'1' video_type,0 record_type,'xiakexing(nanjing)' letter_full,'XKX(NJ)' letter_abbreviate,'cptest001' providerid,
'N2222N1027N1051N0001' assetid,8 stb_suggested_price,12 ott_suggested_price,'Series' show_type,0 content_nibble_level_1,
0 content_nibble_level_2,  to_date('2011-12-28 17:11:56','yyyy-mm-dd hh24:mi:ss') stb_licensing_window_start,
to_date('2014-1-1','yyyy-mm-dd') stb_licensing_window_end ,to_date('2014-2-1','yyyy-mm-dd') ott_licensing_window_start,
to_date('2014-1-1','yyyy-mm-dd') ott_licensing_window_end,'王宝强最新作品,描述现代军营生活' summary_long ,
'王宝强最新作品,描述现代军营生活' summary_medium,'王宝强最新作品,描述现代军营生活' summary_short,
1 propagation_priority,100000  city_id,''  year,'P' grade_code,sysdate sys_date,0 media_type,0  mod_count,0 vod_count
from dual;
end loop;
end p_add_asset;
------------------------------------------------------------------------------------------------------------------------
truncate table t_po_resource;
truncate table t_iepg_goods;

delete from  t_iepg_asset;
select *from t_iepg_asset;
select *from t_iepg_asset_type;
select *from t_resource_pkg_map;
select *from t_iepg_asset_map_assettype;
select *from t_iepg_asset_file;
select *from t_res_cloumn_map;
select *from t_res_cloumn_online_task;
select *from t_po_resource;

select c.constraint_name,c.table_name,c.constraint_type,c.r_constraint_name    
from user_constraints c   
where c.constraint_name = 'FK_T_PO_RES_FK_IEPG_P_T_PRODOF' and c.constraint_type = 'R' ;

truncate table t_prodoffering;

select *from t_iepg_goods;
select *from t_iepg_asset;
select *from t_po_resource;
select *from t_city;

select *from t_prodoffering;

select *from t_iepg_asset where resource_id ='20130808';
select count(1) from t_iepg_asset;
select *from t_iepg_asset_file;
select *from t_Paragraph;---拆条媒资
---2、栏目
create sequence SEQ_Column_ID
minvalue 1
maxvalue 999999999999999
start with 50000
increment by 1
cache 500;
set feedback off
set define off
create or replace procedure p_add_column(x in number) is
begin
  for i in 1..x loop
      insert into t_column (COLUMN_ID, SITE_ID, TYPE, GOODS_TYPE, PARENT_ID, NAME, ALIAS, DESCRIBE, DISPLAY, ABSPATH, RANK, UPDATER, UPDATE_TIME, RESOURCE_TYPE, URL, ISWIDGET, TIPS, GROUP_ID, URL_TYPE, COLUMN_TYPE_CODE, ISSERVICE, CITY_ID, CITYCANMODIFY, SERVICE_CODE)
      values (SEQ_Column_ID.Nextval, 1, 1, -1, 649, 'bendipindao_huandeng', '本地频道', null, 1, '/幻灯版栏目/频道/本地频道', 2, 1, SYSDATE, -1, 'Utility.getEnv("STB_PORTAL_PATH") +"NVOD/publicChannel.htm?index=1"', 0, null, null, 2, '659', null, 100000, 1, null);

      insert into t_column (COLUMN_ID, SITE_ID, TYPE, GOODS_TYPE, PARENT_ID, NAME, ALIAS, DESCRIBE, DISPLAY, ABSPATH, RANK, UPDATER, UPDATE_TIME, RESOURCE_TYPE, URL, ISWIDGET, TIPS, GROUP_ID, URL_TYPE, COLUMN_TYPE_CODE, ISSERVICE, CITY_ID, CITYCANMODIFY, SERVICE_CODE)
      values (SEQ_Column_ID.Nextval, 1, 1, -1, 650, 'zhongyi_huandeng', '综艺', null, 1, '/幻灯版栏目/点播/综艺', 5, 1, SYSDATE, -1, '"http://hditv.jsamtv.com/epg/show.do?app=vpg&hd=y&content=arts&"+helpTailAddr3', 0, null, null, 3, '453', null, 100000, 1, null);
end loop;
end p_add_column;
select count(1) from t_iepg_asset;
select *from t_column;
delete from t_column;


----t_column的外键 site_id 为t_iepg_site的主键
select *from t_iepg_site;
select *from t_column c,t_iepg_site s
where c.site_id =  s.site_id;
select *from t_column;
select SEQ_Column_ID.Nextval from dual;

---脚本中含有&加上下面set define off 去掉
set feedback off
set define off
prompt Deleting T_COLUMN...
delete from T_COLUMN;
commit;
prompt Loading T_COLUMN...
insert into T_COLUMN (COLUMN_ID, SITE_ID, TYPE, GOODS_TYPE, PARENT_ID, NAME, ALIAS, DESCRIBE, DISPLAY, ABSPATH, RANK, UPDATER, UPDATE_TIME, RESOURCE_TYPE, URL, ISWIDGET, TIPS, GROUP_ID, URL_TYPE, CITY_ID, CITYCANMODIFY)
values (591, 1, 1, -1, 0, 'resource', 'MSCP栏目', 'MSCP栏目', 1, '/MSCP栏目', 1, 1, to_date('23-07-2011 15:44:27', 'dd-mm-yyyy hh24:mi:ss'), -1, null, 0, null, null, null, 100000, 1);
commit;
update t_column t set city_id=100000;
commit;
prompt 341 records loaded
set feedback on
set define on
prompt Done.
--------------------3、业务
select * from T_PRODOFFERING t for update;
delete from T_PRODOFFERING;
select seq_prodoffering_id.nextval from dual;
-- Create sequence
create sequence SEQ_PRODOFFERING_ID
minvalue 1
maxvalue 999999999999999
start with 51500
increment by 1
cache 500;
create sequence SEQ_PRODOFFERING_CODE
minvalue 1
maxvalue 999999999999999
start with 100
increment by 1
cache 500;
create or replace procedure p_add_service(x in number) is
begin
  for i in 1..x loop
     insert into T_PRODOFFERING (prodoffering_id, prodoffering_code, prodoffering_name, sp_code, sp_name, remark, status, runtime, access_url, servicetype, platform, city_id, grade_code, modify_date)
     values (seq_prodoffering_id.nextval, '40Auto_S'||SEQ_PRODOFFERING_CODE.nextval, '10元Service', '1', '123', 'AutoCreate4.0YuanAutoService', 0, null, 'http://www.coship.com', 1, '1', 100000, 'P', to_date('15-08-2013 15:35:26', 'dd-mm-yyyy hh24:mi:ss'));
  end loop;
end p_add_service;

---媒资 业务关联表
select count(1) from t_iepg_asset;
select count(1) from t_column;
select count(1) from t_po_resource ;
SELECT p.prodoffering_code 业务编码,a.resource_id 媒资ID,a.asset_name 媒资名,g.goods_code 产品代码,
g.goods_name 产品名称,g.goods_price,g.charge_mode,g.price_desc,g.status
FROM t_prodoffering p,t_iepg_asset a,t_po_resource r,t_iepg_goods g
WHERE p.prodoffering_code = r.prodoffering_code
AND r.resource_id = a.resource_id
AND p.prodoffering_code = g.prodoffering_code;
select *from t_iepg_asset;
select resource_id from t_iepg_asset;
------增加媒资业务关联数据的存储过程
select *from t_prodoffering;
select resource_id from (
 select resource_id,rownum rn from t_iepg_asset
 order by resource_id asc)
 where rn<=7*1000 and rn >6*1000;   100102000
                                     100102399
 
 select firmcode,balance from (
                 select   rownum   rn,t.firmcode,t.balance
                 from FIRMBALANCE_TAB t
                 order by balance desc
          ) tab
          where tab.rn >0 and tab.rn < 11;  
create sequence SEQ_po_resource_int
minvalue 0
maxvalue 999999999999999
start with 0
increment by 1
cache 500;

select SEQ_po_resource_int.Nextval* 1000 from dual;

create or replace procedure p_add_po_resource
as
var_i int :=0;
var_x VARCHAR2(64);
var_y VARCHAR2(64);
 ---媒资ID 媒资每1千条1千条的造
cursor cursor2 is select prodoffering_id,prodoffering_code from t_prodoffering;---业务ID
 begin
  for var_x in cursor2 loop ---yw
      for var_y in (select resource_id from (
         select resource_id,rownum rn from t_iepg_asset
          order by resource_id asc)
           where rn<=(var_i+1)* 2000  
           and rn >var_i* 2000 )   loop
      begin
        insert into t_po_resource(id,prodoffering_id,resource_id,resource_type,resource_name,prodoffering_code,status)
        values(SEQ_RESOURCE_ID.NEXTVAL,var_x.prodoffering_id,var_y.resource_id,1,'天龙八部(南京)',var_x.prodoffering_code,1 );
      end;
  end loop;
  var_i := var_i+1;
  end loop;
end p_add_po_resource;

delete from t_po_resource;

select SEQ_RESOURCE_ID.NEXTVAL from dual;
FK_T_PO_RES_FK_IEPG_P_T_PRODOF

SELECT SEQ_RESOURCE_ID.NEXTVAL AS ID FROM DUAL;

select *from t_po_resource; 1个业务对应 1000个媒资 prodoffering_id 1:1000 resource_id

----4、产品
select count(1) from t_iepg_asset;
select count(1) from t_column;
select count(1) from t_po_resource ;
select count(1) from t_iepg_goods;
select * from t_iepg_goods;
create sequence SEQ_IEPG_GOODS_code
minvalue 1
maxvalue 999999999999999
start with 50500
increment by 1
cache 500;

create or replace procedure p_add_goods(x in number) is
var_i VARCHAR2(64);

cursor cursor1 is select prodoffering_code from t_prodoffering;---业务ID
begin
  for var_i in cursor1 loop
      -------包月
      insert into t_iepg_goods (GOODS_ID, GOODS_CODE, GOODS_NAME, GOODS_PRICE, CHARGE_MODE, PRICE_DESC, REGION, STATUS, REMARK, BEGIN_TIME, END_TIME, MODIFY_DATE, PRODOFFERING_CODE, CHARGE_TERM, CHARGE_TERM_UNIT, GOODS_TYPE, PPV_ID, CITY_ID, PPVNAME, PACKAGEID, PLATFORM, GRADE_CODE)
      values (seq_iepg_goods.NEXTVAL, '10Auto_CKQ15'||SEQ_IEPG_GOODS_code.Nextval, '10.0元产品', 1000, 1, '包月产品', '1', 0, 'portalMSb8测试包月产品', to_date('14-06-2013', 'dd-mm-yyyy'), to_date('29-03-2287 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-08-2013 17:09:29', 'dd-mm-yyyy hh24:mi:ss'), var_i.prodoffering_code, 1, 3, 0, '100Auto_Pr1', 100000, '10.0元价格', '', '1', 'P');
      -------按次
      insert into t_iepg_goods (GOODS_ID, GOODS_CODE, GOODS_NAME, GOODS_PRICE, CHARGE_MODE, PRICE_DESC, REGION, STATUS, REMARK, BEGIN_TIME, END_TIME, MODIFY_DATE, PRODOFFERING_CODE, CHARGE_TERM, CHARGE_TERM_UNIT, GOODS_TYPE, PPV_ID, CITY_ID, PPVNAME, PACKAGEID, PLATFORM, GRADE_CODE)
      values (seq_iepg_goods.NEXTVAL, '10Auto_CKQ15'||SEQ_IEPG_GOODS_code.Nextval, '5.0元产品', 500, 1, '包月产品', '1', 0, 'portalMSb8测试按次产品', to_date('14-06-2013', 'dd-mm-yyyy'), to_date('29-03-2287 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-08-2013 17:09:29', 'dd-mm-yyyy hh24:mi:ss'), var_i.prodoffering_code, 24, 1, 0, '100Auto_Pr2', 100000, '5.0元价格', '', '1', 'P');
     
      end loop;
end p_add_goods;
--待上架媒资上架到栏目上在该表插入数据
select *from t_res_cloumn_map;---影视栏目上架对应关系表
delete from t_res_cloumn_map;

select *from t_column_po;---栏目跟产品

SELECT *FROM t_prodoffering; ---业务
SELECT *FROM t_iepg_asset;----媒资 （资源）
SELECT *FROM t_po_resource; ----产品和资源对应关系表
select *from t_iepg_goods;

select from

select count(1) from t_iepg_asset;
select count(1) from t_column;
select count(1) from t_po_resource ;
select count(1) from t_iepg_goods;
select count(1) from t_prodoffering;

select *from t_iepg_asset;
select count(1) from t_column;
select count(1) from t_po_resource ;
select *from t_iepg_goods;

select *from t_iepg_asset;
select *from t_iepg_asset;
select *from t_iepg_asset;
select *from t_iepg_asset;
select *from t_iepg_asset;
select count(1) from (
SELECT p.prodoffering_code 业务编码,a.resource_id 媒资ID,a.asset_name 媒资名,g.goods_code 产品代码,
g.goods_name 产品名称,g.goods_price,g.charge_mode,g.price_desc,g.status
FROM t_prodoffering p,t_iepg_asset a,t_po_resource r,t_iepg_goods g
WHERE p.prodoffering_code = r.prodoffering_code
AND r.resource_id = a.resource_id
AND p.prodoffering_code = g.prodoffering_code);



1. 查看被锁的表
SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username,
       b.os_user_name
  FROM v$process p, v$session a, v$locked_object b, all_objects c
 WHERE p.addr = a.paddr AND a.process = b.process
       AND c.object_id = b.object_id
       
2. 查看是哪个进程锁的
SELECT sid, serial#, username, osuser FROM v$session  where osuser = 'tangpj'

3. 杀掉这个进程  alter system kill session 'sid,serial#';