Skip to content

Latest commit

 

History

History
362 lines (276 loc) · 13 KB

Oracle.md

File metadata and controls

362 lines (276 loc) · 13 KB

Oracle is an enterprise database.

installation of Oracle client

Oracle support

https://www.oracle.com/us/assets/lifetime-support-technology-069183.pdf

Oracle Client for Windows

It comes with 32 bit and 64 bit versions. 64 bit versions can only be installed in 64 bit Windows.

Registry setup.

Oracle writes to Windows registry for 32 bit or 64 bit installation. Here are some common registry values added to registry. See

  • Registry setup
  • The registry for Windowss 32 bit and 64 bit are saved in different locations.
  • Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ORACLE for 32 bit.
  • Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE for 64 bit version.

ODBC clients setup

check the oracle database is up

tnsping serivcename
lsnrctl status
  • And check the port number is correct
  • look for the following file in product\oracle_version\client\network\admin\sqlnet.ora
  • sqlnet.ora defines the search order of the service name

sample setup for sqlnet.ora

SQLNET.ORA Network Configuration File: C:\oracle\ora21c\NETWORK\ADMIN\sqlnet.ora

Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (LDAP,TNSNAMES)

sample setup for ldap.ora

  • definition of LDAP configuration
DEFAULT_ADMIN_CONTEXT = "dc=xyz,dc=abc"

DIRECTORY_SERVERS= (ldapserver:portNumber)

DIRECTORY_SERVER_TYPE = OID

sample setup for tnsnames.ora

TNSNAME = 
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = "Fully qualified domain name" )(PORT = portNumber))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = serviceName)
    )
  )

sqlplus

debug

GRANT DEBUG ANY PROCEDURE TO hr;
GRANT DEBUG CONNECT SESSION TO hr;
GRANT EXECUTE ON DBMS_DEBUG_JDWP To hr;
COMMIT;

alter session plsql_optimize_level = 1

modifying table.

alter table table_name set unused (column_name);
alter table table_name move online;

alter table mytable enable row movement;
alter table mytable shrink space;

get all the table names and columns of a system.

select col.column_id, 
       col.owner as schema_name,
       col.table_name, 
       col.column_name, 
       col.data_type, 
       col.data_length, 
       col.data_precision, 
       col.data_scale, 
       col.nullable
from sys.all_tab_columns col
inner join sys.all_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name
-- excluding some Oracle maintained schemas
where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS', 
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
order by col.owner, col.table_name, col.column_id;

With privilege on dba_tab_columns and dba_tables

select col.column_id, 
       col.owner as schema_name,
       col.table_name, 
       col.column_name, 
       col.data_type, 
       col.data_length, 
       col.data_precision, 
       col.data_scale, 
       col.nullable
from sys.dba_tab_columns col
inner join sys.dba_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name
-- excluding some Oracle maintained schemas
where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS', 
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
order by col.owner, col.table_name, col.column_id;


select * from all_objects where object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'VIEW')
select * from user_tab_columns
select * from user_tab_columns where table_name like '%yourtable% order by column_name
SELECT VIEW_NAME, TEXT FROM USER_VIEWS;

split a large table to smaller one.

CREATE TABLE xy_001 AS

SELECT  *

FROM    (

    SELECT  y.*

          ,CEIL(ROW_NUMBER() OVER (ORDER BY your_date_column) /100000) x

    FROM    y

        )

WHERE  x = 1;

select certain number of rows in Oracle

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

installation

running old version of Oracle on AWS

XE specific

Oracle tips

Oracle APEX

Oracle APEX plugins

APEX debugging

select * from APEX_WORKSPACE_ACTIVITY_LOG
select * from APEX_DEBUG_MESSAGES

f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly
apex_application.g_print_success_message := 'message';

wwv_flow.debug ('message');
apex_debug.message('message');

Oracle APEX ldap integration

Oracle Rest

  DBMS_NETWORK_ACL_ADMIN package 
  
  BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_190100',
                           principal_type => xs_acl.ptype_db));
END;


SELECT ACL, PRINCIPAL
  FROM DBA_NETWORK_ACLS NACL, XDS_ACE ACE
 WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL AND
       NACL.ACLID = ACE.ACLID AND
       NOT EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);

Oracle APEX with JS libraries

Oracle Apex Javascript integration

Oracle VM

Is Apex installed?

SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = 'APEX'

ORDS Rest service ORDS

Add the following in defaults.xml for debugging purpose.

<entry key="debug.printDebugToScreen">true</entry>

Expressjs integration

node integration

in memory db.

select * from v$inmemory_area
alter system set inmemory_size = 100M scope=SPFILE;
alter system set inmemory_expressions_usage = 'ENABLE';
  • related topics: EXADATA, IMCS, RAC architecture,

data model related

PL/SQL developer plugins

Oracle upgrade

PDF geneartion