Terry.Li-彬

1、用戶： 
　　　select username from dba_users; 
　　改口令 
　　　alter user spgroup identified by spgtest; 
　　2、表空間： 
　　　select * from dba_data_files; 
　　　select * from dba_tablespaces;//表空間 

　　　select tablespace_name,sum(bytes), sum(blocks) 
　　　　from dba_free_space group by tablespace_name;//空閑表空間 

　　　select * from dba_data_files 
　　　　where tablespace_name='RBS';//表空間對應的數據文件 

　　　select * from dba_segments 
　　　　where tablespace_name='INDEXS'; 
　　3、數據庫對象： 
　　　select * from dba_objects;  //得到所有的空間的里面的所有表，包括系統本身的表
　　　CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGE BODY、 
　　　PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。 
　　4、表： 
　　　select * from dba_tables;  //得到所有空間的里面的MYOBJECT
　　　analyze my_table compute statistics;->dba_tables后6列 
　　　select extent_id,bytes from dba_extents 
　　　where segment_name='CUSTOMERS' and segment_type='TABLE' 
　　　order by extent_id;//表使用的extent的信息。segment_type='ROLLBACK'查看回滾段的空間分配信息 
　　　列信息： 
　　　　select distinct table_name 
　　　　from user_tab_columns 
　　　　where column_name='SO_TYPE_ID'; 
　　5、索引：　 
　　　select * from dba_indexes;//索引，包括主鍵索引 
　　　select * from dba_ind_columns;//索引列 
　　　select i.index_name,i.uniqueness,c.column_name 
　　　　from user_indexes i,user_ind_columns c 
　　　　　where i.index_name=c.index_name 
　　　　　and i.table_name ='ACC_NBR';//聯接使用 
　　6、序列： 
　　　select * from dba_sequences; 
　　7、視圖： 
　　　select * from dba_views; 
　　　select * from all_views; 
　　text 可用于查詢視圖生成的腳本 
　　8、聚簇： 
　　　select * from dba_clusters; 
　　9、快照： 
　　　select * from dba_snapshots; 
　　快照、分區應存在相應的表空間。 
　　10、同義詞： 
　　　select * from dba_synonyms 
　　　　where table_owner='SPGROUP'; 
　　　　//if owner is PUBLIC,then the synonyms is a public synonym. 
　　　　　if owner is one of users,then the synonyms is a private synonym. 
　　11、數據庫鏈： 
　　　select * from dba_db_links; 
　　在spbase下建數據庫鏈 
　　　create database link dbl_spnew 
　　　connect to spnew identified by spnew using 'jhhx'; 
　　　insert into acc_nbr@dbl_spnew 
　　　select * from acc_nbr where nxx_nbr='237' and line_nbr='8888'; 
　　12、觸發器： 
　　　select * from dba_trigers; 
　　存儲過程，函數從dba_objects查找。 
　　其文本：select text from user_source where name='BOOK_SP_EXAMPLE'; 
　　建立出錯：select * from user_errors; 
　　oracle總是將存儲過程，函數等軟件放在SYSTEM表空間。 
　　13、約束： 
　　（1）約束是和表關聯的，可在create table或alter table table_name add/drop/modify來建立、修改、刪除約束。 
　　可以臨時禁止約束，如： 
　　　alter table book_example 
　　　disable constraint book_example_1; 
　　　alter table book_example 
　　　enable constraint book_example_1; 
　　（2）主鍵和外鍵被稱為表約束，而not null和unique之類的約束被稱為列約束。通常將主鍵和外鍵作為單獨的命名約束放在字段列表下面，而列約束可放在列定義的同一行，這樣更具有可讀性。 
　　（3）列約束可從表定義看出，即describe;表約束即主鍵和外鍵，可從dba_constraints和dba_cons_columns 查。 
　　　select * from user_constraints 
　　　where table_name='BOOK_EXAMPLE'; 
　　　select owner,CONSTRAINT_NAME,TABLE_NAME 
　　　　from user_constraints 
　　　　where constraint_type='R' 
　　　　order by table_name; 
　　（4）定義約束可以無名（系統自動生成約束名）和自己定義約束名（特別是主鍵、外鍵） 
　　如：create table book_example 
　　　　(identifier number not null); 
　　　　create table book_example 
　　　　(identifier number constranit book_example_1 not null); 
　　14、回滾段： 
　　在所有的修改結果存入磁盤前，回滾段中保持恢復該事務所需的全部信息，必須以數據庫發生的事務來相應確定其大小（DML語句才可回滾，create,drop,truncate等DDL不能回滾）。 
　　回滾段數量=并發事務/4，但不能超過50；使每個回滾段大小足夠處理一個完整的事務； 
　　　create rollback segment r05 
　　　tablespace rbs; 
　　　create rollback segment rbs_cvt 
　　　tablespace rbs 
　　　storage(initial 1M next 500k); 
　　使回滾段在線 
　　　alter rollback segment r04 online; 
　　用dba_extents,v$rollback_segs監測回滾段的大小和動態增長。 
　　回滾段的區間信息 
　　　select * from dba_extents 
　　　where segment_type='ROLLBACK' and segment_name='RB1'; 
　　回滾段的段信息,其中bytes顯示目前回滾段的字節數 
　　　select * from dba_segments 
　　　　where segment_type='ROLLBACK' and segment_name='RB1'; 
　　為事物指定回歸段 
　　　set transaction use rollback segment rbs_cvt 
　　針對bytes可以使用回滾段回縮。 
　　　alter rollback segment rbs_cvt shrink; 
　　　select bytes,extents,max_extents from dba_segments 
　　　　where segment_type='ROLLBACK' and segment_name='RBS_CVT'; 
　　回滾段的當前狀態信息： 
　　　select * from dba_rollback_segs 
　　　　where segment_name='RB1'; 
　　比多回滾段狀態status，回滾段所屬實例instance_num 
　　查優化值optimal 
　　　select n.name,s.optsize 
　　　　from v$rollname n,v$rollstat s 
　　　　　where n.usn=s.usn; 
　　回滾段中的數據 
　　　set transaction use rollback segment rb1;/*回滾段名*/ 
　　　select n.name,s.writes 
　　　　from v$rollname n,v$rollstat s 
　　　　　where n.usn=s.usn; 
　　當事務處理完畢，再次查詢$rollstat，比較writes(回滾段條目字節數)差值，可確定事務的大小。 
　　查詢回滾段中的事務 
　　　column rr heading 'RB Segment' format a18 
　　　column us heading 'Username' format a15 
　　　column os heading 'Os User' format a10 
　　　column te heading 'Terminal' format a10 
　　　select r.name rr,nvl(s.username,'no transaction') us,s.osuser os,s.terminal te 
　　　　from v$lock l,v$session s,v$rollname r 
　　　　　where l.sid=s.sid(+) 
　　　　　and trunc(l.id1/65536)=R.USN 
　　　　　and l.type='TX' 
　　　　　and l.lmode=6 
　　　order by r.name; 
　　15、作業 
　　查詢作業信息 
　　　select job,broken,next_date,interval,what from user_jobs; 
　　　select job,broken,next_date,interval,what from dba_jobs; 
　　查詢正在運行的作業 
　　　select * from dba_jobs_running; 
　　使用包exec dbms_job.submit(:v_num,'a;',sysdate,'sysdate + (10/(24*60*60))')加入作業。間隔10秒鐘 
exec dbms_job.submit(:v_num,'a;',sysdate,'sysdate + (11/(24*60))')加入作業。間隔11分鐘使用包exec dbms_job.remove

/**oracle的表創建*/
create table USERINFO(
  USERID NUMBER(8) NOT NULL,
  USERNAME VARCHAR2(20),
  PASSWR VARCHAR2(20),
  EMAIL VARCHAR2(20),
  STATE NUMBER(1) DEFAULT 0,
  EMPID NUMBER(8),
  CONSTRAINT PK_USER PRIMARY KEY(USERID)
)
 /***增加關聯條件****/
 ALTER TABLE USERINFO
 ADD CONSTRAINT FK_USER FOREIGN KEY(EMPID) REFERENCES EMP (EMPID)
select * from user_tables;--得到用戶下的表名及表空間>
select  * from user_tab_cols;--得到用戶下所有表，表字段，類型及長度---->
select * from user_tab_columns; --得到用戶下所有表，表字段，類型及長度---->
select * from user_col_comments;--得到用戶下表字段的注釋>
select * from user_tab_comments;--得到表類型，注釋>
select * from user_cons_columns;---得到用戶表下的約束關系,表名,字段名>
select * from user_constraints;--得到用戶表下的約束關系,表名,約束類型>
comment on table USERINFO is '用戶信息';--注釋表名>
comment on column USERINFO.USERID is '用戶編號';--字段注釋>
/*---列出某個用戶下所有表的信息--*/
select * from user_tab_comments A,user_tables B where A.table_name=B.table_name and A.table_type='TABLE';
/*----列出某個表的主鍵信息------*/
select A.table_name,A.constraint_name,A.column_name from user_cons_columns A,user_constraints B
where A.constraint_name=B.constraint_name and B.constraint_type='P' and B.table_name='EMPLOVE';
/*----列出某個表的外鍵信息------*/
select A.table_name,A.constraint_name,B.column_name from user_constraints A,user_cons_columns B
where A.constraint_name=B.constraint_name and B.table_name='EMPLOVE' and A.constraint_type='R';
/*----需要得到字段的信息和備注----*/ 
select distinct A.column_name,A.data_type||'('||A.data_length||')',B.comments from user_tab_cols A,user_col_comments B
where  A.table_name=B.table_name and A.column_name=B.column_name and A.table_name='EMP';
/*----日期格式的增加---*/
insert into employee values(to_date('2006-06-10','yyyy-mm-dd'));
/*----日期間的相減---*/
select to_number(to_char(curdate,'dd'))-to_number(to_char(olddate,'dd')) from datetable;
/*----oracle分頁----*/
select * from (select t.*,rownum r_id from tablename t) where r_id between 1 and 10;
select * from (select t.*,rownum r_id from tablename t) where r_id >=1 and r_id<=10;--不可排序〉
select t.*,rowid from tablename t where rownum<=20 minus select t.*,rowid from tablename t where rownum<=10
select cc.* from (select bb.*,rownum r_id from (select * from tablename) bb where rownum<=20 ) cc where cc.r_id>=10
select * from (select * from deptinfo where rownum <=20 minus select * from deptinfo where rownum<=10) order by deptid;---可排序〉
/*-----備注表的查詢----*/
select decode(emptype,0,(select xlname from xueli B where B.xlid=A.posttype),1,(select zcname from zhicheng C where C.Zcid=A.posttype)) from emp A;

/*********************----oracle高級應用-----**********************/
/*--創建游標在某包--*/
create or replace package packagename
  as
    type ref_cursor is ref cursor;
end;
/*---存儲過程的分頁---*/
---**********1>
create or replace procedure getOnePage(curpage in number,page_record in number,rc in out packagename.ref_cursor)
 as
   begin
      open rc for select * from (select t.*,rownum id from deptinfo t order by t.deptid)
       where id between (curpage-1)*page_record and curpage*page_record;
   end;
---**********2>
create or replace procedure getOnePage2(curpage in number,page_record in number,cond in varchar2,rc in out packagename.ref_cursor)
 as
   str_sql varchar2(500);
   begin
       str_sql:='select * from (select t.*,rownum id from deptinfo t where 1=1 '||cond||' order by t.deptid)
       where id between '||(curpage-1)*page_record || ' and '|| curpage*page_record;
       open rc for str_sql;
   end;
/*---新增的存儲過程---*/
create or replace procedure newDept
(deptid deptinfo.deptid%type,
 deptname deptinfo.deptname%type,
 deptnum deptinfo.deptnum%type,
 deptdesc deptinfo.deptdesc%type)
as
    begin
      insert into deptinfo(deptid,deptname,deptnum,deptdesc)values(deptid,deptname,deptnum,deptdesc);
      commit;
    end;
/*---修改的存儲過程---*/
  create or replace procedure updateDept(did in number,num in number)
    as
      begin
         update deptinfo set deptnum=num where deptid=did;
         commit;
      end;
/******************************-----oracle函數---*********************************/
/*-----得到總紀錄---*/
create or replace function getTableCount(v_sql in char) return number
 as
   counts number;
   begin
      execute immediate 'select count(1) from ('||v_sql||')' into counts; 
      return counts;
   exception
   when others then
   raise;
   end;
/*****---------------oracle函數分頁---------------------*******/
<------通用------->
create or replace function getTableResult(result_sql in varchar2,curpage in number default null,page_record in number default null,sortfield in varchar2 default null,sorttype in number default null,countrecord out number)
 return itfuture.ref_cursor
  as
   vsql varchar2(1000);
   startpos number;
   endpos number;
   resultcursor itfuture.ref_cursor;
   begin
     countrecord:=getTableCount(result_sql);------------------調用總計錄方法>
     vsql:=result_sql;
     if(sortfield is not null) then
       vsql:=vsql||' order by '||sortfield;
       if(sorttype='1')then
       vsql:=vsql||' asc ';
       else
       vsql:=vsql||' desc ';
       end if;
     end if;  
     if((curpage is not null)and(page_record is not null)) then
       startpos:=((curpage-1)*page_record+1);
       endpos:=curpage*page_record;
       vsql:='select cc.* from (select bb.*,rownum r_id from('||vsql||') bb where rownum<'||endpos||' ) cc where cc.r_id>'||startpos;
     end if;
     open resultcursor for vsql;
     return resultcursor;
     exception
     when others then
     raise;
   end;
<----------2--------->
create or replace function f_getOnePage(curpage number,page_record number,cond varchar2) return itfuture.ref_cursor
 as
   rc itfuture.ref_cursor;
   str_sql varchar2(2000);
   a NUMBER;
   begin
       a:=6;
       str_sql:='select * from (select t.*,rownum id from deptinfo t where 1=1 '||cond||' order by t.deptid)
       where id between '||(curpage-1)*page_record || ' and '|| curpage*page_record;
       open rc for str_sql;
       return rc;
   end;
/*-----------------在包下創建函數--------------------*/
<----包下函數定義---->
create or replace package itfuture
 as
  type ref_cursor is ref cursor;
  function f_getOnePage(curpage number,page_record number,cond varchar2) return itfuture.ref_cursor;
 end;
<----包下函數實現---->
create or replace package body itfuture
 is
 function f_getOnePage(curpage number,page_record number,cond varchar2) return itfuture.ref_cursor
 as
   rc itfuture.ref_cursor;
   str_sql varchar2(2000);
   a NUMBER;
   begin
       a:=6;
       str_sql:='select * from (select t.*,rownum id from deptinfo t where 1=1 '||cond||' order by t.deptid)
       where id between '||(curpage-1)*page_record || ' and '|| curpage*page_record;
       open rc for str_sql;
       return rc;
   end;
  end;

/***************************************java中存儲過程的調用***************************/

String query="{call getOnePage(?,?,?)}";
CallableStatement cs=open.conn.prepareCall(query);
      cs.setInt(1,1);
      cs.setInt(2,10);
      cs.registerOutParameter(3,oracle.jdbc.OracleTypes.CURSOR);
      cs.execute();
ResultSet rs=(ResultSet)cs.getObject(3);

/***************************************java中函數的調用***************************/
String query="begin :1 :=itfuture.f_getOnePage(:2,:3,:4);end;";
      CallableStatement cs=open.conn.prepareCall(query);
      cs.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
      cs.setInt(2,1);
      cs.setInt(3,5);
      cs.setString(4,null);
      cs.execute();
      ResultSet rs=(ResultSet)cs.getObject(1);

/**oracle的表創建*/
create table USERINFO(
  USERID NUMBER(8) NOT NULL,
  USERNAME VARCHAR2(20),
  PASSWR VARCHAR2(20),
  EMAIL VARCHAR2(20),
  STATE NUMBER(1) DEFAULT 0,
  EMPID NUMBER(8),
  CONSTRAINT PK_USER PRIMARY KEY(USERID)
)
 /***增加關聯條件****/
 ALTER TABLE USERINFO
 ADD CONSTRAINT FK_USER FOREIGN KEY(EMPID) REFERENCES EMP (EMPID)
select * from user_tables;--得到用戶下的表名及表空間>
select  * from user_tab_cols;--得到用戶下所有表，表字段，類型及長度---->
select * from user_tab_columns; --得到用戶下所有表，表字段，類型及長度---->
select * from user_col_comments;--得到用戶下表字段的注釋>
select * from user_tab_comments;--得到表類型，注釋>
select * from user_cons_columns;---得到用戶表下的約束關系,表名,字段名>
select * from user_constraints;--得到用戶表下的約束關系,表名,約束類型>
comment on table USERINFO is '用戶信息';--注釋表名>
comment on column USERINFO.USERID is '用戶編號';--字段注釋>
/*---列出某個用戶下所有表的信息--*/
select * from user_tab_comments A,user_tables B where A.table_name=B.table_name and A.table_type='TABLE';
/*----列出某個表的主鍵信息------*/
select A.table_name,A.constraint_name,A.column_name from user_cons_columns A,user_constraints B
where A.constraint_name=B.constraint_name and B.constraint_type='P' and B.table_name='EMPLOVE';
/*----列出某個表的外鍵信息------*/
select A.table_name,A.constraint_name,B.column_name from user_constraints A,user_cons_columns B
where A.constraint_name=B.constraint_name and B.table_name='EMPLOVE' and A.constraint_type='R';
/*----需要得到字段的信息和備注----*/ 
select distinct A.column_name,A.data_type||'('||A.data_length||')',B.comments from user_tab_cols A,user_col_comments B
where  A.table_name=B.table_name and A.column_name=B.column_name and A.table_name='EMP';
/*----日期格式的增加---*/
insert into employee values(to_date('2006-06-10','yyyy-mm-dd'));
/*----日期間的相減---*/
select to_number(to_char(curdate,'dd'))-to_number(to_char(olddate,'dd')) from datetable;
/*----oracle分頁----*/
select * from (select t.*,rownum r_id from tablename t) where r_id between 1 and 10;
select * from (select t.*,rownum r_id from tablename t) where r_id >=1 and r_id<=10;--不可排序〉
select t.*,rowid from tablename t where rownum<=20 minus select t.*,rowid from tablename t where rownum<=10
select cc.* from (select bb.*,rownum r_id from (select * from tablename) bb where rownum<=20 ) cc where cc.r_id>=10
select * from (select * from deptinfo where rownum <=20 minus select * from deptinfo where rownum<=10) order by deptid;---可排序〉
/*-----備注表的查詢----*/
select decode(emptype,0,(select xlname from xueli B where B.xlid=A.posttype),1,(select zcname from zhicheng C where C.Zcid=A.posttype)) from emp A;

/*********************----oracle高級應用-----**********************/
/*--創建游標在某包--*/
create or replace package packagename
  as
    type ref_cursor is ref cursor;
end;
/*---存儲過程的分頁---*/
---**********1>
create or replace procedure getOnePage(curpage in number,page_record in number,rc in out packagename.ref_cursor)
 as
   begin
      open rc for select * from (select t.*,rownum id from deptinfo t order by t.deptid)
       where id between (curpage-1)*page_record and curpage*page_record;
   end;
---**********2>
create or replace procedure getOnePage2(curpage in number,page_record in number,cond in varchar2,rc in out packagename.ref_cursor)
 as
   str_sql varchar2(500);
   begin
       str_sql:='select * from (select t.*,rownum id from deptinfo t where 1=1 '||cond||' order by t.deptid)
       where id between '||(curpage-1)*page_record || ' and '|| curpage*page_record;
       open rc for str_sql;
   end;
/*---新增的存儲過程---*/
create or replace procedure newDept
(deptid deptinfo.deptid%type,
 deptname deptinfo.deptname%type,
 deptnum deptinfo.deptnum%type,
 deptdesc deptinfo.deptdesc%type)
as
    begin
      insert into deptinfo(deptid,deptname,deptnum,deptdesc)values(deptid,deptname,deptnum,deptdesc);
      commit;
    end;
/*---修改的存儲過程---*/
  create or replace procedure updateDept(did in number,num in number)
    as
      begin
         update deptinfo set deptnum=num where deptid=did;
         commit;
      end;
/******************************-----oracle函數---*********************************/
/*-----得到總紀錄---*/
create or replace function getTableCount(v_sql in char) return number
 as
   counts number;
   begin
      execute immediate 'select count(1) from ('||v_sql||')' into counts; 
      return counts;
   exception
   when others then
   raise;
   end;
/*****---------------oracle函數分頁---------------------*******/
<------通用------->
create or replace function getTableResult(result_sql in varchar2,curpage in number default null,page_record in number default null,sortfield in varchar2 default null,sorttype in number default null,countrecord out number)
 return itfuture.ref_cursor
  as
   vsql varchar2(1000);
   startpos number;
   endpos number;
   resultcursor itfuture.ref_cursor;
   begin
     countrecord:=getTableCount(result_sql);------------------調用總計錄方法>
     vsql:=result_sql;
     if(sortfield is not null) then
       vsql:=vsql||' order by '||sortfield;
       if(sorttype='1')then
       vsql:=vsql||' asc ';
       else
       vsql:=vsql||' desc ';
       end if;
     end if;  
     if((curpage is not null)and(page_record is not null)) then
       startpos:=((curpage-1)*page_record+1);
       endpos:=curpage*page_record;
       vsql:='select cc.* from (select bb.*,rownum r_id from('||vsql||') bb where rownum<'||endpos||' ) cc where cc.r_id>'||startpos;
     end if;
     open resultcursor for vsql;
     return resultcursor;
     exception
     when others then
     raise;
   end;
<----------2--------->
create or replace function f_getOnePage(curpage number,page_record number,cond varchar2) return itfuture.ref_cursor
 as
   rc itfuture.ref_cursor;
   str_sql varchar2(2000);
   a NUMBER;
   begin
       a:=6;
       str_sql:='select * from (select t.*,rownum id from deptinfo t where 1=1 '||cond||' order by t.deptid)
       where id between '||(curpage-1)*page_record || ' and '|| curpage*page_record;
       open rc for str_sql;
       return rc;
   end;
/*-----------------在包下創建函數--------------------*/
<----包下函數定義---->
create or replace package itfuture
 as
  type ref_cursor is ref cursor;
  function f_getOnePage(curpage number,page_record number,cond varchar2) return itfuture.ref_cursor;
 end;
<----包下函數實現---->
create or replace package body itfuture
 is
 function f_getOnePage(curpage number,page_record number,cond varchar2) return itfuture.ref_cursor
 as
   rc itfuture.ref_cursor;
   str_sql varchar2(2000);
   a NUMBER;
   begin
       a:=6;
       str_sql:='select * from (select t.*,rownum id from deptinfo t where 1=1 '||cond||' order by t.deptid)
       where id between '||(curpage-1)*page_record || ' and '|| curpage*page_record;
       open rc for str_sql;
       return rc;
   end;
  end;

/***************************************java中存儲過程的調用***************************/

String query="{call getOnePage(?,?,?)}";
CallableStatement cs=open.conn.prepareCall(query);
      cs.setInt(1,1);
      cs.setInt(2,10);
      cs.registerOutParameter(3,oracle.jdbc.OracleTypes.CURSOR);
      cs.execute();
ResultSet rs=(ResultSet)cs.getObject(3);

/***************************************java中函數的調用***************************/
String query="begin :1 :=itfuture.f_getOnePage(:2,:3,:4);end;";
      CallableStatement cs=open.conn.prepareCall(query);
      cs.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
      cs.setInt(2,1);
      cs.setInt(3,5);
      cs.setString(4,null);
      cs.execute();
      ResultSet rs=(ResultSet)cs.getObject(1);