oracle数据库进去以后,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/，如需转载，请注明出处，否则将追究法律责任。