oracle存储过程管理方法,三.ORACLE日常管理

1.删除重复数据

用户在进行输入操作时，输入了两条完全一样的记录。当用户想删除这条记录时，系统出错，提示“多条记录被操作”。无法删除记录。

(1)select rowid,其他列名from tablename；(你想进行删除操作的表名)。

(2)记下你想删除的记录所对应的rowid的值

(3)delete from tablename where rowid='rowid的值'；

where条件加上rownum=1就可以删除第一条记录。注意，rownum只能接“=”或“”。其实使用rownum有很大的局限性，比如，只能删除rownum=1的行。

可使用“相关删除”的方法删除重复记录：

delete tname o

where

o.rowid <> (select max(rowid) from tname i

where

i.colname=o.colname);

其中：colname为基表tname的列名,可用多列。

2.检查表是否被锁定的

SELECT A.OWNER,A.OBJECT_NAME,

B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME,B.OS_USER_NAME,B.PROCESS,B.LOCKED_MODE,

C.MACHINE,C.STATUS,C.SERVER,C.SID,C.SERIAL#,C.PROGRAM

FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C

WHERE A.OBJECT_ID = B.OBJECT_ID

AND B.PROCESS = C.PROCESS

ORDER BY 1,2;

杀掉：alter system kill session '149,267';---alter system kill session 'sid,serial#';

linux中：kill -9 spid--spid对应process

3.停止存储过程运行

select * from v$access a where object like

'存储过程名%';--存储过程名为大写字母

select * from v$session where sid=25;--25为上一个查询得到的SID值，可能是多个，这里找到他们对应的serial#值

alter system kill session '25,109';--参数为'sid,serial#'，用于停止这个回话，同时也终止了存储过程

4.移除和停止JOB

一般来说都是通过sql来删除或者停止某个job。

移除JOB：remove

停止JOB：broken

首先通过

select * from user_jobs;

查找出JOB的ID，然后调用

begin

dbms_job.remove(982);

end;

select * from dba_jobs_running----查看正在运行的job

============================

exec dbms_job.broken(:job)停止

exec dbms_job.broken(186,true) //标记位broken

exec dbms_job.broken(186,false)//标记为非broken

exec

dbms_job.broken(186,false,next_day(sysdate,'monday'))

//标记为非broken,指定执行时间

exec dbms_job.remove(:job)；删除

exec dbms_job.remove(186);

commit；

5.限制特定ip登录oracle

在$ORACLE_HOME/network/admin/中配置sqlnet.ora

tcp.validnode_checking=yes-------打开ip检查

tcp.invited_nodes=(IP1,IP2,IP3)

tcp.excluded_nodes=(IP4,IP5,IP6)

其中invited_nodes为仅允许IP1,IP2,IP3访问(设置该参数后，注意添加数据库本机IP)

而excluded_nodes为禁止IP4,IP5,IP6访问

当仅存在excluded_nodes时,仅禁止456

当存在invited_nodes时,则excluded_nodes条目无效,除了invited_nodes列出的IP外,其他IP地址均不能访问

该方法无法进行IP地址段的访问控制

设置后重启监听！

alter

system register;

6.表中有几万行数据删除后，回收表空间

1、--shrink必须开启行迁移功能。

alter

table img_file enable row movement ;

2、--保持HWM

alter

table img_file shrink space compact;

3、--回缩表与HWM

alter

table img_file shrink space;

4、--回缩表与相关索引

alter

table img_file shrink space cascade;

5、--回缩索引

alter

index img_file_idx01 shrink space;

==========================================================

一般如果表里的数据确定没有用了，可以直接truncate(如:truncate table t1;)，如果表里还有数据，可以把数据export出来，再import进去。

还有方法，移动表空间。但是记得重建索引

7.降低高水位方法

7.1 move

执行表重建指令

alter

table table_name move;

ALTER

TABLE ... MOVE后面不跟参数也行，不跟参数表示还是在原来的表空间，Move后记住重建索引。

查询失效索引语句：

select

index_name,table_name,tablespace_name,status

from dba_indexes

where

owner='SCOTT' and status<>'VALID';

重建索引语句：

alter

index INDEX_NAME rebuild tablespace TABLESPACE_NAME;

如果以后还要继续向这个表增加数据，没有必要move，只是释放出来的空间，只能这个表用，其他的表或者segment无法使用该空间。

7.2 shrink space

执行alter table table_name shrink space;

注意，此命令为Oracle 10g新增功能，执行该指令之前必须允许行移动alter table table_name enable row

movement;

7.3 rename to

复制要保留的数据到临时表t，drop原表，然后rename to临时表t为原表

验证：

begin

for i in 1..100000 loop

insert into t2 values(i);

end loop;

commit;

end;

/

analyze table t2 compute statistics;

select

table_name,blocks,empty_blocks

from dba_tables

where table_name='T2';

TABLE_NAMEBLOCKS EMPTY_BLOCKS

------------------------------

---------- ------------

T2152103

SQL>

delete t2;

100000

rows deleted.

SQL>

create table t3 as select * from t2;

SQL>

analyze table t2 compute statistics;

SQL>

select table_name,blocks,empty_blocks

2from

dba_tables

3where table_name='T2';

TABLE_NAMEBLOCKS EMPTY_BLOCKS

------------------------------

---------- ------------

T2152103

SQL>

drop table t2;

SQL>

alter table t3 rename to t2;

SQL> analyze

table t2 compute statistics;

SQL>

select table_name,blocks,empty_blocks

2from

dba_tables

3where table_name='T2';

TABLE_NAMEBLOCKS EMPTY_BLOCKS

------------------------------

---------- ------------

T216

7.4 exp/imp

用EXP导出后，删除原表/表空间，之后用IMP重新导入

7.5 deallocate unused

alter

table table_name deallocate unused;

注：这证明,DEALLOCATE UNUSED为释放HWM上面的未使用空间,但是并不会释放HWM下面的自由空间，也不会移动HWM的位置。

7.6 truncate

尽量使用truncate(如：truncate t1)

8.重新设置redo log日志文件大小

方法：加入新的日志文件，然后删掉旧的日志文件

假设现有三个日志组，每个组内有一个成员，每个成员的大小为20MB，现在想把此三个日志组的成员大小都改为50MB

SQL> col member for a55

SQL> select group#,member from

v$logfile;

GROUP# MEMBER

----------

-------------------------------------------------------

3 /u01/app/oracle/oradata/orcl/redo03.log

2 /u01/app/oracle/oradata/orcl/redo02.log

1 /u01/app/oracle/oradata/orcl/redo01.log

1、创建2个新的日志组

SQL> alter database add logfile group 4

('/u01/app/oracle/oradata/orcl/redo04_1.log') size 20m;

SQL> alter database add logfile group 5

('/u01/app/oracle/oradata/orcl/redo05_1.log') size 20m;

2、切换当前日志到新的日志组

SQL> select

group#,sequence#,status,archived from v$log;

GROUP#SEQUENCE# STATUSARC

---------- ---------- ---------------- ---

182 INACTIVEYES

283 INACTIVEYES

384 CURRENTNO

40 UNUSEDYES

50 UNUSEDYES

alter system switch logfile;

alter system switch logfile;

3、删除旧的日志组

SQL> alter system checkpoint;----脏数据写盘

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

4、操作系统下删除原日志组1、2、3中的文件

SQL> ho rm

/u01/app/oracle/oradata/orcl/redo01.log

SQL> ho rm

/u01/app/oracle/oradata/orcl/redo02.log

SQL> ho rm

/u01/app/oracle/oradata/orcl/redo03.log

5、重建日志组1、2、3

SQL> alter database add logfile group 1

('/u01/app/oracle/oradata/orcl/redo01_1.log') size 50m

SQL> alter database add logfile group 2

('/u01/app/oracle/oradata/orcl/redo02_1.log') size 50m;

SQL> alter database add logfile group 3

('/u01/app/oracle/oradata/orcl/redo03_1.log') size 50m;

6、切换日志组

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

7、删除中间过渡用的日志组4、5

alter database drop logfile group 4;

alter database drop logfile group 5;

8、到操作系统下删除原日志组4、5中的文件

SQL> ! rm

/u01/app/oracle/oradata/orcl/redo04_1.log

SQL> ! rm

/u01/app/oracle/oradata/orcl/redo05_1.log

SQL> select

group#,sequence#,status,archived,bytes/1024/1024 "size(M)" from

v$log;

GROUP#SEQUENCE# STATUSARCsize(M)

---------- ---------- ---------------- ---

----------

187 INACTIVEYES50

288 INACTIVEYES50

389 CURRENTNO50

9.EXP/IMP

9.1 exp--full

#exp \'/ as sysdba\'file=full.dmp log=full_exp.log buffer=65535

full=y

9.2 exp--user

#exp scott/scott file=/home/oracle/scott.dmp log=scott_exp.log

buffer=65535

# exp system/manager file=scott.dmp log=scott_exp.log

buffer=65535 owner=scott

#exp \'/ as sysdba\' buffer=65535 file=/home/oracle/scott.dmp owner=scott

log=scott_exp.log

9.3 exp--tables

#exp scott/scott tables=dept emp file=*.dmp

# exp \'/ as sysdba\' tables=scott.dept,scott.empfile=*.dmp

9.4 exp--rows

#exp scott/scott tables=emp query=\'where

deptno=10\' file=*.dmp

#exp scott/scott tables=emp query=\"

where name=\'king\' \" file=*.dmp

9.5 imp--full

#imp \'/ as sysdba\' file=*.dmp full=y

[ignore=y]

9.6 imp--user

#imp scott/scott file=*.dmp

#imp \'/ as sysdba\' file=*.dmp

fromuser=scott touser=scott

#imp \'/ as sysdba\'buffer=65536file=scott.dmp

log=scott_imp.log fromuser=scott touser=scott

9.7 imp--tables

#imp scott/scott file=*.dmp tables=dept

emp[ignore=y]

# imp \'/ as sysdba\' tables=dept emp

file=*.dmp fromuser=scott touser=scott [ignore=y]

10.DataPumpexpdp/impdp

准备工作

SQL>createdirectory dpdata2 as '/home/oracle/dump'

SQL> col owner for a5

SQL> col directory_name for a20

SQL> col directory_path for a50

SQL> select * from dba_directories;

# mkdir /home/oracle/dump

SQL>grant read,write on directory dpdata2

to scott;

10.1 expdp--full

#expdp \'/ as sysdba\' directory=dpdata2

dumpfile=fulldp.dmp full=y log=fulldp.log

10.2 impdp--full

#impdp \'/ as sysdba\' directory=dir

dumpfile=full.dmp full=y [table_exists_action=append/skip/replace/truncate]

10.3 expdp--user

SQL>create or replace directory dir as

'/home/oracle/expdp'

SQL>grant read,write on directory dir to

scott;

#mkdir /home/oracle/expdp

#expdp scott/scott directory=dir

dumpfile=scott.dmp

#expdp \'/ as sysdba\' directory=dir

dumpfile=scott.dmp schemas=scott

10.4 impdp--user

#impdp scott/scott directory=dir

dumpfile=scott.dmp

#impdp \'/ as sysdba\' directory=dir

dumpfile=scott.dmp [remap_schema=scott:hr]

10.5 expdp--tables

SQL>create or replace directory dir as

'/home/oracle/expdp'

SQL>grant read,write on directory dir to

scott;

#mkdir /home/oracle/expdp

#expdp scott/scott directory=dir

dumpfile=table.dmp tables=emp,dept

10.6 impdp--tables

SQL>impdp scott/scott directory=dir

dumpfile=table.dmp tables=emp,dept

[table_exists_action=append/skip/replace/truncate]

10.7 ORA-31626: job does not exist处理

(1)问题

[oracle@zyx dump]$ expdp \'/ as sysdba\'

directory=dir dumpfile=scott.dmp schemas=scott

Export: Release 11.2.0.4.0 - Production on

Thu Apr 28 19:39:33 2016

Copyright (c) 1982, 2011, Oracle and/or its

affiliates.All rights reserved.

Connected to: Oracle Database 11g

Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining

and Real Application Testing options

ORA-31626: job does not exist

ORA-31637: cannot create job

SYS_EXPORT_SCHEMA_01 for user SYS

ORA-06512: at

"SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT_INT",

line 798

ORA-39244: Event to disable dropping null

bit image header during relational select

ORA-06512: at

"SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.KUPC$QUE_INT",

line 1840

ORA-04063: package body

"SYS.DBMS_IJOB" has errors

ORA-06508: PL/SQL: could not find program unit

being called: "SYS.DBMS_IJOB"

(2)解决方法

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

(3)正常

[oracle@zyx dump]$ expdp \'/ as sysdba\'

directory=dir dumpfile=scott.dmp schemas=scott

Export: Release 11.2.0.4.0 - Production on

Thu Apr 28 19:55:51 2016

Copyright (c) 1982, 2011, Oracle and/or its

affiliates.All rights reserved.

Connected to: Oracle Database 11g

Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining

and Real Application Testing options

Starting

"SYS"."SYS_EXPORT_SCHEMA_01":"/******** AS SYSDBA" directory=dir

dumpfile=scott.dmp schemas=scott

Estimate in progress using BLOCKS method...

Processing object type

SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192

KB

Processing object type SCHEMA_EXPORT/USER

Processing object type

SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type

SCHEMA_EXPORT/ROLE_GRANT

Processing object type

SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type

SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type

SCHEMA_EXPORT/TABLE/TABLE

Processing object type

SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type

SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type

SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type

SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type

SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type

SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."DEPT"5.953 KB5 rows

. . exported

"SCOTT"."EMP"8.562 KB14 rows

. . exported

"SCOTT"."SALGRADE"5.859 KB5 rows

. . exported

"SCOTT"."BONUS"0 KB0 rows

Master table

"SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01

is:

/home/oracle/expdp/scott.dmp

Job

"SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu

Apr 28 19:56:19 2016 elapsed 0 00:00:13

(4)总结

这种错误通常都是由于Oracle软件升级之后和库不一致产生的，需要重新

执行catalog.sql and catproc.sql来配置后台数据字典

sql>@?/rdbms/admin/catalog.sql

sql>@?/rdbms/admin/catproc.sql

【执行数据字典的编写应该检测是否有失效的对象，如有失效应该执行以下】

SQL> connect / as sysdba

SQL> @?/rdbms/admin/catmetx.sql

SQL> @?/rdbms/admin/utlrp.sql