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);