数据库相关整理

来源：互联网发布：keep软件编辑：程序博客网时间：2024/04/30 23:43

PLSQL Developer创建创建表空间和用户：（以linux为例，如果是windows则修改盘符）
1、打开一个PLSQL窗口
2、File -> New -> Command Window
3、将下面的脚本运行即可

建表空间

DROP TABLESPACE WEBSMS_DATA INCLUDING CONTENTSAND DATAFILES CASCADE CONSTRAINTS;

CREATE TABLESPACE WEBSMS_DATA

DATAFILE '/dev/raw/raw5' SIZE 1800M REUSE AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED,

'/dev/raw/raw6' SIZE 1800M REUSE AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED,

'/dev/raw/raw7' SIZE 1800M REUSE AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED,

'/dev/raw/raw8' SIZE 1800M REUSE AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED

EXTENT MANAGEMENTLOCAL UNIFORM SIZE 128K

SEGMENT SPACE MANAGEMENT AUTO;

DROP TABLESPACE WEBSMS_TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

CREATE TEMPORARY TABLESPACE WEBSMS_TEMP

TEMPFILE '/dev/raw/raw9' SIZE 180M REUSE AUTOEXTEND ON NEXT 52M MAXSIZE UNLIMITED,

'/dev/raw/raw10' SIZE 180M REUSE AUTOEXTEND ON NEXT 52M MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

DROP TABLESPACE WEBSMS_MESSAGE INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

CREATE TABLESPACE WEBSMS_MESSAGE

DATAFILE '/dev/raw/raw11' SIZE 360M REUSE AUTOEXTEND ON NEXT 52M MAXSIZE UNLIMITED,

'/dev/raw/raw12' SIZE 360M REUSE AUTOEXTEND ON NEXT 52M MAXSIZE UNLIMITED,

'/dev/raw/raw13' SIZE 360M REUSE AUTOEXTEND ON NEXT 52M MAXSIZE UNLIMITED,

'/dev/raw/raw14' SIZE 360M REUSE AUTOEXTEND ON NEXT 52M MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K

SEGMENT SPACE MANAGEMENT AUTO;

DROP TABLESPACE WEBSMS_MESSAGE_BAK INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

CREATE TABLESPACE WEBSMS_MESSAGE_BAK

DATAFILE '/dev/raw/raw15' SIZE 1800M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED,

'/dev/raw/raw16' SIZE 1800M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED,

'/dev/raw/raw17' SIZE 1800M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED,

'/dev/raw/raw18' SIZE 1800M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K

SEGMENT SPACE MANAGEMENT AUTO;

DROP TABLESPACE WEBSMS_INDEX INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

CREATE TABLESPACE WEBSMS_INDEX

DATAFILE '/dev/raw/raw1' SIZE 480M REUSE AUTOEXTEND ON NEXT 52M MAXSIZE UNLIMITED,

'/dev/raw/raw2' SIZE 480M REUSE AUTOEXTEND ON NEXT 52M MAXSIZE UNLIMITED,

'/dev/raw/raw3' SIZE 480M REUSE AUTOEXTEND ON NEXT 52M MAXSIZE UNLIMITED,

'/dev/raw/raw4' SIZE 480M REUSE AUTOEXTEND ON NEXT 52M MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K

SEGMENT SPACE MANAGEMENT AUTO;

ALTER SYSTEM SET OPTIMIZER_MODE=FIRST_ROWS SCOPE=SPFILE;

ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

--ALTER SYSTEM SET DB_CACHE_SIZE=1536M SCOPE=both;

--ALTER SYSTEM SET shared_pool_size=300M SCOPE=both;

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

如果是在windows下建表空间，可将目录改为:/home/oracle/websms1/目录下

创建用户

1）第一种创建方法：

DROPUSER websmsCASCADE;

CREATEUSER websms IDENTIFIEDBY websms

DEFAULT TABLESPACE WEBSMS_DATA

TEMPORARY TABLESPACE WEBSMS_TEMP

QUOTA UNLIMITED ON WEBSMS_DATA

QUOTA UNLIMITED ON WEBSMS_INDEX

QUOTA UNLIMITED ON WEBSMS_MESSAGE

QUOTA UNLIMITED ON WEBSMS_MESSAGE_BAK;

GRANT CONNECT TO websms;

GRANT DBA TO websms;

GRANTCREATEANYTABLE TO websms;

GRANT RESOURCE TO websms;

GRANT EXP_FULL_DATABASE TO websms;

GRANT IMP_FULL_DATABASE TO websms;

2）第二中创建方法：

DROP USER websms_wjt CASCADE;

CREATE USER websms_wjt IDENTIFIED BY websms

DEFAULT TABLESPACE WEBSMS_DATA

TEMPORARY TABLESPACE WEBSMS_TEMP

QUOTA UNLIMITED ON WEBSMS_DATA

QUOTA UNLIMITED ON WEBSMS_INDEX

QUOTA UNLIMITED ON WEBSMS_MESSAGE

QUOTA UNLIMITED ON WEBSMS_MESSAGE_BAK;

GRANT CONNECT TO websms_wjt;

DROP ROLE websms_role CASCADE;

CREATE ROLE websms_role;

GRANT

CREATE SESSION,ALTER SESSION,

CREATE CLUSTER,ALTER ANY CLUSTER,DROP ANY CLUSTER,

CREATE DATABASE LINK,DROP PUBLIC DATABASE LINK,

CREATE ROLE,ALTER ANY ROLE,DROP ANY ROLE,

CREATE VIEW,DROP ANY VIEW,

CREATE SEQUENCE,ALTER ANY SEQUENCE,DROP ANY SEQUENCE,

CREATE ANY INDEX,ALTER ANY INDEX,DROP ANY INDEX,

CREATE SNAPSHOT,ALTER ANY SNAPSHOT,DROP ANY SNAPSHOT,

CREATE PUBLIC SYNONYM,DROP PUBLIC SYNONYM,

CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,DELETE ANY TABLE,

CREATE TRIGGER,DROP ANY TRIGGER,

CREATE TABLESPACE,ALTER TABLESPACE,DROP TABLESPACE,

CREATE ANY PROCEDURE,ALTER ANY PROCEDURE,DROP ANY PROCEDURE,

CREATE ANY TYPE,EXECUTE ANY TYPE,DROP ANY TYPE,

EXECUTE ANY PROCEDURE,FORCE ANY TRANSACTION,

CREATE ROLLBACK SEGMENT,ALTER ROLLBACK SEGMENT,

DROP ROLLBACK SEGMENT,SELECT ANY DICTIONARY

TO websms_role;

GRANT websms_role to websms_wjt;

GRANT UNLIMITED TABLESPACE TO websms_wjt;

--GRANT DBA TO websms_wjt;

--GRANT CREATE ANY TABLE TO websms_wjt;

GRANT RESOURCE TO websms_wjt;

GRANT EXP_FULL_DATABASE TO websms_wjt;

GRANT IMP_FULL_DATABASE TO websms_wjt;

建表

DROPTABLE T_WEBSMS_STATISTICS CASCADE CONSTRAINTS;

--创建统计表，用于对集团用户进行统计

CREATETABLE T_WEBSMS_STATISTICS

(

CORPID VARCHAR2(32) NOT NULL,

COUNTS NUMBER(9) DEFAULT 0,

USERTYPE NUMBER(1) DEFAULT 0,

TIMES DATEDEFAULT SYSDATE

)

partition by range(TIMES)

(

partition T_WEBSMS_STATISTICS20110701values less than (to_date('20110701','yyyymmdd'))

)

TABLESPACE WEBSMS_MESSAGE_BAK

PCTUSED 1

PCTFREE 1

INITRANS 1

MAXTRANS 255

STORAGE

(

INITIAL 50M

MINEXTENTS 1

MAXEXTENTS 2147483645

PCTINCREASE 0

)

LOGGING

NOCACHE

NOPARALLEL

NOMONITORING;

--为统计表添加描述

COMMENT ONTABLE T_WEBSMS_STATISTICS IS'集团短号用户数统计表';

COMMENT ONCOLUMN T_WEBSMS_STATISTICS.CORPID IS '集团ID';

COMMENT ONCOLUMN T_WEBSMS_STATISTICS.COUNTS IS '短号用户数';

COMMENT ONCOLUMN T_WEBSMS_STATISTICS.USERTYPE IS 'V网类型:0集团,1家庭';

COMMENT ONCOLUMN T_WEBSMS_STATISTICS.TIMES IS '统计时间';

建表索引

-- 为统计表创建索引

CREATEINDEX IX_T_WEBSMS_STATISTICS_CORPIDON T_WEBSMS_STATISTICS(CORPID)

LOGGING

LOCAL

TABLESPACE WEBSMS_INDEX

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE

(

INITIAL 64K

MINEXTENTS 1

MAXEXTENTS 2147483645

PCTINCREASE 0

FREELISTS 1

FREELIST GROUPS 1

BUFFER_POOL DEFAULT

)

NOPARALLEL;

创建序列

DROP SEQUENCE SEQ_ADMININFO_ID;

CREATE SEQUENCE SEQ_ADMININFO_ID INCREMENTBY 1 START WITH 2 NOCYCLE CACHE 20 NOORDER;

建包

CREATEOR REPLACE PACKAGE webSM_PackageAS

TYPE t_RetDataSetIS REF CURSOR;

END webSM_Package;

创建方法

--,拆分字符串的方法

CREATEORREPLACE FUNCTION splitstr(p_stringIN VARCHAR2, p_delimiterIN VARCHAR2)

RETURN str_split

PIPELINED

v_length NUMBER := LENGTH(p_string);

v_start NUMBER := 1;

v_index NUMBER;

BEGIN

WHILE(v_start <= v_length)

LOOP

v_index := INSTR(p_string, p_delimiter, v_start);

IF v_index = 0

THEN

PIPE ROW(SUBSTR(p_string, v_start));

v_start := v_length + 1;

ELSE

PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));

v_start := v_index + 1;

END IF;

END LOOP;

RETURN;

END splitstr;

建存储过程

CREATEOR REPLACEPROCEDURE PROC_GATHER_MEMBER

v_date VARCHAR2(12);

backupdate VARCHAR2(30);

v_datedrop VARCHAR2(12);

str VARCHAR2(2000);

v_yy VARCHAR2(30);

v_yy2 VARCHAR2(30);

v_count NUMBER;

BEGIN

SELECT to_char(SYSDATE,'yyyyMMdd') INTO v_dateFROM dual;

SELECT to_char(SYSDATE+1,'yyyyMMdd') INTO backupdateFROM dual;

SELECT to_char(SYSDATE-265,'yyyyMMdd') INTO v_datedropFROM dual;

v_yy := 'yyyyMMdd';

v_yy2 := '''' || v_yy || '''';

--- 增加分区

BEGIN

str := 'alter table T_WEBSMS_STATISTICS add partition T_WEBSMS_STATISTICS' || v_date ||

' values less than (to_date(' || '''' || backupdate ||'''' || ',' || v_yy2 ||

')) tablespace WEBSMS_MESSAGE_BAK';

EXECUTE IMMEDIATE str;

EXCEPTION

WHEN OTHERS THEN

null;

END;

--- 删除初始化分区

SELECTCOUNT(*)INTO v_countFROM user_tab_partitionsWHERE partition_name ='T_WEBSMS_STATISTICS20110701'and table_name ='T_WEBSMS_STATISTICS';

IF v_count > 0 THEN

BEGIN

str := 'alter table T_WEBSMS_STATISTICS drop partition T_WEBSMS_STATISTICS20110701';

EXECUTE IMMEDIATE str;

EXCEPTION

WHEN OTHERS THEN

null;

END;

END IF;

--- 定时删除超过265天的数据

BEGIN

str := 'alter table T_WEBSMS_STATISTICS drop partition T_WEBSMS_STATISTICS' || v_datedrop;

EXECUTE IMMEDIATE str;

EXCEPTION

WHEN OTHERS THEN

null;

END;

--- 查询集团表，统计用户数到统计表

INSERTINTO T_WEBSMS_STATISTICS (CORPID)

SELECT CORPIDFROM CORPINFOWHERE CORPID

NOTIN (SELECT CORPIDFROM CORPMEMBERGROUPBY CORPID);

COMMIT;

insertinto T_WEBSMS_STATISTICS (CORPID, COUNTS)

SELECT CORPID,count(MEMBERID)FROM CORPMEMBERGROUPby CORPID;

COMMIT;

END PROC_GATHER_MEMBER;

建触发器

CREATEOR REPLACE TRIGGER TRIG_ADMININFO_UPDATE

after UPDATEon admininfo

for each row

DECLARE

-- local variables here

BEGIN

UPDATE CORPINFO

SET MANAGERNAME = :NEW.USERNAME,

MANAGERTELEPHONE = :NEW.TELEPHONE

WHERE MANAGERID = :OLD.ADMINID;

END TRIG_ADMININFO_AFT_DEL;

建视图

createor replaceview view_messageinfo as

select *from messageinfo o where o.sendtime >= trunc(sysdate);

建job

variable jobnu number;

begin

sys.dbms_job.submit(job => :jobnu,

what =>'

begin

PROC_MESSAGE_BACKNEW3;

end;',

next_date => trunc(SYSDATE + 1,'dd') + 0/24,

interval =>'trunc(sysdate + 1, ''dd'') + 0/24');

commit;

end;

建link

drop database link townee_link;

create database link townee_link

connect to L02109 identifiedby websms using'

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.71.122.153)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = oraussd)

)

)';

执行脚本

prompt CREATEING TABLE ..........................

@@01_tables.sql

prompt REBUILD UPDATE.......................

@@02_sequences.sql

prompt INSERTING INITIAL DATA ...................

@@03_packages.sql

prompt CREATEING PROCEDURE.......................

@@10_types.sql

prompt CREATEING PROCEDURE.......................

@@04_functions.sql

prompt REBUILD UPDATE.......................

@@05_procedures.sql

prompt REBUILD UPDATE.......................

@@06_triggers.sql

prompt REBUILD UPDATE.......................

@@07_views.sql

prompt REBUILD UPDATE.......................

@@08_initdata.sql

prompt REBUILD UPDATE.......................

@@09_jobs.sql

0 0