创建同义词
conn
tt/bitservice@sw00058
;
grant select on vw_rightpeople to account; //先授权
conn
account/bitservice@sw00058
;
create or replace synonym vw_rightpeople for tt.vw_rightpeople; //创建同义词
exp1:
connect
pubr/bitservice@account
;
grant select on orgpeople to account;
connect
account/bitservice@account
;
create or replace synonym orgpeople for pubr.orgpeople;
exp2:
conn pubr/bitservice;
grant select on allactive to archive;
conn archive/bitservice;
create or replace synonym allactive for pubr.allactive;
创建索引
conn
pubr/bitservice@sw00058
;
create index pubr.idx_taxfeedetail_tfdefineid on pubr.taxfeedetail (taxfeedefineid)
创建序列号sequence
create sequence seq_account_taxfee
创建用户
-- Create the user
create user AP identified by;
-- Grant/Revoke object privileges
grant select, insert, update, delete, references, alter, index on RECEIVETAX to AP; //授权
创建角色
-- Create the role
create role CONNECT;
-- Grant/Revoke system privileges
grant alter session to CONNECT; //角色授权
grant create cluster to CONNECT;
grant create database link to CONNECT;
grant create sequence to CONNECT;
grant create session to CONNECT;
grant create synonym to CONNECT;
grant create table to CONNECT;
grant create view to CONNECT;
创建存贮地址
-- Create directory
create or replace directory MEDIA_DIR
as 'E:\oracle\ora92\demo\schema\product_media\';
创建library
-- Create library
create or replace library SYS.DBMS_XMV_LIB
as '';
创建触发器
create or replace trigger tri_Invoice_biud
before insert or update of realtypeid,flowid,applytime,mastatus,bastatus or delete on Invoice for each row
declare
l_cnt number(8);
begin
.......
end;
创建数据类型
types
type bodies
创建包
create or replace package account_taxfeedata as
type p_receiveTaxIDs is table of account_TaxFee.receiveTaxID%type index by binary_integer;
type p_goodsids is table of account_TaxFee.goodsid%type index by binary_integer;
v_receiveTaxIDs p_receiveTaxIDs;
v_goodsids p_goodsids;
v_NumEntries binary_integer := 0;
end account_taxfeedata;
创建过程
create or replace procedure buildTable_afterSelect(pmMainTable in varchar2,pmStanID in number,pmRealTypeID in number,pmActID in number,pmBldroomID in number)
as
l_category number(8);
l_parentid realotherright.parentid%type;
begin
end;
创建函数
create or replace function func_syncTaxfeeData(pOpertype number,pAccid number) return varchar2 is
Result varchar2(2000);
l_url varchar2(2000);
l_uid varchar2(20);
l_pwd varchar2(100);
l_ret varchar2(2000);
begin
return(Result);
end func_syncTaxfeeData;
给sequence创建synonym，并授予select权限
connect
用户1/密码@实例名
;
grant select on seq_ma_realsalepermit_multiid to archive;
connect 用户2/密码@实例名;
create or replace synonym seq_ma_realsalepermit_multiid for aestate.seq_ma_realsalepermit_multiid;
创建job
select job,what,last_date,next_date,next_sec,BROKEN,INTERVAL,FAILURES,INSTANCE from user_jobs;
若job不存在（看what是否是pck_report_getreportdata.proc_getreport_forjobexec; proc_CreateAllStreportData(1)），建议通过以下语句创建job：
variable testjobid number;
-- 创建job
begin
dbms_job.submit(:testjobid,'proc_CreateAllStreportData(1);',trunc(sysdate)+18/24,'trunc(sysdate)+1+18/24');
end;
/
show err;
