oracle database DBA working

wanhualeng

CREATE OR REPLACE FUNCTION Get_table_model(vname in varchar2
                                          ,v_flag in varchar2 default 'N'-----首字母是否小寫-------
                                                                                  ,v_col  in varchar2 default 'Y' ----是否欄位
                                                                                  ) RETURN varchar2 IS
/******************************************************************************
   NAME:       Get_table_model
   description 根據表名或欄位名,獲得Model名
******************************************************************************/
v_str varchar2(255);
v_temp varchar2(255);
v_temp1 varchar2(255);
BEGIN

if v_col = 'N' then

  v_temp:=substr(vname,instr(vname,'_')+1);
  
  v_temp1:=substr(vname,instr(vname,'_')+1);
  
else

  v_temp:=vname;
  
  v_temp1:=vname;
  
end if;

if vname is not null then

   loop
   
     if v_flag = 'Y' then
         
            if v_temp = v_temp1 then
               
                  if instr(v_temp,'_') =0 then
                 
                   v_str:=v_str||lower(v_temp);
                 
                  else
                  
                   v_str:=v_str||lower(substr(v_temp,0,instr(v_temp,'_')-1));
                 
                  end if;
               
                else
                    if instr(v_temp,'_') =0 then
                 
                       v_str:=v_str||initcap(v_temp);
                  
                     else
                 
                      v_str:=v_str||initcap(substr(v_temp,0,instr(v_temp,'_')-1));
                 
                   end if;
                end if;
               
                else
                 
                 if instr(v_temp,'_') =0 then
                 
                  v_str:=v_str||initcap(v_temp);
                  
                 else
                 
                  v_str:=v_str||initcap(substr(v_temp,0,instr(v_temp,'_')-1));
                 
                 end if;
               
         end if;

        exit when instr(v_temp,'_') = 0;
         
   v_temp:=substr(v_temp,instr(v_temp,'_')+1);
   
   end loop;

null;

end if;

return v_str;

END Get_table_model;
/

wanhualeng

修改數據文件
alter database  datafile 4 offline;
alter database  datafile '/opt/oracle/datafile/user01.dbf' offline;
alter database  datafile '/opt/oracle/datafile/user01.dbf'   resize 100m;
alter database  datafile '/opt/oracle/datafile/user01.dbf'  autoextend on next 100m maxsize

1024m ;
alter database datafile 4 end backup;


修改臨時文件
alter database tempfile 4 resize 100m;
alter database tempfile 4 autoextend on next 100m maxsize 1024m;
alter database tempfile 4 drop including datafiles;
alter database tempfile 4 offline;

歸檔日志模式命令

alter database archivelog;

alter database noarchivelog;
alter database force logging;
alter database clear logfile '/opt/oracle/logfiles/redo01.rdo';
alter database clear unarchived logfile '/opt/oracle/logfiles/redo01.rdo';
alter database add supplemental log data;
alter database add supplemental log data (primary key,unique);
alter database drop supplemental log data;


控制文件操作

alter database backup controlfile to trace;
alter database backup controlfile to trace as '/opt/oracle/logfile_backup/backup_logfile.trc'

reuse resetlogs;
alter database backup controlfile to '/opt/oracle/logfile_backup/backup_logfile.ctl' ;


創造數據文件

alter database create datafile '/opt/oracle/datafile/user01.dbf' as

'/opt/oracle/datafile/user01.dbf';
alter database create datafiel 4 as ''opt/oracle/datafile/users01.dbf';
alter database create datafile 'opt/oracle/datafiel/users01.dbf' as new;

日志文件命令
alter database add logfile group 2

('/opt/oracle/logfiles/redo02a.rdo','/opt/oracle/logfiles/redo02b.rdo') size 300m reuse;
alter database add logfile member '/opt/oracle/logfiles/redo02c.rdo' to group 2;
alter database add logfile thread 3 group 2

('/opt/oracle/logfiles/redo02a.rdo','/opt/oracle/logfiles/redo02b.rdo') size 300m reuse;
alter database drop logfile group 3;
alter database drop logfile member '/opt/oracle/logfiles/redo02b.rdo';

安裝與打開數據

alter database mount ;
alter database open;
alter database open read only; (只讀方式打開數據庫)
alter database open resetlogs;(使用resetlogs 打開數據庫)

移動或重命名數據庫文件
alter database rename file '/ora/datafile/oldfile.dbf' to 'ora/datafile/newfile.dbf';

重新編譯函數
alter function my_function compile;

分配和釋放區
alter index ix_my_tab allocate extent;
alter index ix_my_tab deallocate unused;
alter index ix_my_tab deallocate unused keep 100m;

雜項維護
alter index ix_my_tab parallel 3;
alter index ix_my_tab noparalles;
alter index ix_my_tab nocompress;
alter index ix_my_tab compress;

修改日志屬性
alter index ix_my_tab logging;
alter index ix_my_tab nologging;
alter index ix_my_tab add partition tablespace newidxtbs;(添加分區)
alter index ix_my_tab coalese partitions;（合並分區）
alter index ix_my_tab drop partition ix_my_tab_jan_04;(刪除分區)
alter index ix_my_tab rename partion ix_my_tab_jan_04 to ix_my_tab_jan_05( 重命名)
alter index ix_my_tab rebuild online;
alter index ix_my_tab rename to 'ix_my_tab_01';

alter materialized view mv_my_tab allocate extend;
alter materialized view mv_my_tab deallocate unused;
alter materialized view mv_my_tab compress;
alter materialized view mv_my_tab nologging;
alter materialized view mv_my_tab logging;
alter materialized view mv_my_tab consider fresh;
alter materialized view mv_my_tab enable query rewrite;

alter materialized view mv_my_tab refresh fast;
alter materialized view mv_my_tab refresh complete;
alter materialized view mv_my_tab refresh fast on demand;
alter materialized view mv_my_tab refresh fast on commit;
alter materialized view mv_my_tab refresh complete  start with sysdate;
alter materialized view mv_my_tab refresh complete startwith sysdate next sysdate+1/24;

alter materizlized view mv_my_tab shrink space(compact cascade);


alter package:編譯
alter package pk_my_package compile;
alter package pk_my_package compile specification;
alter package pk_my_package compile body;
alter procedure pk_my_procedure compile;

alter role my_role identified by password ;
alter role my_role not identified;

alter profile my_profile limit failed_login_attempts=3;
alter porfile my_profile limit password_lock_time=2/24;
alter porfile my_profile limit password_grace_time=5;
alter porfile my_profile limit password_lifetime=60;
alter porfile my_profile limitpasword_reuse_time=365 password_reuse_max=3;

alter porfile my_profile limit session_per_cpu=10;
alter porfile my_profile limit connect_time=1000;
alter porfile my_profile limit idle_time=60;
alter porfile my_profile limit private_sge=10000000;

alter rollback segment rbs01 offline;
alter rollback segment rbs01 online;
alter rollback segment rbs01 shrink;
alter rollback segment rbs01 shrink to 100m;
alter rollback segment rbs01 storage(next 50m optimal 100m);

alter sequence my_seq increment by -5;
alter sequence my_seq increment by 1 maxvalue 50000 cycle;
alter sequence my_seq nomaxvalue;
alter sequence my_seq cache order;
alter sequence my_seq incrment by 1 minvalue1 maxvalue 500 cycle;


alter session enable parallel dml parallel 3;
alter session enable parallel ddl;
alter session disable parallel query;

alter session enable resumable timeout 3600
alter session disable resumable;

alter session set nls_date_format ='yyyy/mm/dd hh24:mi:ss';
alter session set sor_area_size=10000000;
alter session set query_rewrite_enabled=true;
alter session set resumable_timeout=3600;
alter session set skip_unusable_indexes=true;
alter session ser sql_trace=true;

alter system switch logfile;
alter system archive log start;
alter system archive log stop;
alter system archive log all;
alter system archive log thread 1 all;
alter system archive log all to 'C:\oracle\allarch';

alter system set db_cache_size=325m comment = 'This ehange is to add more memory to the system'

scope = both;
alter system set compatible=10.0.0 comment='Going to 10' scope=spfile;

alter system checkpoint global;
alter system kill seeeion '145,334';
alter system enable restricted session ;
alter system disable restricted session ;
alter system suspend;
alter system quiesce restricted;
alter system unquiesce;
alter system resume;
alter system flush shared_pool;
alter system flush buffer_cache;

alter table ext_parts reject limit 500;
alter table ext_parts defualt directory ext_employee_dis;
alter table ext_parts access parameters (fileds terminated by ',');
alter tabel ext_parts location ('parts01.txt','parts02.txt');
alter table ext_parts add column (ssn number);
alter table parts move tablespace parts_new_tbs pctfree 10 pctused 60;

alter table parts add(part_location varchar2(20));
alter table parts add(part_location varcha2(20),part_bin varchar2(30));
alter table parts add(photo blob) lob (photo) store as lob_parts_photo (tablespace

parts_lob_tbs);


alter table parts modify (part_location varchar2(30));
alter table parts modify (part_location varchar2(30),part_bin varchar2(30));

〇〇

很多脚本

wanhualeng

alter table parts modify (name not null);
alter table parts modify (name null);
alter table parts modify lob(photo) (storage(freelists2));
alter table parts modify lob(photo) (pctversion 50);

alter table parts drop (part_location);
alter table parts drop(part_location,part_bin);

alter table parts rename column part_location to part_loc;
alter table parts add(constraint ck_parts_01 check (id>0));

alter table parts modify (name default 'NOt avilable');
alter table parts add(vendor_code number default 0);
alter table parts modify(part_description default null);

alter table parts add constraint fk_part_bin foreign key(bin_code references part_bin;

alter table parts add constraint pk_parts_part_id primary key(id) using index tablespace parts_index storage( initial 100k next 100k pcrincrease 0);
alter table parts add constraint uk_parts_bin unique(part_bin) using index talespace parts_index storage(ubutuak 100k next 100k pctincrease 0);

---16
alter table parts disable unique(part_bin);
alter table parts disable constraint uk_parts_part_bin;
alter table parts disable constraint uk_parts_part_bin keep index;
alter table parts disable constraint fk_part_bin;
alter table parts disable constraint fk_partk_bin disable primary key keep index;
alter table parts enable constraint fk_part_bin;
alter table parts enable unique(part_bin);
alter table parts enable novalidate constraint fk_part_bin;
alter table parts enable novalidate primary key enable novalidate constraint fk_part_bin;

alter table parts drop constraint fk_part_bin;
alter table parts drop primary key;
alter table parts drop primary key cascade;
alter table parts drop unique (uk_parts_part_bin);


create materialized view

create materialized view emp_dept_mv1 tablespace users build immediate
refresh fast on commit with rowid enable query rewrite as
select * from emp ;

create materialized view emp_dept_mv2 tablespace users build immediate
refresh fast on commit with rowid disable uqery rewrite as
select  * from emp;

create materialized view part_emp_mv11
partition by range(hiredate)
(partition month1 values less then (o_date(2009/01/01','yyyy/mm/dd'))
  tablesparce users,
partition month2 values less then (o_date(2009/02/01','yyyy/mm/dd'))
  tablesparce users,
partition month3values less then (o_date(2009/01/01','yyyy/mm/dd'))
  tablesparce users)
build immediae refresh fast enable query rewrite as
select * from emp group by hiredate;

create materialized view log

create materialized view log on emp tablespace users
with primary key,sequence(ename,job,mgr,hiredate,sal,comm,deptno)including new values;

------18
create package/create package body

create or replace package get_tomdate_pkg is
  function gettomdate return date;
end get_tomdate_pkg;

create or replace package body get_tomdate_pkg is
  v_sysdate date := trunc(sysdate);
  function gettomdate return date is
   begin
      retrun v_sysdate+1;
   end;
end get_tomdate_pkg;


create procedure

create or replace procedure new_eno_salary
(p_emp_id in number ,p_increase in number)
as
begin
  update emp set salary= salary *p_increase;
end;


create pfile

create pfile from spifle;
create pfile  ='/opt/oracle/initmybd.ora' from spfile='/opt/orace/spfilemybd.ora';

create spfile

create spfile from pifle;
create spfile  ='/opt/oracle/spfilemybd.ora' from pfile='/opt/orace/initmybd.ora';



create role
create role develoer_role identified using develop;

create rollback segment
create rollback segment r01 tablespace rbs storage(initial 100m next 100m minextents 5 optimal 500m);

create sequence
create sequence my_seq start_with 1 increment by 1 maxvlue 10000 cycle cache;

create synonym
create synonym scott_user.emp for scott.emp;
create public synonym emp for scott_emp;


create table
create table my_tab ()
id number,current_value varchar2(2000)) tablespace parts_tablespace;

create tablespace data_tbs
datafile '/opt/oracle/data/my_tbs_data_tbs_01.dbf' size 100m;

create tablespace data_tbs
datafile '/opt/oracle/data/my_tbs_data_tbs_01.dbf' size 100m force logging blocksize 8k;

create tablespace data_tbs
datafile '/opt/oracle/data/my_tbs_data_tbs_01.dbf' size 100m nologging;


create tablespace temp_tbs tempfile '/opt/oracle/data/tbs_01.tmp' size 100m;

create tablespace undo_tbs tempfile '/opt/oracle/data/tbs_02.tmp' size 1g retention guarantee;


create trigger

create or replace trigger emp_comm_after_insert
before insert on emp ofr each row
declare
  v_sql number;
  v_comm number;
begin
  v_sal := :new.salary;
  :new.comm := v_sal *.10;
end;

create user robert identified by freeman default tablespace users_tbs
temporary tablespace temp ;

drop database;

drop database link my_db_link;

drop directory mydir

drop function find_value_in_table;

drop index ix_my_tab;

drop materialized view  my_mview preserve table;
drop materialized view log on mytab;

drop package my_package;
drop package body my_package;

drop procedure my_proc;
drop frofile my_profile cascade;

drop role my_role;
drop rollback segment rbs01;
drop sequence my_seq;
drop synonym my_stnonym;
drop table my_tab cascade constraints purge;
drop tablepace my_tbs including contents and datafiles cascade constraints;
drop trigger my_trigger;
drop user my_user cascade;
drop view my_view cascade constraints;

eppain plan

explain plan set statement_id='test' for select * from emp where empid=100;


create or replace view vw_emp_dept-10 as
select * from emp ;

flashback database to scn 10000;
flashback database to timestamp sysdate-1/24;
flashback database to bfore timestamp sysdate-1/24;

flashback table my_tab to scn 10000;
flashback table to timestamp sysdate -1/24 enable triggers;
flashback table my_tab to  before drop rename to rec_tab;

wanhualeng

grant select on m_tab to my_user;
grant select,update,insert on my_tab to my_user wiht grant optio ,with admin option;



merge into emp_retire a
using (select * from emp ) b
on a.emp_id-b.empid
when matched thn update set a.ename_last=b.ename_last
when not matched then insert (a.empid,a,ename_last)
values(b.empid,b.ename_last)
where b.retire_idI-'D');


purge table my_tab;
purge index ix_my_tab;
purge recyclebin;
purge dba_recyclebin;


recover database;
recover tablespace user_data,user_index;
recover database until time '2008/01/01 22:10:10';
recover database until cancel using backup conrtolfile;
recover datafile '/opt/oracle/mydb_users_01.dbf';


rename my_table to my_tab;

revoke select on mytab from my_user;
revoke create table from my_user;
revoke all privileges from my_users;

savepoint alpha;

trucate table my_table drop storage;
set transaction read only;
set transaction use rollbck segment rbs01;

anna8885

thanks for your sharing ..^^

efforttime

谢谢楼主

wanhualeng

CREATE OR REPLACE FUNCTION IT_TESTDB.fn_md5 (input_string IN VARCHAR2)
    RETURN VARCHAR2
IS
    raw_input                        RAW (128)
                                           := UTL_RAW.cast_to_raw (input_string);
    decrypted_raw                    RAW (2048);
    error_in_input_buffer_length     EXCEPTION;
BEGIN
    DBMS_OBFUSCATION_TOOLKIT.md5 (input => raw_input,
                                  checksum => decrypted_raw
                                 );
    RETURN LOWER (RAWTOHEX (decrypted_raw));
END;
/

wanhualeng

CREATE OR REPLACE package IT_TESTDB.PG_ENCRYPT_DECRYPT is
iKey varchar2(8):='oracle9i';
function GEN_RAW_KEY  ( iKey in varchar2) return raw;
function DECRYPT_3KEY_MODE(iValue in raw,iMode in pls_integer)return varchar2;
function ENCRYPT_3KEY_MODE(iValue in varchar2,iMode in pls_integer)return raw;
end;
/




CREATE OR REPLACE package body IT_TESTDB.PG_ENCRYPT_DECRYPT is
function GEN_RAW_KEY  ( iKey in varchar2) return raw
as
rawkey raw(240) := '';
begin
   for i in 1..length(iKey) loop  
     rawkey := rawkey||hextoraw(to_char(ascii(substr(iKey, i, 1))));
   end loop;
return rawkey;
end;
/* Creating function DECRYPT_3KEY_MODE*/
FUNCTION DECRYPT_3KEY_MODE  ( iValue in raw, iMode in pls_integer)return varchar2
as
vDecrypted varchar2(4000);
rawkey raw(240) := '';
begin
  rawkey := GEN_RAW_KEY(iKey);
-- decrypt input string
vDecrypted := dbms_obfuscation_toolkit.des3decrypt (UTL_RAW.CAST_TO_VARCHAR2(iValue), key_string => rawkey, which => iMode);
return vDecrypted;
end;
/*Creating function ENCRYPT_3KEY_MODE*/
FUNCTION ENCRYPT_3KEY_MODE  ( iValue in varchar2,  iMode in pls_integer) return raw
as
vEncrypted varchar2(4000);
vEncryptedRaw Raw(2048);
rawkey raw(240) := '';
begin
rawkey := GEN_RAW_KEY(iKey);
-- encryptinput string
vEncrypted := dbms_obfuscation_toolkit.des3encrypt (iValue, key_string => rawkey, which => iMode);
-- convert to raw as out
vEncryptedRaw := UTL_RAW.CAST_TO_RAW(vEncrypted);
return vEncryptedRaw;
end;
end;
/

sir.liang

Thanks