1不是SElECTed表达式
莫名其妙的报错信息。。。“ORA-01791:不是SElECTed表达式”
经分析，如果在ORDER BY中指定多个列,结果将先按照子句中的第一个列排序,然后第二个,依此类推
在 SELECT中未出现的列名也可用于ORDER BY 子句中,主要TABLE中有就行,但如果SELECT子句中出现了DISTINCT关键字,则只能用出现过的列名,而且如果SELECT子句中使用了任何运算符,在ORDER BY 子句中必须保持和SELECT子句中表达式完全一致,否则出现ORA-01791 ERROR
所以分析以后把上面的句子写成这样：
query="select distinct area_no, dwbm,dwmc,role_no,zgbm_no from s_mtab where role_no between 2 and 3 and (substr(zgbm_no,1,4)='6101' or dwbm=6101) and area_no is not nullorder by area_no ";
2恢复误删TABLE
曾经不小心把开发库的数据库表全部删除，当时吓的要死。结果找到下面的语句恢复到了1个小时之前的数据！很简单。
注意使用管理员登录系统：
select * from 表名 as of timestamp sysdate-1/12 //查询两个小时前的某表数据！既然两小时以前的数据都得到了，继续怎么做，知道了吧。。
如果drop了表，怎么办？？见下面：
drop table 表名;
数据库误删除表之后恢复:（绝对ok，我就做过这样的事情，汗）不过要记得删除了哪些表名。
flashback table 表名 to before drop;
3查询得到当前数据库中锁，以及解锁：
查锁
SELECT s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL;
解锁
alter system kill session 'sid,serial';
如果解不了。直接倒os下kill进程kill －9 spid
ORA-28000:账户被锁定
因为密码输入错误多次用户自动被锁定.
解决办法:alter user user_name account unlock;
4关于查询数据库用户，权限的相关语句：
查看所有用户：
- select * from dba_user;
- select * from all_users;
- select * from user_users;
查看用户系统权限：
- select * from dba_sys_privs;
- select * from all_sys_privs;
- select * from user_sys_privs;
查看用户对象权限：
- select * from dba_tab_privs;
- select * from all_tab_privs;
- select*fromuser_tab_privs;
查看所有角色：
1. select *fromdba_roles;
查看用户所拥有的角色：
- select * from dba_role_privs;
查询oracle中所有用户信息
select * from dba_user;
只查询用户和密码
select username,password from dba_users;
查询当前用户信息
select * from dba_ustats;
查询用户可以访问的视图文本
select * from dba_varrays;
查询数据库中所有视图的文本
select * from dba_views; 查询全部索引
select * from user_indexes; 查询全部表格
select * from user_tables; 查询全部约束 select * from user_constraints; 查询全部对象 select * from user_objects;
查看相关进程在数据库中的会话
Select a.sid,a.serial#,a.program, a.status ,
substr(a.machine,1,20), a.terminal,b.spid
from v$session a, v$process b
where a.paddr=b.addr
and b.spid = &spid;
查看相关会话正在执行的SQL
select sql_textfromv$sqlarea where address =( select sql_address from v$sessionwheresid = &sid );
查询表的结构：表名大写！！
select t.COLUMN_NAME,
t.DATA_TYPE,
nvl(t.DATA_PRECISION, t.DATA_LENGTH),
nvl(T.DATA_SCALE, 0),
c.comments
from all_tab_columns t, user_col_comments c
whEre t.TABLE_NAME = c.table_name
and t.COLUMN_NAME = c.column_name
and t.TABLE_NAME = UPPER('OM_EMPLOYEE_T')
order by t.COLUMN_ID
5 很基础的语句
1.在数据字典查询约束的相关信息：
SELECT constraint_name, constraint_type,search_condition
FROM user_constraints WHERE table_name = 'EMPLOYEES';
//这里的表名都是大写！
2对表结构进行说明：
desc Tablename
3查看用户下面有哪些表
select table_name from user_tables;
4查看约束在那个列上建立：
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';
5结合变量查找相关某个表中约束的相关列名：
select constraint_name,column_name from user_cons_columns where table_name = '&tablename'
6查询数据字典看中间的元素：
SELECT object_name, object_type
FROM user_objects
WHERE object_name LIKE 'EMP%'
OR object_name LIKE 'DEPT%'
7查询对象类型：
SELECT DISTINCT object_type FROM user_objects ;
8改变对象名：（表名，视图,序列）
rename emp to emp_newTable
9添加表的注释：
COMMENT ON TABLE employees IS 'Employee Information';
10查看视图结构：
describe view_name
11在数据字典中查看视图信息：
select viewe_name,text from user_views
12查看数据字典中的序列：
select * from user_sequences
13得到所有的时区名字信息：
select * from v$timezone_names
14显示对时区‘US/Eastern’的时区偏移量
select TZ_OFFSET('US/Eastern') from DUAL--dual英文意思是‘双重的’
显示当前会话时区中的当前日期和时间:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';--修改显示时间的方式的设置
ALTER SESSION SET TIME_ZONE = '-5:0';--修改时区
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;--真正有用的语句！
SELECT CURRENT_TIMESTAMP FROM DUAL;--返回的时间是当前日期和时间，含有时区
SELECT CURRENT_TIMESTAMP FROM DUAL;--返回的时间是当前日期和时间，不含有时区！！！
15显示数据库时区和会话时区的值：
select datimezone,sessiontimezone from dual;
16普通的建表语句：
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
17使用子查询建立表:
CREATE TABLE dept80
AS SELECT employee_id, last_name,
salary*12 ANNSAL,
hire_date FROM employees WHERE department_id = 80;
18添加列：// alter table EMP add column (dept_id number(7))；错误！！
alter table EMP add (dept_id number(7));
19删除一列：
alter table emp drop column dept_id;
20添加列名同时和约束：
alter table EMP add (dept_id number(7)
constraint my_emp_dept_id_fk references dept(ID));
21改变列：//注意约束不能够修改 的！！
alter table dept80 modify(last_name varchar2(30));//这里使用的是modify而不是alter!
22增加一行：
insert into table_name values();
23添加主键:
alter Table EMP add constraint my_emp_id_pk primary key (ID);
24添加一个有check约束的新列：
alter table EMP
add (COMMISSION number(2) constraint emp_commission_ck check(commission>0))
24创建视图：
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees WHERE department_id = 80;
25找到工资最高的5个人。（top-n分析）（行内视图）
select rownum,employee_id from (select employee_id,salary from
employees order by salary desc)
where rownum<5;
26建立同义词：
create synonym 同义词名 for 原来的名字
或者 create public synonym 同义词名 for 原来的名字
27建立序列：（注意，这里并没有出现说是哪个表里面的序列！！）
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE
28使用序列：
insert into dept(ID,NAME) values(DEPT_ID_SEQ.nextval,'Administration');
29建立索引：//默认就是nonunique索引，除非使用了关键字：unique
CREATE INDEX emp_last_name_idx ON employees(last_name);
30建立用户：（可能有错，详细查看帮助）
create user username(用户名)
identified by oracle(密码)
default tablespace data01(表空间名//默认存在system表空间里面)
quota 10M(设置大小，最大为unlimited) on 表空间名//必须分配配额！
31创建角色：create ROLE manager
赋予角色权限：grant create table,create view to manage
赋予用户角色：grant manager to DENHAAN,KOCHHAR( 两个用户)
32分配权限：
GRANT update (department_name, location_id)
ON departments
TO scott, manager;
回收权限
REVOKE select, insert
ON departments
FROM scott;
6常用应用语句
group语句：和高级的应用语句
SELECT department_id, job_id,SUM(salary),COUNT(employee_id)FROM employees
GROUP BY department_id, job_id ;
使用having进行约束：
group by rollup:对n列组合得到n+1种情况
SELECT department_id, job_id,SUM(salary)FROM employees WHERE department_id < 60GROUP BY ROLLUP(department_id, job_id);
group by cube:得到2的n次方种情况
SELECT department_id, job_id,SUM(salary)FROM employees WHERE department_id < 60GROUP BY CUBE (department_id, job_id) ;
使用grouping得到一行中构成列的情况，只是返回1和0：是空的话就返回1，否则返回0(注意不要弄反了！)
SELECT department_id DEPTID, job_id JOB,SUM(salary),GROUPING(department_id) GRP_DEPT,GROUPING(job_id) GRP_JOB
FROM employeesWHEREdepartment_id < 50 GROUP BYROLLUP(department_id, job_id);
grouping sets:根据需要得到制定的组合情况
SELECT department_id, job_id, manager_id,avg(salary)FROMemployees GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));
exists语句的使用
SELECT employee_id, last_name, job_id, department_id
FROM employeesouter--下面的 exists里面的select选择出来的是随便的一个字符或者数字都可以
WHERE EXISTS (SELECT 'X' FROM employees WHERE manager_id = outer.employee_id);
WITH
dept_costs AS (--定义了一个临时的表
SELECT d.department_name,SUM(e.salary) AS dept_total--其间定义了一个临时的列dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*)AS dept_avg
FROM dept_costs)--这里的第二张临时表里面就引用了前面定义的临时表和之间的列！
SELECT * FROM dept_costs WHERE dept_total > (SELECTdept_avg FROM avg_cost) ORDERBY department_name;---最后的查询语句中使用了前面的临时表
遍历树
SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;--自底向上的遍历树
导入导出dmp文件
imp 用户名/密码@数据库 ignore=y file=备份文件 log=D:\DBtest\db_bak\imp.log
exp system/manager@TEST file=d:\daochu.dmp full=y
大对象字段blob:查看blob字段的大小
select dbms_lob.getLength(字段名) from表名
创建一个只允许在工作时间访问的视图
create or replace view newviewemp
as
select * from 表名
where exists(select 1 from dual where sysdate >=
to_date(to_char(sysdate, 'yyyy-mm-dd ') ||'08:00:00', 'yyyy-mm-dd hh24:mi:ss')
and sysdate < to_date(to_char(sysdate,'yyyy-mm-dd ') || '18:00:00','yyyy-mm-dd hh24:mi:ss'))
存储过程中执行ddl语句
Create Or Replace Procedure My_Proc As Sqlddl Varchar2(1000); Begin Sqlddl := 'create table MyTable(ID Number(5), Name Varchar2(20))'; Dbms_Output.Put_Line(Sqlddl); Execute Immediate Sqlddl; End;
7 oracle创建表空间
注意点：
1.如果在PL/SQL 等工具里打开的话，直接修改下面的代码中[斜体加粗部分]执行
2.确保路径存在，比如【D:\oracle\oradata\Oracle9i\】也就是你要保存文件的路径存在
create temporary tablespace user_temp
tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
create tablespace test_data
logging
datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
create user username identified by password
default tablespace user_data
temporary tablespace user_temp;
grant connect,resource,dba to username;
关于oracle的锁表解决
1查询哪些对象被锁：select object_name,machine,s.sid,s.serial# from v$locked_object l,dba_objects o ,v$session s where l.object_id = o.object_id and l.session_id=s.sid;
2.下面的语句用来杀死一个进程：alter system kill session '524,1095'; (其中24,111分别是上面查询出的sid,serial#)
3.再一次查询目前锁定的对象，若发现以上方法不能解除锁定的表，则用以下方法：
3.1 执行下面的语句获得进程（线程）号： select spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=524 （524是上面的sid）
3.2 在OS上杀死这个进程（线程）： unix上，用root身份或是相应的oracle身份执行命令: #kill -9 9846（9846 上一步查询出的spid） windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为： orakill sid thread PS: sid：表示要杀死的进程属于的实例名 thread：是要杀掉的线程号，即第3步查询出的spid。 exp：
c:>orakill orcl 9846
ORA-24324：未初始化服务句柄，不允许此值，正在关闭 - 不允许连接
今天使用Oracle遇到了这个问题ora-01033:oracle initialization or shutdown in progress，经过分析研究终于解决了，记下来。
首先：问题的产生原因，出现这个错误是因为我将\oracle\product\10.1.0\oradata\oral下的一个文件删除掉后出现的。
分析：这个错误的原因应该是Oracle在启动后，用户登录时是要将方案中原有配置信息装载进入，装载过程中配置中有文件未找到，所以就报出错误。
解决过程：
C:\Documents and Settings\DHai>sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on 星期一 9月 28 14:35:38 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL> connect sys/123 as sysdba;
已连接。
SQL> shutdown normal
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 171966464 bytes
Fixed Size
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 6: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORAL\FSCREDIT40'
SQL> alter database datafile 6 offline drop;
数据库已更改。
SQL> alter database open resetlogs;
数据库已更改。
SQL> alter database datafile 6 offline drop;
数据库已更改。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01531: 例程已打开数据库
SQL>shutdown normal
提示：数据库已经关闭
已经卸载数据库
ORACLE 例程已经关闭
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 171966464 bytes
Fixed Size
到此，问题解决了。
=============================================
============================================
网上另外的文章
=====================================
============================
oracle一个问题的解决方法：Error:ORA-01033:ORACLE initialization or shutdown in progress2009-07-14 18:42从网上找了一些资料，最后加上自己的实践终于解决了
后来发现原因既然是：用系统清理工具把系统垃圾清理了一番,结果在打开oracle 数据库时出现了错误:
解 决方法在DOS环境下sqlplus /NOLOGSQL>connect sys/sys as sysdbaSQL>shutdown normalSQL>startup mountSQL>alter database open;第 1 行出现错误:ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件ORA-01110: 数据文件 6: ''D:\ORACLE\ORADATA\ORAGWH\INDX01.DBF''出现这种问题时继续输入SQL> conn sys/sys as sysdba;已连接。SQL> alter database datafile 6 offline drop;直到用scott登陆为止SQL> conn scott/tiger然后接着输入即可SQL>shutdown normalSQL>startup本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/henlson/archive/2007/07/18/1696966.aspx
在输入上述命令的时候，可能会出现下面的问题：
ORA-00313: 无法打开日志组 1 (线程 1) 的成员
解决方法如下：
SQL> connect / as sysdba;
已连接。
SQL> shutdown immediate;
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size
Variable Size
Database Buffers
Redo Buffers
数据库装载完毕。
ORA-00313: 无法打开日志组 1 (线程 1) 的成员
ORA-00312: 联机日志 1 线程 1: 'C:\ORACLE\ORADATA\ORCL\REDO01.LOG'
查看C:\ORACLE\ORADATA\myoracle\REDO01.LOG还在,但是REDO02.LOG,REDO03.LOG被误删了.
赶紧换个例程OEMREP试试,结果还是一样的错误!
蒙了,难道要重装数据库不成?有什么办法可以解决这个问题呢?
解决办法:
SQL> shutdown immediate;
ORA-01109: 数据库未打开
已经卸载数据库。
SQL> startup mount;
ORACLE 例程已经启动。
SQL>select * from v$log;
GROUP#
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
已选择3行。
alter database clear unarchived logfile group 1;
数据库已经更改.
alter database clear unarchived logfile group 2;
数据库已经更改.
alter database clear unarchived logfile group 3;
数据库已经更改.
SQL>shutdown;
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>startup;
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size
Variable Size
Database Buffers
Redo Buffers
数据库装载完毕。
数据库已经打开。
问题解决!
但是,在对myoracle 例程进行同样的操作后，也不知道是哪里出了问题,
SQL>startup;
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size
Variable Size
Database Buffers
Redo Buffers
数据库装载完毕。
ORA-03113: 通信通道的文件结束
在网上查找资料,出现这样的问题原因很多,一般是网络不通,或者是init.ora、C:\oracle\ora92\network\admin \sqlnet.ora或tnsnames.ora有问题.显然这些不适合我.我的两个例程,一个可以正常使用,另外一个有问题的!
在我关闭数据库后,重新打开,执行:
SQL>connect "/as sysdba";
SQL>select * from scott.emp;
ORA-27101 shared memory realm does not exist -
出现这个错误也有很多解释,一般要检查init.ora文件的内存配置是否有问题,但是在没有打开数据库的时候执行命令,也会出现这样的错误提示,我的属于后者,是由于没有挂载数据库造成的，这里提醒一下.
ORA-24324: 未初始化服务句柄
ORA-01041: 内部错误，hostdef 扩展名不存在
这是越来越乱了,无奈之下,只好重头再来整理一遍,从重新配置日志文件开始,操作一样，但是奇迹出现了,错误消失了,也不提示通信通道文件结束了，数据库正常了,呵呵,但是很晕!
解决问题知识整理:
首先，执行下面的语句看看group1是不是current日志组
select * from v$log;
如果被删除的日志不是当前日志组的成员，比较简单
alter database clear logfile group 1;
如果你的库是非归档的，或许要用下面的语句
alter database clear unarchived logfile group 1;
如果是当前日志组损坏，但是数据库是正常关闭的，上面的办法也可以使用
如果日志组中有活动的事务，那么可能需要利用备份来恢复了:过程如下:
解决过程：Microsoft Windows XP [版本 5.1.2600](C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\5201314>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 5月 27 11:19:52 2006Copyright (c) 1982, 2005, Oracle. All rights
reserved.
SQL> conn sys/dd as sysdba
已连接。
SQL> startup mount
ORA-01081: 无法启动已在运行的 ORACLE - 请首先关闭它
SQL> recover database until time '2006-05-19 13:45:02';
完成介质恢复。
SQL> alter database open resetlogs;
数据库已更改。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 205520896 bytes
Fixed Size 1248092 bytes
Variable Size 79692964 bytes
Database Buffers 117440512 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
数据库已经打开。
扫一扫
966
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
