Three recent summary of Oracle Management

1. Statement to modify the Oracle user password

Alter user edw1010 identified by edw1010;

In fact, this is very simple, but because it is not commonly used, it may seem a little busy.

2. View deleted code scripts

The reference statement is as follows:

-- Can be used to view objects (PROCEDURE, PACKAGE, package body, type body, TRIGGER, FUNCTION, and TYPE) of the flashback query)
-- In fact, it is the use of the flashback query of ORACLE in the data dictionary table.
-- Log On As a dba using sys
Select *
From dba_source as of timestamp to_timestamp ('2017-01-23 06:56:00 ', 'yyyy-MM-DD HH24: MI: ss ')
Where owner = 'edw'
And type = 'processed ';
Select *
From dba_source as of timestamp to_timestamp ('2017-01-23 07:46:00 ', 'yyyy-MM-DD HH24: MI: ss ')
Where owner = 'edw'
And type = 'function ';

Flashback view of Table Structure

-- The following statement can be used to view the table creation statements in the table structure, but it does not work for Flashback queries.
SELECT DBMS_METADATA.GET_DDL ('table', 'test', 'edw ')
From dual as of timestamp to_timestamp ('2017-01-23 08:13:30 ', 'yyyy-MM-DD HH24: MI: ss ');
-- However, you can view the table structure status using the following statement:
Select *
From dba_tab_columns as of timestamp to_timestamp ('2017-01-23 08:27:00 ', 'yyyy-MM-DD HH24: MI: ss ')
Where owner = 'edw' and table_name = 'gbicc ';

3. Generate a script to delete the object statement under the user

-- Delete objects under a user
Set heading off;
Set feedback off;
Spool D: \ drop_user_obj. SQL;
Prompt -- start to generate a script to delete the following objects under the current user (if not all objects listed in this script, you can add them yourself)
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' | view_name | ';' from user_views;
Prompt -- Drop procedure
Select 'drop procedure '| object_name | ';'
From user_procedures
Where object_type = 'processure ';
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;