orcale数据库新建库的常用sql

– start: 解决数据库用户密码过期问题 – – dba 用户(如： sys , system 等)登录数据库, 查看指定概要文件（如 DEFAULT）的密码有效期时间（oracle 默认是 180天） select * from dba_profiles s where s.PROFILE = ‘DEFAULT’ and resource_name = ‘PASSWORD_LIFE_TIME’;

– 将密码有效期由默认的 180天修改成 ‘无限制’ alter PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

– end: 解决数据库用户密码过期问题 –

– 设置对空表分配segment，解决空的数据库表导出错误， 注意：该参数只对设置该参数后的表产生作用 – alter system set deferred_segment_creation=false;

– 查看 对空表分配segment 参数值 – show parameter deferred_segment_creation;

– start: 修改数据库连接数量 – 查看数据库连接数量 select count(*) from v$session;--当前的连接数selectvaluefromv$parameter where name=‘processes’; – 数据库允许的最大连接数量

– 修改最大连接数量 – sessions 与 processes 是： sessions = 1.1 * processes + 5; – 修改 sessions 和 processes 后必须重启oracle 服务 alter system set processes=4000 scope=spfile; alter system set sessions=4405 scope=spfile;

– end: 修改数据库连接数量

— 创建新的表空间、数据库及赋权脚本 , 根据工程名称 替换 fis – create temporary tablespace fis_tbs_temp tempfile ‘d:/app/Administrator/oradata/orcl/fis_tbs_temp.dbf’ size 50m autoextend on next 50m maxsize 20480m extent management local;

create tablespace fis_tbs logging datafile ‘d:/app/Administrator/oradata/orcl/fis_tbs.dbf’ size 50m autoextend on next 50m maxsize 20480m extent management local;

– 创建用户 并指定表空间 create user fis identified by 123456 default tablespace fis_tbs temporary tablespace fis_tbs_temp profile DEFAULT ACCOUNT UNLOCK ;

– 给用户赋权 connect , dba , resource – grant connect to fis; grant dba to fis; grant resource to fis; ALTER USER fis DEFAULT ROLE “RESOURCE”,“CONNECT”,“DBA”; – 如果是 mup平台第一次自动生成数据库，则删除 DBA – Grant/Revoke system privileges grant create any sequence to fis; grant create any table to fis; grant create any trigger to fis; grant create any view to fis; grant unlimited tablespace to fis; GRANT EXPORT FULL DATABASE TO fis ; GRANT IMPORT FULL DATABASE TO fis ;

– start: 导入用户表空间 文件到不同的表空间，修改用户权限后导入，如果导入与导出的表空间相同，则不执行一下语句 – –grant dba to fis; – dba 权限是必须的 ， 一般默认导入的表空间是导出时候的表空间 – –revoke unlimited tablespace from fis; – 回收用户无限的表空间权限 – –alter user fis quota 0 on users; – 用户表空间限额设置 – –alter user fis quota unlimited on fis_tbs; – 修改用户表空间限定 – – end: 导入用户表空间 文件到不同的表空间，修改用户权限后导入，如果导入与导出的表空间相同，则不执行一下语句 –

— 导出数据库为dmp文件的脚本 – exp fis/123456@192.168.11.14/orcl file=d:/fis/fis_20160704.dmp owner=fis log=‘d:/fis/fis_20160704_exp.log’

– 从一个用户导入的新的用户，并导入到指定的表空间 , ignore=y 忽略已存在的表重复创建问题 – imp fis/123456@192.168.11.14/orcl file=d:/fis/fis_20160704.dmp tablespaces=fis_tbs fromuser=fis touser=fis log=‘d:/fis/fis_20160704_imp.log’ ignore=y

— 清理MUP平台数据脚本 – –工作流数据清理和工作流业务数据清理；手动执行 create or replace trigger pt_wf_tri_del_bytearray before delete on act_ge_bytearray for each row begin delete from act_ru_variable where bytearray_id_=:old.id_; end; /

delete act_hi_actinst; delete act_hi_attachment; delete act_hi_comment; delete act_hi_detail; delete act_hi_procinst; delete act_hi_taskinst; delete from act_ge_bytearray t where t.id_ in (select bytearray_id_ from act_ru_variable t2 where t.id_ = t2.bytearray_id_); delete act_ru_job; delete act_ru_identitylink; delete act_ru_variable; delete act_ru_task; delete act_ru_execution;

delete pf_bpm_undo;

delete pf_bpm_mainform;

delete PF_BPM_PROCTRACK;

commit;

— 清理业务数据表脚本 –

– delete EXP_QT_QUESTION_ZERO;

commit;