创建同义词 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;
|