Friday, July 31, 2009

Identifying default passwords in Oracle databases.

If you want to scan an Oracle database for accounts with default passwords Oracle provide patch 4926128 which includes script dfltpass.sql, documented in Metalink note 227010.1.

From 11g Oracle the database includes table SYS.DEFAULT_PWD$ which includes default password hashes, and you can get a list of users with default passwords by just selecting from DBA_USERS_WITH_DEFPWD.

SQL> select * from DBA_USERS_WITH_DEFPWD;

USERNAME
------------------------------
XDB
MDSYS
TSMSYS
EXFSYS
LBACSYS
SI_INFORMTN_SCHEMA
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
DIP
ORACLE_OCM
ORDSYS
SCOTT
WMSYS
CTXSYS
MDDATA
ORDPLUGINS
DBSNMP
DMSYS
OUTLN

Tuesday, July 28, 2009

Script to list connections to all databases on a server

#run.sh
for ORACLE_SID in $(cat /etc/oratab |grep -v ^#|grep -v ^agent | grep :Y|cut -f1 -d:)
do
ORAENV_ASK=NO
. oraenv
echo "$ORACLE_SID"
sqlplus / as sysdba @run.sql $ORACLE_SID
done

--run.sql
spool &1
set pagesize 0 linesize 200
select osuser||' '||machine||' '||program||' '||count(*) from v$session
where username <> 'SYS'
and program not like '%J00%'
group by osuser, program, machine
order by machine, osuser, program;
spool off
exit

Best Practices for Securing Oracle E-Business Suite Release 12

For EBS 12 security hardening the first place to start is Metalink Note 403537.1 - Best Practices for Securing Oracle E-Business Suite Release 12
https://metalink2.oracle.com/metalink/plsql/docs/EBS_SEC_1_0_0.pdf

Patch 4926128 contains a script to scan for default passwords which should be changed by FNDCPASS

http://download.oracle.com/docs/cd/B53825_02/current/acrobat/121sacg.pdf

Also need to set site level profile option Sign-On: Audit Level to Form to track logins, responsibility access, and form access.

http://download.oracle.com/docs/cd/B53825_02/current/acrobat/121sasg.pdf

Test flashback

# stop applications (forms,concurrent managers etc)

# check parameters
sqlplus / as sysdba
show parameter recovery
show parameter flash

--ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G;
--ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+ASM_ORADATA226';
--alter system set db_flashback_retention_target = 1440;

# Check archivelog, flashback

select log_mode, flashback_on from v$database;

# Enable flashback

sqlplus / as sysdba
shutdown immediate;
startup mount exclusive;
alter database flashback on;
select flashback_on from v$database;
shutdown immediate;
startup

# Test flashback point
select TO_CHAR(CURRENT_SCN) FROM V$DATABASE; - record this
create table flashback_test (id integer primary key);
create restore point before_load guarantee flashback database;
select TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
insert into flashback_test values (1);
commit;
select * from flashback_test;
select TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

# Test flashback to restore point
shutdown immediate;
startup mount exclusive;
flashback database to restore point before_load;
alter database open resetlogs;
select * from flashback_test;
drop restore point before_load;
shutdown immediate;

# Test flashback to scn
startup mount exclusive
flashback database to scn ;
alter database open resetlogs;
select * from flashback_test;

Saturday, July 18, 2009

Create a Dataguard Broker configuration

SID_LIST_LISTENER_PD01CFD_PROD-ORANODE-10 =
(SID_LIST =
(SID_DESC =
(SID_NAME = pd01cfd1)
(GLOBAL_DBNAME=pd01cfd_DGMGRL)
(ORACLE_HOME = /opt/oracle/product/10.2.0/db_2)
)
)


SYS AS SYSDBA@pd01cfd1> show parameter dg

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string +ASM_ORADATA11/pd01cfd/dr1pd01
cfd.dat
dg_broker_config_file2 string +ASM_ORADATA12/pd01cfd/dr2pd01
cfd.dat
dg_broker_start boolean TRUE


create configuration 'PROD_BKO' as PRIMARY DATABASE IS 'pd01bko' CONNECT IDENTIFIER IS pd01bko;
ADD DATABASE 'pl01bko' AS CONNECT IDENTIFIER IS pl01bko MAINTAINED AS PHYSICAL;
edit database pd01bko set property logxptmode='ASYNC';
edit database pl01bko set property logxptmode='ASYNC';
edit database pd01bko set property PreferredApplyInstance='pd01bko1';
edit database pl01bko set property PreferredApplyInstance='pl01bko1';
edit database pd01bko set property MaxFailure=15;
edit database pl01bko set property MaxFailure=15;
edit database pd01bko set property ReopenSecs=60;
edit database pl01bko set property ReopenSecs=60;
edit database pd01bko set property LogArchiveMaxProcesses=10;
edit database pl01bko set property LogArchiveMaxProcesses=10;
enable configuration;

ORA-16714 Dataguard broker sensitive to "alter database"

If you are using Dataguard Broker you need to be very careful about parameter changes in the spfile, or "alter system set" commands. Dataguard broker will go into a warning state and will refuse to switchover if it detects any inconsistency between the spfile and the running database. Make sure dgmgrl "show configuration" returns "SUCCESS" to confirm all is good.

DGMGRL> show configuration

Configuration
Name: PROD_CFD
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
pd01cfd - Physical standby database
pl01cfd - Primary database

Current status for "PROD_CFD":
SUCCESS

If it returns WARNING then you need to check the Dataguard Broker logfile in bdump/drc$ORACLE_SID.log directory - e.g /oralogs/pd01cfd1/bdump/drcpd01cfd1.log

For example Oracle Support asked me to set LOG_ARCHIVE_TRACE=891 to diagnose switchover issues. I set this using an "alter system" command, which caused Dataguard Broker to go into WARNING and also refuse to switchover.

# From the log :-

DG 2009-07-18-08:43:16 1000 2 692526578 DMON: CTL_GET_STATUS operation completed
DG 2009-07-18-08:45:15 0 2 0 RSM Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='0', DATABASE='891'
DG 2009-07-18-08:45:15 0 2 0 RSM0: HEALTH CHECK WARNING: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting

I restarted the database to fix this, but it would also have been possible to run "alter system set LOG_ARCHIVE_TRACE=0

Thursday, July 16, 2009

dgmgrl switchover

# alter system set dg_broker_start=TRUE;

dgmgrl
connect sys
show configuration

# Switchover
switchover to pl01cfd

Manual Dataguard Switchover

# primary
# first shutdown the second RAC node
SELECT SWITCHOVER_STATUS FROM V$DATABASE; -- result should be either TO STANDBY if nothing is running or SESSIONS ACTIVE if there are connected sessions
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

# standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SELECT SWITCHOVER_STATUS FROM V$DATABASE; -- result should be TO PRIMARY
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN;
STARTUP MOUNT FORCE;
ALTER DATABASE OPEN;

# old primary
STARTUP MOUNT FORCE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

DBA_RECYCLEBIN

alter system set recyclebin='ON';

show parameter recycle

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string ON

drop table test;
select owner, object_name, original_name from dba_recyclebin;
flashback table test to before drop;


purge dba_recyclebin;

Wednesday, July 15, 2009

Cleanup invalid objects

#extract DDL for invalid objects
select 'select dbms_metadata.get_ddl('''||replace(OBJECT_TYPE,'PACKAGE BODY','PACKAGE')||''','''||OBJECT_NAME||''','''||OWNER||''') stmt from dual;'
from dba_objects where status = 'INVALID';

# try to compile invalid objects
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects where status = 'INVALID'
order by owner, object_name;

# compile public synonyms
select 'alter public synonym '||object_name||' compile;'
from dba_objects where status = 'INVALID' and object_type = 'SYNONYM' and owner = 'PUBLIC'
order by owner, object_name;

# list all compile errors
select * from dba_errors;

# drop invalid objects
select 'drop '||object_type||' '||owner||'.'||object_name||';' from dba_objects where status = 'INVALID' order by owner, object_name;

# identify packages with no body
select p.owner, p.object_name from
dba_objects p
where p.object_type = 'PACKAGE'
and not exists (select 1 from dba_objects ph where p.owner = ph.owner and p.object_name = ph.object_name and ph.object_type = 'PACKAGE BODY');

Generate DDL for invalid objects

select 'select dbms_metadata.get_ddl('''||replace(OBJECT_TYPE,'PACKAGE BODY','PACKAGE')||''','''||OBJECT_NAME||''','''||OWNER||''') stmt from dual;'
from dba_objects where status = 'INVALID';

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects where status = 'INVALID'
order by owner, object_name;

select 'alter public synonym '||object_name||' compile;'
from dba_objects where status = 'INVALID' and object_type = 'SYNONYM' and owner = 'PUBLIC'
order by owner, object_name;

select * from dba_errors;

select 'drop '||object_type||' '||owner||'.'||object_name||';' from dba_objects where status = 'INVALID' order by owner, object_name;

Monday, July 13, 2009

Oracle 11gR2 new features

Difficult to find much detail on 11gR2, but I found the following link to Oracle 11gR2 new features for Administrators course. It gives some hints about what is coming, including ACFS, Columnar compression etc.

http://education.oracle.com/pls/web_prod-plq-dad/web_prod.view_pdf?c_id=D50081GC20&c_org_id=38&c_lang=US

Also found :-

http://www.eygle.com/archives/2009/03/oracle_11gr2_acfs_filesystem.html

http://translate.google.co.uk/translate?u=http%3A%2F%2Fwww.dbform.com%2Fhtml%2F2009%2F632.html&sl=zh-CN&tl=en&hl=en&ie=UTF-8

Friday, July 10, 2009

ORA-26723: user "STRMADMIN" requires the role "DBA"

When running DBMS_STREAMS_ADM.ADD_TABLE_RULES on a database with Database Vault enabled you get :-

ERROR at line 1:
ORA-26723: user "STRMADMIN" requires the role "DBA"
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 372
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 312
ORA-06512: at line 2

STRMADMIN has the DBA role, so the error message is wrong.

Cause :- Database Vault revokes become user, which causes Datapump and Streams to fail.

Solution :- "grant become user to strmadmin"

Thursday, July 9, 2009

Change AWR snapshot frequency

select * from dba_hist_wr_control;

execute dbms_workload_repository.modify_snapshot_settings
( interval => 10,
retention => 14400);

select * from dba_hist_wr_control;

Wednesday, July 8, 2009

Script to generate Oracle password hashes

Function to use username||password as a seed to generate Oracle password hashes.

(based on examples on petefinnigan.com)

create or replace function password_hash (username in varchar2, password in varchar2) return varchar2 is

raw_key_v raw(128):= hextoraw('0123456789ABCDEF');
ascii_string_v varchar2(124):='';
uni_string_v raw(128) := '';
hex_string_v varchar2(2048) := '';
raw_string_v raw(2048) :='';
length_v number :=0;
current_char_v char(1);
padd_lenth_v number := 0;
password_hash_raw_v raw(2048) :='';
password_hash_hex_v varchar2(16) := '';

begin
length_v:=length(username||password);

for i in 1..length_v loop
current_char_v:=substr(upper(username||password),i,1);
ascii_string_v:=ascii_string_v||chr(0)||current_char_v;
end loop;

length_v:= mod((length_v*2),8);

if (length_v = 0) then
padd_lenth_v:= 0;
else
padd_lenth_v:=8 - length_v;
end if;

for i in 1..padd_lenth_v loop
ascii_string_v:=ascii_string_v||chr(0);
end loop;

uni_string_v:=utl_raw.cast_to_raw(ascii_string_v);
dbms_obfuscation_toolkit.desencrypt(input => uni_string_v, key => raw_key_v, encrypted_data => raw_string_v);
hex_string_v:=rawtohex(raw_string_v);

length_v:=length(hex_string_v);

raw_key_v:=hextoraw(substr(hex_string_v,(length_v-16+1),16));
dbms_obfuscation_toolkit.desencrypt(input => uni_string_v, key => raw_key_v, encrypted_data => password_hash_raw_v);

hex_string_v:=rawtohex(password_hash_raw_v);
length_v:=length(hex_string_v);
password_hash_hex_v:=substr(hex_string_v,(length_v-16+1),16);

return(password_hash_hex_v);
end;
/
show errors;


SQL> select password from dba_users where username = 'DUMMY';

PASSWORD
------------------------------
448E9EB8FEF04D8B

SQL> select password_hash('dummy','dummypw') from dual;

PASSWORD_HASH('DUMMY','DUMMYPW')
--------------------------------------------------------------------------------
448E9EB8FEF04D8B

Tuesday, July 7, 2009

Fast Refresh Materialized View

connect payroll
create materialized view log on payroll.employee;

connect payroll_audit
create materialized view payroll_audit.employee_mview as select * from payroll.employee;

How to reduce Oracle infrastructure costs (and keep your job)

How to reduce Oracle infrastructure costs (and keep your job)

A step-by-step process, with expert advice, tips and tricks for reducing IT Infrastructure costs.
By Robert Geier (Robert.Geier@ContractOracle.com)



If you have ever had to negotiate with a builder you may have heard the saying "I can build it Fast, Good, Cheap, choose two.". The same rule applies to IT projects. When the economy is growing companies are competing for market share and IT Managers and DBAs will be told "build it Fast and Good". As a result, DBAs focus on build time, build quality, and uptime, allowing unnecessary expenses and waste to creep into the systsm. When the economy is shrinking companies focus on cost cuts and efficiency and DBAs will be told "now make it Cheap".


In the current economy the best way to keep your job is to be smarter than the DBA sitting next to you, and be proactive about cost reduction. Your first thought may be that your systems are well run, and there is no room for improvement, but if you step back and look at the efficiency, you may be disappointed. In the past I worked for a large multinational company with a good reputation that hired the best DBAs, and bought the best servers. They followed industry "best practice" for maximum uptime, but when I did an efficiency survey across their systems the waste became obvious. For example :-


* they bought the best servers for DEV, TEST, PROD, DR, but the average CPU usage on the servers was only 5%. (95% wasted hardware, Unix SA, Data center ...)
* they bought the best SAN storage, but they kept 10% free space in the filesystem, 20% free space in tablespaces, and 10% free space in blocks. (40% wasted SAN, SAN Admin ...)
* they didn't trust autoextend, so they monitored millions of datafiles across thousands of databases, and manually extended them. (Many man-years of expensive DBA wasted.)


Of course there is no way to get to 100% hardware efficiency without impacting system availability, but in a large company even a 1% improvement can save millions of dollars and justify your salary. There are lots of ways for a smart DBA to reduce IT costs, and sometimes even small changes in policy can have large benefits. Just a simple change like turning on autoextend could reduce disk usage by 20% and save thousands of hours of DBA time, and millions of dollars a year. A simple rule like no new server purchases until average CPU usage reaches 50% can save many millions of dollars. Following is a rough plan along with a list of easy wins that I have used on previous cost reduction projects that may also help you :-



1. Start with a full accurate inventory and centralised monitoring. List all databases, servers, disk, SLA, owner, and metrics including disk usage, maximum and average cpu usage, cost, depreciation etc. Talk to your manager and your accountant to understand your costs and get a better idea of how they can help you. Ask your accountant what the company policy is on hardware depreciation, and leasing vs purchase. Identify cost reduction opportunities, document them, and set up a project plan with reasonable timelines for delivery. Start with easy wins, and keep the project timeline short, and the goals achievable.
2. Talk to your users. Be honest about the costs and SLA requirements for each database and suggest ways they can help you to reduce costs. Explain to developers how their decisions impact costs (poor sizing estimates, unused development systems, poorly written code, old data kept in the database etc). Consider moving low priority data to cheap servers, storage, network, data centers, and reduce frequency of backup and monitoring. Consider if DR, backup, or archival are needed based on the SLA.
3. Compare the costs of different Oracle licensing models. Options include CPU/Core vs Named User vs Site. Compare different CPU models and processor core pricing factors. Remember that license costs are negotiable, so talk to your Oracle Sales rep.
4. Consolidate databases. Patch all databases to the same level, standardise the characterset, and use DNS alias and DB Service for easier service relocation (not IP, hostname, SID). Use NCHAR instead of CHAR fields to avoid characterset conversion issues. Audit for security before consolidating, and avoid grants to PUBLIC or granting DBA or ANY roles (select any table etc). Consolidation means fewer sets of SYSTEM, TEMP, UNDO, REDO, ORACLE_HOME, and more efficient use of memory. The result is reduced disk space, tape space, network traffic, license and support costs, upgrades, patching, and DBA work. There is also potential for better performance by direct data access, and lower system integration costs. Try to combine applications with similar SLA and get signoff from the application owners that they understand the SLA. Stop adding more applications to the database before it becomes impossible to arrange downtime. If a user requests a new database, ask why it can't be just a new schema in an existing database.
5. Consolidate servers. If you have databases which cannot be consolidated (due to vendor support, version requirements, security etc), move them onto the same server. This can result in reduced hardware costs, and reduced license and support fees for databases, monitoring, filesystem, and backup software.
6. Use N+1 Active/Passive clusters instead of 1+1 clusters. Less wasted hardware.
7. Use Active/Active RAC clusters instead of Active/Passive clusters. Less wasted hardware and higher uptime, but be aware that the shared datafiles are still a single point of failure.
8. Compress large tables and indexes. Up to 80% reduction in disk space and tape usage, with lower network traffic and potential for performance improvement due to fewer blocks to read.
9. Use large ASM disk groups instead of many small filesystems. More efficient use of disk space, striping and mirroring, fewer hot spots, possible to grow and shrink storage with no database downtime, and no expensive filesystem. ASM is not just for RAC.
10. Turn on autoextend for permanent tablespaces. Monitor filesystem space and file MAXSIZE. Use ASSM, and limit file INCREMENT_BY to reduce transaction wait times during file growth. Avoid autoextend on UNDO, TEMP tablespaces to limit runaway transactions. Turning on autoextend can result in a 20% reduction in disk space, lower network usage, less manual work by DBAs, with minimal performance impact. Every GB saved on PROD can be multiplied by disk mirrors, DEV, TEST, and DR environments.
11. Use compressed database and archivelog backups. This will greatly reduce tape and/or disk usage, and potentially result in faster backups due to lower network traffic to tape unit and fewer blocks to write. Consider backup from standby to reduce load on the primary database.
12. Use incremental backups with change tracking logs. No need to backup unchanged blocks every day, means reduced tape and/or disk usage.
13. Use larger block sizes for larger segments. More efficient reads, less wasted disk space, larger files, less manual work by DBAs.
14. Set PCT_FREE 0 for WORM tables. Save 10% of disk space, tape space, network traffic. (WORM = Write Once Read Many - only inserts, no updates or deletes)
15. Delete old data or archive to cheap storage. Use partitions and/or transportable tablespaces for easy removal.
16. Set inactive tablespaces read only. Read only tablespaces can be on cheaper disk, are easily transportable, no need to replicate, no need to restore to dev and test, no need to backup every day.
17. Use Dataguard or Streams instead of SAN level replication. Dataguard can easily switch the direction of replication, and streams can be Active/Active multi-master for more efficient use of DR hardware.
18. Avoid keeping a backup on disk. Dataguard failover is much faster than database restore.
19. Frequently backup and delete archivelogs to reduce disk space. If you do need to keep a copy on disk, a compressed backup piece is more efficient than uncompressed archive logs.
20. Clone the ORACLE_HOME instead of installing every time. Save time on install, patching, upgrades and be certain that the builds are identical.
21. Use DBCA templates to reduce DBA workload when creating databases. Save time on post install tasks like setup of monitoring users security, and be certain builds are identical.
22. Turn on auditing to identify inactive users, then lock and drop them. Potential to reduce security risks, identify data to drop, and reduce named user license costs.
23. Monitor or audit for inactive tables and indexes to be dropped. Dropping unused segments can lead to disk and tape savings and potential performance improvements on inserts, updates, deletes.
24. Empty the recycle bin. The recycle bin is useful for restoring an accidentally dropped table, but not forever.
25. Use profiles. Limit the number of connections a user can have to the database, or the amount of CPU they can use. Also useful for enforcing password complexity and expiry.
26. Reorganize sparse tables and rebuild indexes to lower high water mark. Reduce disk usage and improve performance.
27. Use external tables instead of permanent staging tables. Less disk space needed, less redo/archive log to replicate and backup, and fewer disk writes.
28. Use global temporary tables for temporary data. Transactions are not written to permanent datafiles, are not written to redo/archive, and are not backed up or replicated.
29. Use nologging and bulk transactions. Reduce archivelogs on disk and tape.
30. Move less data. Instead of moving data around and having copies in every database try keeping it in a single location and access it via Database Links, Heterogeneous Services, SOA, ODBC, JDBC, or external tables.
31. Reduce processing peaks. Reduces hardware, improves performance, and reduces licence costs. Reduce the frequency of scheduled jobs, reduce the size of peak period transactions and increase the size of off-peak transactions using streams, materialized views, parallel processing etc.
32. Move low SLA processing like batch jobs off high SLA databases. Use Logical Dataguard, Streams, or materialised views for near-realtime data on low SLA machines.
33. Identify high resource transactions and tune them (top 10 CPU, READS, SORTS, REDO etc)
34. Identify which applications only need Oracle Standard Edition. Considerable license and support cost savings are possible if you don't need Enterprise Edition options like partitioning.
35. Virtualize DEV and TEST servers. This can be done either at server level (VM) or disk level (disk snaps). Make more efficient use of servers and storage.
36. Use cheaper PC software. E.g Linux, Cygwin, SQL Developer instead of Windows, Exceed, Toad.
37. Just in time provisioning. Why buy the production server and software licences on day 1 of a 2 year development project ? Why have 2 years of disk growth online ? Delay the purchase, lower your costs, and lower your workload. Read and understand the software licence, and if you have any doubt follow up with the vendor or company counsel.
38. Standardise on one monitoring product. Reduce software license costs, monitoring servers, email alerts, and server CPU usage.
39. Implement centralised password authentication. If a user only has one username and password for every PC, Server, Database, or Application login, think about how many thousands of Helpdesk calls for changing passwords can be avoided. If a person leaves the company, only one account needs to be locked.
40. End with accurate metrics listing databases, servers, disk usage, maximum and average cpu usage, costs etc. Calculate the cost savings, make sure your boss knows about them, and start again. You may not have reduced the total spend, but you should at least have lowered the growth rate.



It does take time for the cost reduction to show, so start ASAP, identify the easiest wins, add the improvements back into the new build standards and monitoring systems, then repeat the cycle. Remember that it is easy to plan cost reductions, but change introduces risk and fear, and getting agreement from the developers, users, application owners, and other DBAs will take persuasion and coercion. Other employees may be more concerned with uptime than cost reduction, and you won't get agreement for every change you propose, so choose your battles carefully. Put together a cost/benefit proposal for your manager detailing estimated savings, along with risks, and if you can convince them, their political support will be invaluable for removing roadblocks.



The methods of cost reduction listed in this document are only examples and may not work in every company, but I hope they will start you on the right path. Feedback is welcome.

Monday, July 6, 2009

Audit DDL with a BEFORE DDL Trigger.

create table DDL_AUDIT
(audit_date date,
username varchar2(30),
instance_number integer,
database_name varchar2(9),
object_type varchar2(19),
object_owner varchar2(30),
object_name varchar2(128),
sql_text varchar2(2000));



CREATE OR REPLACE TRIGGER BEFORE_DDL_TRG BEFORE DDL ON DATABASE
DECLARE
l_sql_text ora_name_list_t;
l_count NUMBER;
l_puser VARCHAR2(30) := NULL;
l_sql varchar2(2000);
BEGIN
l_count := ora_sql_txt(l_sql_text);
l_puser := SYS_CONTEXT('USERENV', 'KZVDVCU');

l_count := ora_sql_txt(l_sql_text);
for i in 1..l_count
loop
l_sql := l_sql||l_sql_text(i);
end loop;

insert into ddl_audit (audit_date, username, instance_number, database_name, object_type, object_owner, object_name, sql_text)
values (sysdate, l_puser,ora_instance_num,ora_database_name,ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name,l_sql);

exception
when others then
null;
END;
/
show errors;

#### or if you want screen output.

CREATE OR REPLACE TRIGGER BEFORE_DDL_TRG BEFORE DDL ON DATABASE
DECLARE
l_sql_text ora_name_list_t;
l_count NUMBER;
l_puser VARCHAR2(30) := NULL;
BEGIN
l_count := ora_sql_txt(l_sql_text);
l_puser := SYS_CONTEXT('USERENV', 'KZVDVCU');

dbms_output.put_line(l_puser);
dbms_output.put_line(ora_instance_num);
dbms_output.put_line(ora_database_name);
dbms_output.put_line(ora_dict_obj_type);
dbms_output.put_line(ora_dict_obj_owner);
dbms_output.put_line(ora_dict_obj_name);

l_count := ora_sql_txt(l_sql_text);
for i in 1..l_count
loop
dbms_output.put_line(l_sql_text(i));
end loop;

exception
when others then
null;
END;
/
show errors;