Oracle管理方面的3个近期汇总整理

1、语句修改oracle用户密码alter user edw1010 identified by edw1010;其实这很简单，但因为不经常用，所以用到的时候会显得有些

1、语句修改Oracle用户密码

alter user edw1010 identified by edw1010;

其实这很简单，但因为不经常用，，所以用到的时候会显得有些手忙脚乱的。

2、查看已经删除的代码脚本

参考语句如下：

–可用于查看闪回查询的对象（PROCEDURE、PACKAGE、PACKAGE BODY、TYPE BODY、TRIGGER、FUNCTION、TYPE）
–实际上就是在数据字典表上ORACLE的闪回查询的利用
–以sys作为DBA登录
select *
from dba_source as of timestamp to_timestamp(‘2011-01-23 06:56:00’, ‘YYYY-MM-DD HH24:MI:SS’)
where owner = ‘EDW’
and type = ‘PROCEDURE’;
select *
from dba_source as of timestamp to_timestamp(‘2011-01-23 07:46:00’, ‘YYYY-MM-DD HH24:MI:SS’)
where owner = ‘EDW’
and type = ‘FUNCTION’;

表结构的闪回查看

–下面的语句可以查看表结构建表语句，但对闪回查询不起作用
SELECT DBMS_METADATA.GET_DDL(‘TABLE’, ‘TEST’, ‘EDW’)
FROM DUAL as of timestamp to_timestamp(‘2011-01-23 08:13:30’, ‘YYYY-MM-DD HH24:MI:SS’);
–但可以如下语句查看表结构状态
select *
from dba_tab_columns as of timestamp to_timestamp(‘2011-01-23 08:27:00’, ‘YYYY-MM-DD HH24:MI:SS’)
where owner = ‘EDW’ and table_name=’GBICC’;

3、生成删除用户下对象语句的脚本

–删除某个用户下的对象
set heading off;
set feedback off;
spool D:\drop_user_obj.sql;
prompt –开始生成删除当前用户下如下对象的脚本（若本脚本列示的不全，可自行添加）
prompt –Drop constraint
select ‘alter table ‘ || table_name || ‘ drop constraint ‘ ||
constraint_name || ‘ ;’
from user_constraints
where constraint_type = ‘R’;
prompt –Drop tables
select ‘drop table ‘ || table_name || ‘;’ from user_tables;
prompt –Drop view
select ‘drop view ‘ ||

本文来源gao!%daima.com搞$代*!码9网(

view_name || ‘;’ from user_views;
prompt –Drop procedure
select ‘drop procedure ‘ || object_name || ‘;’
from user_procedures
where object_type = ‘PROCEDURE’;
prompt –Drop function
select ‘drop function ‘ || object_name || ‘;’
from user_procedures
where object_type = ‘FUNCTION’;
prompt –Drop sequence
select ‘drop sequence ‘ || sequence_name || ‘;’ from user_sequences;
spool off;

喜欢 (0)赏

[搞代码]

分享 (0)