Category: ORACLE DATABASE

Foreign Key’e Sahip Kolonların Farklı Data Uzunlukta Olanları Nasıl Bulunur?

Oracle veritabanında oluşturulmuş foreign keylerin bulunduğu kolonların farklı data uzunluğuna sahip olanlarını aşağıdaki sql ile bulabilirsiniz. 

SELECT acol.owner,
acol.table_name,
acol.column_name,
acol.data_type,
acol.data_length,
acol.data_precision,
bcol.owner,
bcol.table_name,
bcol.column_name,
bcol.data_type,
bcol.data_length,
bcol.data_precision
FROM dba_tab_columns acol, dba_tab_columns bcol
WHERE (acol.owner,
acol.table_name,
acol.column_name,
bcol.owner,
bcol.table_name,
bcol.column_name) IN
(SELECT b.owner b_owner,
b.table_name b_tblname,
b.column_name b_column,
c.owner c_owner,
c.table_name c_tblname,
c.column_name c_column
FROM dba_cons_columns b,
dba_cons_columns c,
dba_constraints a
WHERE b.constraint_name = a.constraint_name
AND a.owner = b.owner
AND b.position = c.position
AND c.constraint_name = a.r_constraint_name
AND c.owner = a.r_owner
AND a.constraint_type = 'R') and (acol.data_length <> acol.data_length or acol.data_precision <> bcol.data_precision );

How to Enable/Disable Unified Audit

Hello everyone, I want to explain how to enable/disable unified audit option on Oracle Databases. When you enable or disable unified audit option, all databases must be closed while operation time.

You can check the unified audit that open or closed status with the below code

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE
-------
NO

You can open a unified audit option with the below code.

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle

When operation completed, you can check to unified audit status.

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
VALUE
-------
YES

Also, you can purge to logs on the databases with the below code.

BEGIN 
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp =>  FALSE);
END; 
/

You can purge to logs on the OS with the below code.

EXEC DBMS_AUDIT_MGMT.DROP_PURGE_JOB('CLEANUP_OS_DB_AUDIT_RECORDS'); 


BEGIN 
DBMS_AUDIT_MGMT.DEINIT_CLEANUP( 
AUDIT_TRAIL_TYPE   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL); 
END;
/

Lastly, you can disable to unified audit with the below code.

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_off ioracle

and check

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

VALUE
-------
NO