This query returns the archivelog sequence containing a scn. This could be usefull in case you want to manually recover a standby database until a scn or just want to know which arvhivelog you should copy to recover the standby.
select sequence# from v$archived_log where &scn between FIRST_CHANGE# and NEXT_CHANGE#-1;
DBMS_METADATA is a useful package to generate the DDL of an existing user in the database, it can also be used to generate the DDL of any object and its dependencies.
set feedback off pages 0 long 90000 serveroutput on
accept USERNAME prompt "Enter username :"
--This line add a semicolon at the end of each statement
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
-- This will generate the DDL for the user and add his objects,system and role grants
SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee)as script from DBA_SYS_PRIVS where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee)as script from DBA_ROLE_PRIVS where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee)as script from DBA_TAB_PRIVS where grantee='&username' and rownum=1;
If you want to generate the DDL for an object you can use the following command :
SET LONG 2000000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') FROM DUAL;
If you want the grants for the object as well you can use the GET_DEPENDENT_DDL procedure :
SET LONG 2000000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EMP','SCOTT') FROM DUAL;
First argument is the object_type, second one the object_name and the last one represents the schema.
Now, suppose you want to get the DDL for all tables in the schema SCOTT, you can use this code :
DROP TABLE my_metadata;
CREATE TABLE my_metadata (md CLOB);
CREATE OR REPLACE PROCEDURE get_schema_md IS
-- Define local variables.
h NUMBER; -- handle returned by OPEN
th NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB; -- metadata is returned in a CLOB
BEGIN
-- Specify the object type.
h := DBMS_METADATA.OPEN('TABLE');
-- Use filters to specify the schema.
DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');
-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
--add semicolon at the end of the statement
DBMS_METADATA.SET_TRANSFORM_PARAM(th,'SQLTERMINATOR',true);
-- Fetch the objects.
LOOP
doc := DBMS_METADATA.FETCH_CLOB(h);
-- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
EXIT WHEN doc IS NULL;
-- Store the metadata in the table.
INSERT INTO my_metadata(md) VALUES (doc);
COMMIT;
END LOOP;
-- Release resources.
DBMS_METADATA.CLOSE(h);
END;
/
EXEC get_schema_md;
SELECT * FROM my_metadata;
With this method you can also generate the DDL for a whole schema, you just have to change the object_type from ‘TABLE’ to ‘SCHEMA_EXPORT’ in the OPEN procedure.
This procedure is from the oracle documentation, you can consult it at the following address: DBMS_METADATA documentation
This script list all objects stored in the SYSAUX tablespace ordered by space used.
set lines 150 pages 100
col Object_Name FOR a30
col "Space Used (MB)" FORMAT 9999.99
col Owner for a30
SELECT occupant_name "OBJECT_NAME",
space_usage_kbytes/1024 "Space Used (MB)",
schema_name "Owner"
FROM v$sysaux_occupants
ORDER BY 2 desc;