Importing Trusted Certificates Into Oracle Wallet

Download certificates to your computer as below (certificate_root and certificate_int)

Create an wallet via orapki

orapki wallet create -wallet <wallet_location> -pwd <password> -auto_login

Then add certificates into wallet.

orapki wallet add -wallet <wallet_location> -trusted_cert -cert "<wallet_location>/certificate_root.cer" -pwd <password>
orapki wallet add -wallet <wallet_location> -trusted_cert -cert "<wallet_location>/certificate_int.cer" -pwd <password>

Show ACLs

SQL> SELECT * FROM dba_network_acls ORDER BY acl;
SQL> SELECT * FROM dba_network_acl_privileges ORDER BY acl, aclid, privilege; 

Create an ACL from database and assign site address.

BEGIN
 SYS.dbms_network_acl_admin.create_acl(
   acl => '<ACL_NAME>',
   description => '<ACL DESCRIPTION>',
   principal => '<ORACLE_USER>',
   is_grant => TRUE,
   privilege => 'connect',
   start_date => null,
   end_date => null
 );

 SYS.DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
   acl => '<ACL_NAME>',
   principal => '<ORACLE_USER>',
   is_grant => true,
   privilege => 'connect'
 );

 SYS.DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
   acl => '<ACL_NAME>',
   principal => '<ORACLE_USER>',
   is_grant => true,
   privilege => 'resolve'
 );

 SYS.dbms_network_acl_admin.assign_acl(
   acl => '<ACL_NAME>',
   host => '*.emrahgumus.com',
   lower_port => null,
   upper_port => null
 );
END;
/

Then you can test via below code.

SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE
  lo_req  UTL_HTTP.req;
  lo_resp  UTL_HTTP.resp;
BEGIN
  UTL_HTTP.SET_WALLET (
       'file:<wallet_location>',
       '<password>');
    lo_req := UTL_HTTP.begin_request ('https://www.emrahgumus.com');
    lo_resp := UTL_HTTP.get_response (lo_req);
    DBMS_OUTPUT.put_line (lo_resp.status_code);
    UTL_HTTP.end_response (lo_resp);
END;
/

ORA-30683: failure establishing connection to debugger

If you getting the ORA-30683 error when attempting to connect database via SQL Developer:

Connecting to the database ORCLDB.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.121.104', '54070' )
ORA-30683: failure establishing connection to debugger
ORA-12535: TNS:operation timed out
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database ORCLDB.

Execute below command then try to connect again.

GRANT DEBUG CONNECT SESSION TO <USER>;
GRANT DEBUG ANY PROCEDURE TO <USER>;
BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
 host => '192.168.121.104',
 lower_port => null,
 upper_port => null,
 ace => xs$ace_type(
         privilege_list => xs$name_list('jdwp'),
         principal_name => '<USER>',
         principal_type => xs_acl.ptype_db
        )
 );
END;
select * from dba_network_acl_privileges where principal = '<USER>' and privilege = 'JDWP' order by acl, aclid, privilege;  

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 );