oracle数据库开发的一些经验积累2 [转]

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: TNSscreen.width/2)this.style.width=screen.width/2;" border="0" />acket writer failure（包写入程序失败）

(1) 这个错误在客户端遇到过，通常重新连接一下服务器就好了。
服务器重新启动的时候，在client也会遇到该错误。
这个错误你是在server还是client上遇到的？最常用的办法就是加上跟踪，查看一下 跟踪记录，分析分析错误的原因。
网络问题也会出现该错误，比如网络路由没有配置好。
(2) 安装的杀毒软件导致的
(3) 服务器端的IP是否被改动
(4) 最后不行的话，重新创建监听器

62、ORACLE服务不能自动启动的解决办法

把ORACLEHOMEnetworkADMINsqlnet.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:ORACLEORADATAORCLtest.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);

1、分析表
analyze table mzbs.db_code ESTIMATE STATISTICS SAMPLE 20 PERCENT;


2、表空间管理和用户管理

--查看表空间和数据文件

select file_name,tablespace_name,autoextensible from dba_data_files;

--数据表空间

CREATE TABLESPACE USER_DATA
LOGGING
DATAFILE 'D:ORACLEORADATAORCLtest.DBF' SIZE 50m REUSE ,
'c:USERS01112.DBF' SIZE 50m REUSE
AUTOEXTEND
ON NEXT 1280K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL


--修改表空间数据文件的路径

ALTER TABLESPACE app_data
RENAME
DATAFILE '/DISK4/app_data_01.dbf'
TO '/DISK5/app_data_01.dbf';


ALTER DATABASE
RENAME FILE '/DISK1/system_01.dbf'
TO '/DISK2/system_01.dbf';


--临时表空间

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 TABLESPACE tablespacename
MINIMUM EXTENT 2M;


ALTER TABLESPACE tablespacename
DEFAULT STORAGE (
INITIAL 2M
NEXT 2M
MAXEXTENTS 999 );


--表空间联机/脱机/只读

ALTER TABLESPACE tablespacename OFFLINE/ONLINE/READ ONLY;


--修改数据文件大小
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;


3、表的管理

--创建表

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
PCTFREE 30
PCTUSED 50
STORAGE(NEXT 500K
MINEXTENTS 2
MAXEXTENTS 100);

ALTER TABLE tablename
ALLOCATE EXTENT(SIZE 500K
DATAFILE '/DISK3/DATA01.DBF');

--把表移到另一个表空间

ALTER TABLE TABLENAME MOVE TABLESPACE TABLESPACENAME;


--回收空闲的空间(回收到High-water mark)
全部回收需要TRUNCATE TABLE tablename

ALTER TABLE tablename
DEALLOCATE UNUSED;

--删除表(连同所用constraint)

DROP TABLE tablename
CASCADE CONSTRAINTS;


--给表增加列

ALTER TABLE TABLENAME
ADD COLUMN COLUTYPE DEFAULT(value) NOT NULL;


--删除表中的列

ALTER TABLE tablename
DROP COLUMN columnname;


ALTER TABLE tablename
DROP COLUMN columnname
CASCADE CONSTRAINTS CHECKPOINT 1000;


--标记列不可用

ALTER TABLE tablename
SET UNUSED COLUMN columnname
CASCADE CONSTRAINTS;

--删除标记为不可用的列

ALTER TABLE tablename
DROP UNUSED COLUMNS CHECKPOINT 1000;

--继续删除列选项

ALTER TABLE tablename
DROP COLUMNS CONTINUE CHECKPOINT 1000;

--把表放到BUFFER_POOL中去

ALTER TABLE tablename
STORAGE (BUFFER_POOL RECYCLE);

--避免动态分配EXTENT

ALTER TABLE tablename ALLOCATE EXTENT;


--把表放到CACHE中去

ALTER TABLE tablename ALLOCATE CACHE/NOCACHE;


4、索引管理


--创建索引

CREATE INDEX indexname ON TABLENAME(COLUMNNAME);

CREATE INDEX indexname ON TABLENAME(COLUMNNAME) TABLESPACE TABLESPACENAME;

--重新建立索引

ALTER INDEX indexname REBUILD TABLESPACE TABLESPACE;


--索引分配参数

ALTER INDEX indexname
STORAGE(NEXT 400K
MAXEXTENTS 100);


--释放索引空间

ALTER INDEX indexname
ALLOCATE EXTENT (SIZE 200K
DATAFILE '/DISK6/indx01.dbf');

ALTER INDEX indexname
DEALLOCATE UNUSED;

--重新整理索引表空间碎片

ALTER INDEX indexname COALESCE;

--删除索引

DROP INDEX indexname


--把索引放到BUFFER_POOL中

ALTER INDEX cust_name_idx
REBUILD
STORAGE (BUFFER_POOL KEEP);


5、约束管理


--建立主键

ALTER TABLE TABLENAME
ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY(COLUMN1,COLUMN2)


--使约束无效

ALTER TABLE TABLENAME ENABLE NOVALIDATE CONSTRAINT constraintname;

ALTER TABLE TABLENAME ENABLE VALIDATE CONSTRAINT constraintname;

--删除约束

ALTER TABLE tablename DROP CONSTRAINT constraintname;

DROP TABLE tablename CASCADE CONSTRAINTS;(删除表后将所用的外键删除)


--给列增加缺省值

ALTER TABLE TABLENAME
MODIFY columnname DEFAULT(value) NOT NULL;


--给表增加外键
ALTER TABLE tablename
ADD CONSTRAINT constraintname
FOREIGN KEY(column) REFERENCES table1name(column1);


6、安全策略

--加密传输

把客户端环境变量ora_encrypt_login设为true
把服务器端参数dblink_encypt_login设为true

--数据库管理员安全策略

a、建库后立即修改SYS/SYSTEM的口令(9.2后必须修改其口令)
b、只有数据库管理员才能以SYSDBA登录系统
c、建立不同角色的管理员，分配不同的权限

比如：对象创建于维护
数据库的调整与维护
创建用户分配角色
启动关闭
恢复备份

--应用开发者的安全策略

a、开发者的特权只能在测试开发的数据库中赋予权限
b、自由开发者、受控开发者
自由开发者:create tableindexprocedurepackage
受控开发者:没有以上权限


7、日志文件管理

--切换日志文件

ALTER SYSTEM SWITCH LOGFILE;

--增加日志文件

ALTER DATABASE ADD LOGFILE
('/DISK3/log3a.rdo',
'/DISK4/log3b.rdo') size 1M;

--增加日志成员

ALTER DATABASE ADD LOGFILE MEMBER
'/DISK4/log1b.rdo' TO GROUP 1
'/DISK4/log2b.rdo' TO GROUP 2;

--删除日志文件

ALTER DATABASE DROP LOGFILE GROUP 3;

--删除日志成员

ALTER DATABASE DROP LOGFILE MEMBER '/DISK4/log2b.dbf';

--清除日志文件内容

ALTER DATABASE CLEAR LOGFILE '/DISK3/log2a.rdo';[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/503782/viewspace-983261/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/503782/viewspace-983261/