select a.constraint_name, a.table_name, b.constraint_name   from user_constraints a, user_constraints b where a.constraint_type = R   and b.constraint_type = P   and a.r_constraint_name = b.constr

系统：XP，在CMD下输入下面命令c:>sqlplus /nolog;SQL> conn /as sysdba已连接。SQL> alter user system identified by password;

首先以sysdba身份登录sqlplus connect system/orcl as sysdba;然后修改参数1.sga_target不能大于sga_max_size，可以设置为相等。2.SGA加上PGA等其他进程占用的内存总数必须小于操作系统的物理内存。alter system set sga_target=150M scope=spfile;alter ...

导入imp prop/prop@orcl file=d:\servant.dmp fromuser=servant touser=prop prop/prop 登录的可户名 file:数据库文件名fromuser:导出数据库时所使用的用户名touser:要把这个数据库给本地的那个用户导出exp civilinterview/civilinterview@t.12...

[code="sql"]select i.sid,i.sname,i.birthday,i.schooltime,i.sphone,c.classname,a.assnname,sum(decode(subject,'语文',s.score,0)) as chin, sum(decode(subject,'数学',s....

[code="sql"]insert into ti_examinee_group_link(GROUP_LINK_ID, EXAMINEE_ID, group_id)select seq_ti_examinee_group_link.nextval, t.examinee_id,1 from ti_examinee_info t ...

Oracle语句优化[url]http://coolvinson.iteye.com/blog/526437[/url]

首先建表，序列[code="sql "]drop table student;drop table st_class;drop table st_scoredrop sequence SEQ_st_class;drop sequence SEQ_student;drop sequence seq_st_score;--创建学生表create table stud...

使用SQL查看表的主键：[code="sql"]select column_name from user_cons_columns wherE CONSTRAINT_NAME in (select CONSTRAINT_NAME from user_constraints where table_name ='TABLE_NAME' and CONSTRAINT_TYPE='P');[/cod...

分页中使用，查询表中第2行到第5行的数据[code="SQL"]select * from(select id,name,rownum as myrow from tuse where rownum1;[/code]

[code="sql"] select a.* from (select SCORE_ID,EX_EB_ID,EB_NAME,ss.ADMISSION_TICKET,SCOPE,EXAM_TIME,UPDATE_TIME, ei.examinee_id,ei.examinee_name, ei.register_dept,ei.dept_code,ei.r...

[code="SQL"]select username,count(username) from v$session t where username is not null group by username; [/code]

[code="SQL"]//建表空间create tablespace TS_UPCFJ_USERdatafile 'f:/oracle/tablespace/TS_UPCFJ_USER.dbf' size 200M autoextend on next 100M maxsize UNLIMITED extent management ...

[code="SQL"]select count(*) from g_cardapplydaliey a group by a.g_id having count(*)>=2[/code]

一、(由于数据文件被删) 数据库启动不了! 即登录不了数据库;如下图 [img]http://dl.iteye.com/upload/attachment/364208/d5a6d56d-08a1-35ff-a090-369df717eca6.jpg[/img]解决方法:1) cmd 2) sqlplus “sys/oracle as sysdba” --即以sysdb...

SQL> drop user a cascade;drop user a cascade*ERROR 位于第 1 行:ORA-01940: 无法删除当前已连接的用户SQL> select username,sid,serial# from v$session;USERNAME SID SERIAL#...

oracle中使用on delete cascade和on delete set null来建立外键其面我们介绍了创建外键约束时如果使用oracle默认的创建方式，在删除被参照的数据时，将无法被删除，这一点在oracle9i中给了我们更多灵活的选择，我们可是使用on delete cascade和 on delete set null关键字来决定删除被参照数据时是否要将参照这个数据的那些数据一...

[code="sql"]select a.constraint_name, a.table_name, b.constraint_name from user_constraints a, user_constraints b where a.constraint_type = 'R' and b.constraint_type = 'P' and a.r_cons...

原来是SQL语句中加了双引号的原因，将值改为单引号就行了！看来对于SQL中，单引号与双引号的区别还得好好复习一下了！另外一个可能的原因是，将数据库表的列名写错了！...

//删除用户drop user civilinterview cascade;//创建用户并指定表空间 create user username identified by password default tablespace test_data temporary tablespace test_temp; //给用户授予权限 grant connect,resource to u

oracle中使用on delete cascade和on delete set null来建立外键其面我们介绍了创建外键约束时如果使用oracle默认的创建方式，在删除被参照的数据时，将无法被删除，这一点在oracle9i中给了我们更多灵活的选择，我们可是使用on delete cascade和 on delete set null关键字来决定删除被参照数据时是否要将参照这个数据的那些数据一并...

还有个变通的做法Loop...if(true) thengoto nextloop;end if;...>null;end loop;

c:>sqlplus /nolog;SQL> conn /as sysdba已连接。SQL> alter user system identified by password;

首先以sysdba身份登录sqlplus connect system/orcl as sysdba;然后修改参数1.sga_target不能大于sga_max_size，可以设置为相等。2.SGA加上PGA等其他进程占用的内存总数必须小于操作系统的物理内存。alter system set sga_target=150M scope=spfile;alter system set s

方法一.select a.idcard,SUM( CASE WHEN a.subject_code=2 THEN a.SCOPE ELSE 0  END) ,SUM( CASE WHEN a.subject_code=3 THEN a.SCOPE ELSE 0  END),SUM( CASE WHEN a.subject_code=4 THEN a.SCOPE ELSE 0  END),SUM

Oracle语句优化５３个规则详解(1) Oracle sql 性能优化调整    1. 选用适合的ORACLE优化器         ORACLE的优化器共有3种： a. RULE （基于规则）   b. COST （基于成本） c. CHOOSE （选择性）     设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE

 select a.*    from (select SCORE_ID,EX_EB_ID,EB_NAME,ss.ADMISSION_TICKET,SCOPE,EXAM_TIME,UPDATE_TIME,               ei.examinee_id,ei.examinee_name, ei.register_dept,ei.dept_code,ei.register_station,

select i.sid,i.sname,i.birthday,i.schooltime,i.sphone,c.classname,a.assnname,sum(decode(subject,语文,s.score,0)) as chin,                                     sum(decode(subject,数学,s.score,0)) as mat

运行该函数后就可以直接在SQL中使用[code="sql"]CREATE OR REPLACE TYPE ty_str_array IS TABLE OF VARCHAR2 (4000);CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN ty_st...

方法一.[code="SQL"]select a.idcard,SUM( CASE WHEN a.subject_code=2 THEN a.SCOPE ELSE 0 END) ,SUM( CASE WHEN a.subject_code=3 THEN a.SCOPE ELSE 0 END),SUM( CASE WHEN a.subject_co...

我们在写SQL的时候经常会用到别的表的数据，都要通过关联来取，但如果关联表多的话，会SQL变的难于读懂：[code="sql"]create or replace function f_sys_get_state(in_table_name VARCHAR2, --TS_STATE中配置的表名 in_...

有个变通的做法[code="sql"]Loop...if(true) thengoto nextloop;end if;...null;end loop;[/code]

[code="sql"]select t.examinee_id, t.batch_id, t.signcompany, t.signcompany_code, t.signposition, t.signposition_code, (select count(*) from ti_examinee_...

oracle怎么实现左联，右联与外联?9i以前写法 9i以后10g 11g的写法左联：select a.id,a.name,b.address from a,bwhere a.id=b.id(+)右联:select a.id,a.name,b.address from a,bwhere a.id(+)=b.id外联SELECT a.id,a.name,b.address...