GENERAL USAGE
set long 300000 pages 0 lines 140
SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','OWNER') FROM DUAL;
The 'OWNER' argument is sometimes optional if you are connected as the same user who owns the object you are trying to retreive the DDL for. Pasted below are the statements to generate the DDL for most of the object types.
select dbms_metadata.get_ddl('CLUSTER','C_MLOG#','SYS') from dual;
select dbms_metadata.get_ddl('CONTEXT','LT_CTX') from dual;
select dbms_metadata.get_ddl('DB_LINK','PROD.WORLD','ADAM') from dual;
select dbms_metadata.get_ddl('DB_LINK','TEST.WORLD','PUBLIC') from dual;
select dbms_metadata.get_ddl('FUNCTION','TO_DATE_FUNC','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','REPCAT$_REPCOLUMN_PK','SYSTEM') from dual;
select dbms_metadata.get_ddl('JAVA_SOURCE','java_util','ADAM') from dual
select dbms_metadata.get_ddl('JAVA_SOURCE','/6c363944_Dumper','SYS') from dual
select dbms_metadata.get_ddl('LIBRARY','UTL_SMT_LIB','SYS') from dual;
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','BS_TAB_MV','SYS') from dual;
select dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG','MLOG$_BS_TABLE','SYS') from dual;
select dbms_metadata.get_ddl('OPERATOR','OLAP_EXPRESSION') from dual;
select dbms_metadata.get_ddl('OPERATOR','OLAP_EXPRESSION','SYS') from dual;
select dbms_metadata.get_ddl('PACKAGE','XMLPARSER') from dual;
select dbms_metadata.get_ddl('PACKAGE','XMLPARSER','SYS') from dual;
select dbms_metadata.get_ddl('PACKAGE_BODY','STATSPACK','PERFSTAT') from dual;
select dbms_metadata.get_ddl('PROCEDURE','ORA$_SYS_REP_AUTH','SYSTEM') from dual;
select dbms_metadata.get_ddl('SEQUENCE','STATS$SNAPSHOT_ID','PERFSTAT') from dual;
select dbms_metadata.get_ddl('SYNONYM','/2fddc42_paintARGB_PRE_ONTO_S5','PUBLIC') from dual;
select dbms_metadata.get_ddl('TABLE','SQLEXPERT_PLAN1','SYSTEM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
select dbms_metadata.get_ddl('TRIGGER','DEF$_PROPAGATOR_TRIG','SYSTEM') from dual;
select dbms_metadata.get_ddl('TYPE','XMLSEQ_IMP_T','SYS') from dual;
select dbms_metadata.get_ddl('TYPE_BODY','ORACLE_LOADER','SYS') from dual;
select dbms_metadata.get_ddl('VIEW','DBA_PROPAGATION','SYS') from dual;
select dbms_metadata.get_ddl('VIEW','DBA_PROPAGATION') from dual;
select dbms_metadata.get_ddl('PACKAGE','DBMS_METADATA','SYS') from dual;
Extract DDL for Users.
It is a DBA task to create users or migrate users from one
database environment to another.
Let’s use DBMS_METADATA.GET_DDL package to extract CREATE
USER DDL.
SQL> select dbms_metadata.get_ddl
2 ('USER',username)
3 as col1
4 from dba_users
5 where username
6 = 'SCOTT';
COL1
-----------------------------------
CREATE USER "SCOTT" IDENTIFIED BY
VALUES
'S:6BA47D5BAA9A46E9A3E9073AA77F486B
01E2AF0AA28D98515B969693B8AE;191F2D
E583F11B20'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
Let’s get the system grants and object grants using function
GET_GRANTED_DLL for this user SCOTT.
SQL> select dbms_metadata.get_granted_ddl
2 ('SYSTEM_GRANT',username)
3 as col1
4 from dba_users
5 where username
6 = 'SCOTT';
COL1
-----------------------------------
GRANT SELECT ANY TABLE TO "SCOTT";
GRANT UNLIMITED TABLESPACE TO
"SCOTT";
GRANT CREATE SESSION TO "SCOTT";
Changing the value from SYSTEM_GRANT to OBJECT_GRANT, and
running the same SQL, gives the following object grants given to SCOTT.
SQL> select dbms_metadata.get_granted_ddl
2 ('OBJECT_GRANT',username)
3 as col1
4 from dba_users
5 where username
6 = 'SCOTT';
COL1
-----------------------------------
GRANT ALTER, DELETE, INDEX, INSERT,
SELECT, UPDATE, REFERENCES, ON
COMMIT REFRESH, QUERY REWRITE,
DEBUG, FLASHBACK ON "BHAVESH"."T11"
TO "SCOTT";
GRANT DELETE, INSERT, SELECT,
UPDATE ON "BHAVESH"."T2" TO
"SCOTT";
GRANT DELETE, INSERT, SELECT,
UPDATE ON "BHAVESH"."T3" TO
"SCOTT";
GRANT DELETE, INSERT, SELECT,
UPDATE ON "BHAVESH"."T4" TO
"SCOTT";
The following SQL gives the roles granted to username SCOTT.
SQL> select dbms_metadata.get_granted_ddl
2 ('ROLE_GRANT',username)
3 as col1
4 from dba_users
5 where username
6 = 'SCOTT';
COL1
-----------------------------------
GRANT "CONNECT" TO "SCOTT";
GRANT "RESOURCE" TO "SCOTT";
GRANT "DEV_ROLE" TO "SCOTT";++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Reset Sequence.
When you export Oracle data and then import to another environment, sometimes the sequence number gives primary key errors. In this situation, we need to reset Oracle sequences.This example, we bump up all the sequences by 100.
I have 10 sequences in my schema and I want to increase all of them by 100.
SQL> select sequence_name, last_number from user_sequences;
SEQUENCE_NAME LAST_NUMBER
------------------------------ -----------
S1 1
S10 1
S2 1
S3 1
S4 1
S5 1
S6 1
S7 1
S8 1
S9 1
10 rows selected.
Currently, their last_number is one because I just created them. In real world, your sequnces' last_number will be different!
Here is a little PL/SQL block that will bump up the sequence numbers by 100.
declare
my_seq_number number;
my_sql varchar2(200);
-- increase the sequence by 100..change it if you want
my_increase_seq_by_number NUMBER := 100;
begin
for my_sequneces in ( select sequence_name from user_sequences
)
loop
my_sql := 'select ' || my_sequneces.sequence_name || '.nextval from dual';
for i in 1..my_increase_seq_by_number
loop
execute immediate my_sql into my_seq_number;
end loop;
end loop;
end;
/
After executing the above PL/SQL block, here is the last_number for all my sequences.
SQL> select sequence_name, last_number from user_sequences;
SEQUENCE_NAME LAST_NUMBER
------------------------------ -----------
S1 101
S10 101
S2 101
S3 101
S4 101
S5 101
S6 101
S7 101
S8 101
S9 101
10 rows selected.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Have you ever tried to get the DDL for Materialized Views in Oracle using dbms_metadta.get_ddl and got the error ORA-06512?
Here is a little demo the explains the error and then provides a work around.
SQL> create materialized view mv
2 as
3 select d.dname, sum(sal) sal_per_dept, avg(sal) avg_per_dept, count(*) count_per_dept
4 from emp e join dept d on (e.deptno= d.deptno)
5 group by d.dname;
Materialized view created.
SQL> select dbms_metadata.get_ddl(object_type, object_name) as mv_ddl
2 from user_objects where object_type = 'MATERIALIZED VIEW';
ERROR:
ORA-31600: invalid input value MATERIALIZED VIEW for parameter OBJECT_TYPE in
function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 3773
ORA-06512: at "SYS.DBMS_METADATA", line 3828
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1
no rows selected
The error is caused by the space in the object name MATERIALIZED VIEW. Replace the space with "_" and the object_type will become MATERIALIZED_VIEW and the function will work!
Similarly, DB LINK should be DB_LINK
PACKAGE SPECIFICATION should be PACKAGE_SPEC
and PACKAGE BODY should be PACKAGE_BODY
The object_type parameter will not accept space in the object_type name.
SQL> set long 50000
SQL> col mv_ddl format a100 word_wrap
SQL> set pagesize 100
SQL>
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
PL/SQL procedure successfully completed.
SQL> select dbms_metadata.get_ddl(replace(object_type,' ','_'), object_name) as mv_ddl
2 from user_objects where object_type = 'MATERIALIZED VIEW';
MV_DDL
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW "SCOTT"."MV" ("DNAME", "SAL_PER_DEPT",
"AVG_PER_DEPT", "COUNT_PER_DEPT")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
LOGGING
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS select d.dname, sum(sal) sal_per_dept, avg(sal) avg_per_dept, count(*)
count_per_dept
from emp e join dept d on (e.deptno= d.deptno)
group by d.dname;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Extract User grants
On a database project with the multiple team members doing the development work, it becomes harder to keep track of all the grants given. Before promoting your changes to the higher environments like UAT, TEST or production, you can use the following script to extract the grants on all the database objects in a schema.
SQL> select 'grant ' || privilege || ' on ' || table_name ||
2 ' to ' || grantee ||
3 case when grantable = 'YES' then ' with grant option;'
4 else ';'
5 end as my_grant
6 from user_tab_privs_made
7 order by table_name;
MY_GRANT
-----------------------------------------------------------------------------
grant ALTER on BONUS to SYSTEM;
grant SELECT on DEPT to SCOTT;
grant INSERT on EMP to SCOTT;
grant SELECT on EMP to SCOTT;
grant UPDATE on EMP to SCOTT;
grant REFERENCES on EMP to SCOTT;
grant ON COMMIT REFRESH on EMP to SCOTT;
grant QUERY REWRITE on EMP to SCOTT;
grant DEBUG on EMP to SCOTT;
grant FLASHBACK on EMP to SCOTT;
grant INDEX on EMP to SCOTT;
grant ALTER on EMP to SCOTT;
grant DELETE on EMP to SCOTT;
grant SELECT on EMP to SYSTEM with grant option;
grant EXECUTE on P to HR;
15 rows selected.
Please notice the "with grant option" in the above query result. Also, notice that the procedure grants are also included there. In fact, all the grants will be included here.
The dbms_metadata is not a good idea to use for the grants. The above approach always works!
If you want to get the object grants for more than one schema, use the following version of the query.
SQL> select 'grant ' || privilege || ' on ' || owner || '.' || table_name ||
2 ' to ' || grantee ||
3 case when grantable = 'YES' then ' with grant option;'
4 else ';'
5 end as my_grant
6 from dba_tab_privs
7 where owner in ('SCOTT','HR')
8 order by owner,table_name;
MY_GRANT
---------------------------------------------------------------------------------------
grant SELECT on SCOTT.BONUS to HR;
grant UPDATE on SCOTT.BONUS to HR;
grant INSERT on SCOTT.BONUS to HR;
grant ON COMMIT REFRESH on SCOTT.DEPT to PUBLIC;
grant REFERENCES on SCOTT.DEPT to PUBLIC;
grant UPDATE on SCOTT.DEPT to PUBLIC;
grant SELECT on SCOTT.DEPT to PUBLIC;
grant INSERT on SCOTT.DEPT to PUBLIC;
grant INDEX on SCOTT.DEPT to PUBLIC;
grant DELETE on SCOTT.DEPT to PUBLIC;
grant ALTER on SCOTT.DEPT to PUBLIC;
grant QUERY REWRITE on SCOTT.DEPT to PUBLIC;
grant DEBUG on SCOTT.DEPT to PUBLIC;
grant FLASHBACK on SCOTT.DEPT to PUBLIC;
grant UPDATE on SCOTT.DEPT to HR;
grant INSERT on SCOTT.DEPT to HR;
grant SELECT on SCOTT.DEPT to HR;
grant SELECT on SCOTT.LINEITEM to HR;
grant UPDATE on SCOTT.LINEITEM to HR;
grant INSERT on SCOTT.LINEITEM to HR;
grant SELECT on SCOTT.ORDERS to HR;
grant UPDATE on SCOTT.ORDERS to HR;
grant INSERT on SCOTT.ORDERS to HR;
grant UPDATE on SCOTT.PROJ to HR;
grant SELECT on SCOTT.PROJ to HR;
grant INSERT on SCOTT.PROJ to HR;
grant SELECT on SCOTT.SALGRADE to HR;
grant UPDATE on SCOTT.SALGRADE to HR;
grant INSERT on SCOTT.SALGRADE to HR;
29 rows selected.
Please note that we are using "dba_tab_privs" view now and also prefixing the object name with the owner.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Recompile Invalid Objects
When we run DDLs against Oracle tables, views and other database objects, some of the dependent objects become invalid.Here is a script to recompile invalid objects.
The command to recompile is
alter object_type object_name compile;
The only exception is for the package body. The command to recompile the package body is
alter package package_name compile body;
To take that exception into account, here is the script that will general the "alter" for each invalid database object in the current schema.
select 'alter ' || case when object_type = 'PACKAGE BODY'
then 'PACKAGE'
else object_type
end
|| ' ' || object_name || ' compile ' ||
case when object_type = 'PACKAGE BODY'
then 'body'
else null
end || ';' as mysql
from USER_OBJECTS
where status = 'INVALID';
Hope, it will save you time.
Thanks
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Getting the DDL for tables
Oracle scripting can be used to extract DDL for database objects. The most common use is for getting the DDL for tables.
Here is a simple demo.
SQL> col my_ddl format a100 word_wrap
SQL> set long 50000
SQL> set pagesize 0
SQL>
SQL> select dbms_metadata.get_ddl('TABLE',table_name,user) AS my_ddl
2 from all_tables where owner = 'SCOTT'
3 and table_name = 'DEPT';
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" CHAR(14),
"LOC" CHAR(13),
CONSTRAINT "DEPT_DEPTNO_PK" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
The above output needs some revision.
Let's get rid of the storage clause.
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
PL/SQL procedure successfully completed.
SQL> /
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" CHAR(14),
"LOC" CHAR(13),
CONSTRAINT "DEPT_DEPTNO_PK" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"
We also want to get rid of segment attributes, put a semicolon at the end as sql terminator and make the output pretty!
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
PL/SQL procedure successfully completed.
SQL> /
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" CHAR(14),
"LOC" CHAR(13),
CONSTRAINT "DEPT_DEPTNO_PK" PRIMARY KEY ("DEPTNO") ENABLE
) ;
Now, the output looks little better!
If we want to get the DDL for all the tables in the schema, let's remove the table_name filter.
SQL> select dbms_metadata.get_ddl('TABLE',table_name,user) AS my_ddl
2 from all_tables where owner = 'SCOTT';
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" CHAR(10),
"JOB" CHAR(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
"PROJNO" NUMBER,
"LOADSEQ" NUMBER,
CONSTRAINT "EMP_DEPTNO_FK" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) ;
CREATE TABLE "SCOTT"."SALGRADE"
( "GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) ;
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" CHAR(14),
"LOC" CHAR(13),
CONSTRAINT "DEPT_DEPTNO_PK" PRIMARY KEY ("DEPTNO") ENABLE
) ;
Looks good? Well, the only catch is, the table creation script may not be in the right order. In the above case, we want the dept table to be created first and then emp table. Otherwise, we are going to get error.
We will try to figure out how to achieve that in the next post!
Meanwhile, if you have a better idea, please weigh in!
==========================================================
No comments:
Post a Comment