53、Oracle常见服务
几个主要的:
OracleOraHome81TNSListener 监听服务
OracleServiceSID ORACLE服务
OracleOraHome81Agent 智能代理服务
OracleOraHome81CMan 连接管理服务
OracleOraHome81HTTPServer APACHE WEB 服务
OracleOraHome81ManagementServer ORACLE 企业管理器服务
OracleOraHome81Names ORACLE命名服务
剩下的也不常用。
54、ORACLE的热备份
在不关闭数据库的时候进行ORACLE的备份。
原理停复杂的,你去找本书看看吧。
举个简单的例子:备份表空间USERS
ALTER TABLESPACE USERS BEGIN BACKUP
COPY USERS TABLESPACE 的数据文件到备份目录
ALTER TABLESPACE USERS END BACKUP
55、导致索引不起作用的解决办法
你的问题我刚处理过,是由optimizer_mode参数引起的,该参数的默认值为choose,即为如表有statis则查询走基于cost的方式,否则走基于rule的方式,因些你可以有以下几个解决方法。
(1)、简单的在init<sid>.ora中设optimizer_mode=rule,重起数据库。
(2)、使用analyze table table_name(索引基表) delete statistics;
(3)、最后一个万能办法,将表和索引drop掉,重建。
56、关于数据库进程的问题。
(1).查看相关进程在数据库中的会话
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;
(2).查看数据库中被锁住的对象和相关会话
select a.sid,a.serial#,a.username,a.program,
c.owner, c.object_name
from v$session a, v$locked_object b, all_objects c
where a.sid=b.session_id and
c.object_id = b.object_id;
(3).查看相关会话正在执行的SQL
select sql_text from v$sqlarea where address =
( select sql_address from v$session where sid = &sid );
57、查看IP地址
select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;
58、运行SQLPLUS时不用输入用户名和密码,进入之后使用CONNECT
SQLPLUS /NOLOG
SQL>CONNECT SCOTT/TIGER
59、查看当前会话
userenv() 函数
select userenv('language') from dual 字符集
select userenv('isdba') from dual 是否DBA
select userenv('sessionid') from dual sessionid
select userenv('TERMINAL') from dual 客户端名字
select userenv('INSTANCE') from dual 实例数
SYS_CONTEXT() 函数
select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') from dual; 当前模式
select SYS_CONTEXT('USERENV','CURRENT_SCHEMAID') from dual; 当前模式ID
select SYS_CONTEXT('USERENV','CURRENT_USER') from dual; 当前用户
select SYS_CONTEXT('USERENV','DB_NAME') from dual; 数据库
select SYS_CONTEXT('USERENV','HOST') from dual; 主机
..........
60、删除重复列的方法
(1) DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);
(2) create table table2 as select distinct * from table1;
drop table1;
rename table2 to table1;
(3) Delete from mytable where rowid not in(
select max(rowid) from mytable
group by column_name );
(4) delete from mytable t1
where exists (select 'x' from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
...
and t2.rowid > t1.rowid);
61、ORA-12571: TNS:packet writer failure(包写入程序失败)
(1) 这个错误在客户端遇到过,通常重新连接一下服务器就好了。
服务器重新启动的时候,在client也会遇到该错误。
这个错误你是在server还是client上遇到的?最常用的办法就是加上跟踪,查看一下 跟踪记录,分析分析错误的原因。
网络问题也会出现该错误,比如网络路由没有配置好。
(2) 安装的杀毒软件导致的
(3) 服务器端的IP是否被改动
(4) 最后不行的话,重新创建监听器
62、ORACLE服务不能自动启动的解决办法
把ORACLEHOME/network/ADMIN/sqlnet.ora
文件中的 sqlnet.authentication_service=(nts)
注释掉就可以了
63、不完全的时间点恢复
shutdown immediate
copy 备份文件到需要恢复的目录下
startup mount
recover database until time '2002-12-26 09:00:00'
alter database open resetlogs
自己仔细检查一下,不会发生这样的问题的。
64、oracle如何设置查询超时
select /*+ timeout 30*/ * from veryLargeTable
65、修改字符集
(1)、ALTER DATABAE CHARACTER SET SIMPLIFIED CHINESE_CHINA.ZHS16GBK ;
(2)、update props$ set value$='ZHS16CGB231280'
where name='NLS_CHARACTERSET';
update props$ set value$='ZHS16CGB231280'
where name='NLS_NCHAR_CHARACTERSET';
建议不使用(2)
注意:
(1)、执行ALTER DATABASE CHARACTER SET必须有SYSDBA权限,并且在STARTUP RESTRICT模式下执行
(2)、原字符集必须是目标字符集的一个真子集(就是浪子所说的只能从WE8ISO8859P1转到ZHS16GBK的原因)
(3)、CLOB字段装换可能有问题,建议在转换以前把有CLOB字段的表导出后DROP,转换以后再导回
(4)、该转换不可逆,所以在做这个操作以前建议做数据库全备份
66、修改数据库名字
(1)、启动svrmgrl,以文本方式备份控制文件
oracle>svrmgrl
svrmgrl>connect internal
svrmgrl>alter system backup controlfile to trace
(2)、编辑产生的跟踪文件,在udump目录下
改CREATE CONTROLFILE REUSE DATABASE 'CTC' NORESETLOGS ARCHIVELOG
中的REUSE为SET
然后把create controlfile这段语句拷出
(3)、正常宕库,后启动到nomount下
svrmgrl>shutdown immediate
svrmgrl>startup nomount
(4)、执行create controlfile那段语句
(5)、打开数据库
svrmgrl>alter database open
如提示用resetlogs选项则使用
svrmgrl>alter database open resetlogs
(8)、相应修改初始化参数
67、rownum的用法
select * from (select t.*,rownum id from dept t)
where id between 1 and 20
68、oracle的内部参数
SELECT a.ksppinm NAME,
b.ksppstdf default_val,
a.ksppdesc DESCRIPTION
FROM x$ksppi a,
x$ksppcv b
WHERE a.indx=b.indx
AND substr(a.ksppinm,1,1)='_'
ORDER BY a.ksppinm
69、9i安装时报areasqueries错误的解决办法
包括IAS 和 IDS
把安装源文件目录全部改为英文字母或数字
注意:不能是中文的路径
70、我如何知道一个表空间还有多少可以用
(1)、
SELECT upper(f.tablespace_name) 表空间名,
d.Tot_grootte_Mb '表空间大小(M)',
d.Tot_grootte_Mb - f.total_bytes '已使用空间(M)',
round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) '使用比',
f.total_bytes '空闲空间(M)',
f.max_bytes '最大块(M)'
FROM
(SELECT tablespace_name,
round(SUM(bytes)/(1024*1024),2) total_bytes,
round(MAX(bytes)/(1024*1024),2) max_bytes
FROM sys.dba_free_space
GROUP BY tablespace_name) f,
(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
FROM sys.dba_data_files dd
GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC
(2)、select tablespace_name,round(sum(bytes)/1024/1024,2) 'M' from dba_free_space
group by tablespace_name
71、creck pl/sql developer 的方法
(1)、安装pl/sql developer
(2)、用UltraEdit将程序PLSQLDev.exe打开
(3)、将UltraEdit设置为16进制模式
(4)、查找串:BA 1E 00 00 00 2B D0
修改:2B D0 为:4A 90
(5)、存盘退出
(6)、运行PLSQLDev.exe,如果提示你还有29天的时间可用,那就恭喜你了!
72、使索引无效
ALTER INDEX idx UNUSABLE;
ALTER INDEX idx_acctno DISABLE;(only to a function based index)
73、在SQLPLUS中给指定用户进行 set autotrace on/off
以SCOTT用户为例:
SQL>CONNECT scott/tiger
connected.
SQL>@$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL
Table created.
SQL>CONNECT / AS SYSDBA
connected.
SQL>@$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL
drop role plustrace;
Role dropped.
create role plustrace;
Role created.
.
grant plustrace to dba with admin option;
Grant succeeded.
SQL>GRANT PLUSTRACE TO SCOTT;
Grant succeeded.
SQL>CONNECT SCOTT/TIGER
connected.
SQL>set autotrace on
SQL>
74、关于约束的四种状态
Disabled novalidate:当约束使不能时,约束的规则不能强制在列
(包含在约束中)的数据之上。但约束的定义保存在数据字典中。
在执行数据仓库卷起(rollup)或装载且要加快装载过程时该方式
是有用的。
Enabled novalidate:是能无效,该状态的表可以包含非法
的数据,但不可能加入新的非法数据。
Enabled validate:使能有效,一个使能的约束是强制的,表的数据检查
有效
75、在SQLPLUS中调用存储过程
SET SERVEROUTPUT ON
declare
out_param varchar2(100);
begin
your_proc(1,out_param);
dbms_output.put_line(out_param);
end;
/
SET SERVEROUTPUT OFF
75、生成系统表和存储过程的三个文件。
cat*.sql
dbms*.sql
utl*.sql
76、JOB中日期的使用
每个月1号:
last_day(sysdate)+1
每个季度的第一天:
to_date(decode(to_char(sysdate,'q'),'1',to_char(sysdate,'yyyy')||'0101',
'2',to_char(sysdate,'yyyy')||'0401','3',to_char(sysdate,'yyyy')||'0701',
'4',to_char(sysdate,'yyyy')||'1001'),'yyyymmdd')
每天:
sysdate+1
每个星期几:
decode(to_char(sysdate,'w'),'1',sysdate+7,
to_char(sysdate,'w'),'2',sysdate+6,to_char(sysdate,'w'),'3',sysdate+5,
to_char(sysdate,'w'),'4',sysdate+4,to_char(sysdate,'w'),'5',sysdate+3,
to_char(sysdate,'w'),'6',sysdate+2,to_char(sysdate,'w'),'7',sysdate+1)
每个星期x下午三点:interval(21, 'next_day(trunc(sysdate),x+1)+15/24');
每个季度的第一个星期x:
interval(21, 'next_day(trunc(sysdate,''Q''),3),5)');
77、使用execute immediate 的问题
8i以上才支持execute immediate
8.05只能用dbms_sql
最好使用execute immediate
78、ORACLE9i中删除表空间中数据文件的方法
drop tablespace tbsname including contents
79、找出串中的数字
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789')
'Translate example'
FROM DUAL
/
2229
--全是数字的:
select * from 你的表 where translate(你的列,'0123456789',' ')='';
select * from 你的表 where trim(ltrim(rtrim(replace(col_name,'0123456789',' ')))) is null
80、分析表
analyze table mzbs.db_code ESTIMATE STATISTICS SAMPLE 20 PERCENT;
81、表空间管理和用户管理
--查看表空间和数据文件
select file_name,tablespace_name,autoextensible from dba_data_files;
--数据表空间
CREATE TABLESPACE USER_DATA
LOGGING
DATAFILE 'D:/ORACLE/ORADATA/ORCL/test.DBF' SIZE 50m REUSE ,
'c:/USERS01112.DBF' SIZE 50m REUSE
AUTOEXTEND
ON NEXT 1280K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL
--临时表空间
CREATE TEMPORARY
TABLESPACE USER_DATA_TEMP TEMPFILE 'D:/TEMP0111.DBF'
SIZE 50M REUSE AUTOEXTEND
ON NEXT 1024K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1024K
--增加数据文件
ALTER TABLESPACE USER_DATA
ADD DATAFILE 'c:/USERS01113.DBF' SIZE 50M;
ALTER TABLESPACE USER_DATA
ADD DATAFILE 'c:/USERS01114.DBF' SIZE 50M
AUTOEXTEND ON
;
--删除表空间
DROP TABLESPACE USER_DATA INCLUDING CONTENTS;
--修改数据文件大小
ALTER DATABASE
DATAFILE 'c:/USERS01113.DBF' RESIZE 40M;
--创建用户、赋予权限
CREATE USER USER_DATA PROFILE DEFAULT IDENTIFIED BY USER_DATA
DEFAULT
TABLESPACE USER_DATA TEMPORARY
TABLESPACE USER_DATA ACCOUNT UNLOCK;
GRANT CONNECT TO USER_DATA;
GRANT RESOURCE TO USER_DATA;
--把表移到另一个表空间
ALTER TABLE TABLENAME MOVE TABLESPACE TABLESPACENAME;
--创建索引
CREATE INDEX INDEXNAME ON TABLENAME(COLUMNNAME);
CREATE INDEX INDEXNAME ON TABLENAME(COLUMNNAME) TABLESPACE TABLESPACENAME;
--重新建立索引
ALTER INDEX INDEXNAME REBUILD TABLESPACE TABLESPACE;
--创建表
CREAE TABLE TABLENAME
(COLUMN1 COLUTYPE DEFAULT(VALUE) NOT NULL)
(COLUMN2 COLUTYPE DEFAULT(VALUE) NOT NULL);
--建表的索引存储分配
CREATE TABLE summit.employee(id NUMBER(7) CONSTRAINT employee_id_pk PRIMARY KEY DEFERRABLE USING INDEX STORAGE(INITIAL 100K NEXT 100K)
TABLESPACE indx,
last_name VARCHAR2(25) CONSTRAINT employee_last_name_nn NOT NULL,
dept_id NUMBER(7))
TABLESPACE data;
--建立主键
ALTER TABLE TABLENAME
ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY(COLUMN1,COLUMN2)
--使约束无效
ALTER TABLE TABLENAME ENABLE NOVALIDATE CONSTRAINT CONSTRANAME;
ALTER TABLE TABLENAME ENABLE VALIDATE CONSTRAINT CONSTRANAME;
--删除约束
ALTER TABLE TABLENAME DROP CONSTRAINT constraintname;
DROP TABLE TABLENAEM CASCADE CONSTRAINTS;(删除表后将所用的外键删除)
--给表增加列
ALTER TABLE TABLENAME
ADD COLUMN COLUTYPE DEFAULT(VALUE) NOT NULL;
--给列增加缺省值
ALTER TABLE TABLENAME
MODIFY COLUMNNAME DEFAULT(VALUE) NOT NULL;
--给表增加外键
ALTER TABLE TABLENAME
ADD CONSTRAINT CONSTRAINTNAME
FOREIGN KEY(COLUMN) REFERENCES TABLE1NAME(COLUMN1);