WYSIWYG md editor SimpleMDE or use some desktop markdown text editor    Home    [ html2markdown ]
Markdown txt 01/001_db/z_all_old_oracle_DB18c_devsuite10g_F6i_to_apex.txt Parsedown-ed to HTML.    [ Edit text ]    [ Colored text ]

Top.....Instalac 18c XE.....Instalac APEX 20.1.....Conv. 6i to APEX (Devsuite10g on WinXP) 2020.08.17

J:

cd J:\app\oraclexe\product\18.0.0\dbhomeXE\bin = [oracle_home]\bin

sqlplus / as sysdba or sqlplus.exe sys/MYPSW@//SSPC2:1521/XE as sysdba
@users - my script, see below
quit or ALTER SESSION SET container = XEPDB1;

sqlplus.exe sys/MYPSW@//SSPC2:1521/XEPDB1 as sysdba
@users
quit or ALTER SESSION SET container = CDB$ROOT;

Sqlplus hr/hr@ora7 - on PC where 18c XE is installed (if listener port is default = 1521) Sqlplus HR/HR@XEPDB1 or Sqlplus hr/hr@xepdb1 XEPDB1 and ora7 are synonims (plugable DB name) conn / as sysdba is same as conn system/MYPSW@sspc2:1521/XEPDB1 as SYSDBA (both conn to SYS user)

Sqlplus HR/HR@sspc2/XEPDB1 - on some other intranet PC Sqlplus HR/HR@sspc2:1521/XEPDB1

sqlplus system@dbhost.example.com:1521/XEPDB1 - on internet PC

sqlplus /nolog
conn / as sysdba - connects even if Net Services DB listener lsnrctl is not running shutdown abort
startup

lsnrctl allows you to connect to DB over TCP/IP from the same machine or other machines on the network.




1. INST / DEINST DB 18c XE

release 18.0.0.0.0 ver 18.4.0.0.0 from OracleXE184_Win64.zip

Top.....Instalac 18c XE.....Instalac APEX 20.1.....Conv. F6i to APEX (Devsuite10g on WinXP)

Specified value of MEMORY_TARGET is too small, needs to be at least 2048 M
SHOW PARAMETER PFILE;
SHOW PARAMETER MEMORY_TARGET
ALTER SYSTEM SET MEMORY_TARGET='2048 M' SCOPE=spfile;
SHUTDOWN --ORA-01034 is simple error, it means that your DB is down! !!!!!!!!!!!!!!!!!!!!!!!!!!!
NOT POSSIBLE : STARTUP

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

You can edit pfile you created to correct errors you have. Then once your instance is started, you can create an spfile out of that pfile.

J:
cd J:\app\oraclexe\product\18.0.0\dbhomeXE\bin sqlplus /nolog
sqlplus / as sysdba
--outputs : Connected to an idle instance.
create pfile = 'c:\temp\init.ora' from spfile;

--Then you edit file see *.memory_target=2048 M and use that to start startup pfile=c:\temp\init.ora

--once your database is open
create spfile from pfile='c:\temp\init.ora';

C:\temp\INIT.ORA

ORA-32006: SEC_CASE_SENSITIVE_LOGON initialization parameter has been deprecated

xe.__data_transfer_cache_size=0
xe.__db_cache_size=1107296256
xe.__inmemory_ext_roarea=0
xe.__inmemory_ext_rwarea=0
xe.__java_pool_size=16777216
xe.__large_pool_size=33554432
xe.__oracle_base='J:\app\oraclexe\product\18.0.0'#ORACLE_BASE set from environment
xe.__pga_aggregate_target=536870912
xe.__sga_target=1610612736
xe.__shared_io_pool_size=83886080
xe.__shared_pool_size=352321536
xe.__streams_pool_size=0
*.audit_file_dest='J:\app\oraclexe\product\18.0.0\admin\XE\adump'
*.audit_trail='db'
*.compatible='18.0.0'
*.control_files='J:\app\oraclexe\product\18.0.0\oradata\XE\control01.ctl','J:\app\oraclexe\product\18.0.0\oradata\XE\control02.ctl'
*.db_block_size=8192
*.db_name='XE'
*.diagnostic_dest='J:\app\oraclexe\product\18.0.0'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_XE'
*.memory_target=2048 M
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=512m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_target=1536m
*.undo_tablespace='UNDOTBS1'

...dbhomeXE/dbs/init.ora

# J:\app\oraclexe\product\18.0.0\dbhomeXE\dbs\init.ora
# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time) J:/app/oraclexe/product/18.0.0

#db_name='ORCL'
db_name='XE'
#memory_target=1G
memory_target=2048 M
processes = 150
#audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_file_dest='J:/app/oraclexe/product/18.0.0/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='J:/app/oraclexe/product/18.0.0/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='J:/app/oraclexe/product/18.0.0'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

set oracle_sid=XE =DATABASE NAME
sqlplus /nolog
conn sys/SYSPSW as sysdba
shutdown abort
startup

https://www.aflorestorres.com/2019/03/install-oracle-database-18c-express-and.html

https://dbahelp2018.wordpress.com/install-oracle-18c-xe-on-windows-10/

Sources: https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinw/
Big articles https://dbvisit.com/blog/oracle-database-18c-express-edition-getting-started-part-4 Linux, docker

If you don’t already have an account you will need to create one to download the s/w:
https://profile.oracle.com/myprofile/account/create-account.jspx

https://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

on Windows 10 pro 64 bit Ver : 10.0.19041

  1. Deinstall 11g XE (615 MB) and check environment variables - Win key and type env - delete any ORACLE_HOME env. var.
  2. run as admin L:\1_downloads\data\1_instalac\ora18c\setup.exe
    1. DESTIN_DIR : J:\app\oraclexe\product\18.0.0
    2. ORACLE_HOME : DESTIN_DIR\dbhomeXE : J:\app\oraclexe\product\18.0.0\dbhomeXE
    3. ORACLE_BASE : DESTIN_DIR
  3. When prompted, set database password to use for SYS, SYSTEM and PDBAADMIN accounts alter user identified by ;)
  4. Verify and remember the Oracle Home and other locations. Note that the spaces are not allowed in directory names.
  5. Next hit Install to get it going Windows defender will alert for java firewall rule, I've changed this to 'Private networks, such as my home or work network'

Note details for our XE DB:

Server: localhost
Port: 1521
Multitenant container DB : localhost:1521 Container DB will be called XE
Pluggable DB : XEPDB1 localhost:1521/XEPDB1
Enterprise Express URL : https://localhost:5500/em

Click Finish, to close installer



Check DB in GUI Enterprise Manager Express

https://localhost:5500/em
User Name: sys
Password: ...
Container name: Leave blank (Enter a PDB name if you want to access a specific PDB or leave it blank to access the root container. ) as sysdba: tick box

Making Oracle Database EM Express Available to Remote Clients

After you install Oracle Database XE, EM Express is only available from the local server, it cannot be accessed remotely.
To make EM Express available to remote clients, start SQLPlus, log in as SYSTEM , and execute following procedure. From CLI run following commands. In the commands, replace "oracle_home" with path to your Oracle Home; see 18c XE Installation Guide Table 5-1 for more details about Oracle Home.

J:
cd J:\app\oraclexe\product\18.0.0\dbhomeXE\bin
sqlplus system               or   sqlplus /nolog     sqlplus / as sysdba  
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);





select sys_context( 'userenv', 'current_schema' ) from dual;  
prints SYS_CONTEXT('USERENV','CURRENT_SCHEMA')  
           ---------------------------------------
           SYS  

conn hr/hr@xepdb1
select sys_context( 'userenv', 'current_schema' ) from dual;
prints  SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
             ---------------------------------
             HR

You cannot change your identity in a definers rights procedure, it is fixed AT COMPILE time by definition.

How can I in procedure query system to change and return current schema I am in?

https://asktom.oracle.com/pls/asktom/asktom.search?tag=how-to-get-current-schema

start J:\app\oraclexe\product\18.0.0\dbhomeXE\demo\schema\human_resources\hr_main.sql
After DB 18c install. 35 rows selected then 36 :
PDBADMIN 101 2020-08-25 00:44:00

SCOTT, HR and other sample schemas are in PDB and not in CDB. So, you need to login as sysdba into PDB :

sqlplus SYS/password@XEPDB1 AS SYSDBA
show con_name
sho user
show pdbs

ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;
grant CONNECT,DBA,RESOURCE to HR IDENTIFIED BY HR;

conn HR/HR@XEPDB1

select tname from tab;

exit

ALTER USER system IDENTIFIED BY MYPSW ACCOUNT UNLOCK ALTER USER system IDENTIFIED BY MYPSW

sqlplus scott/tiger@pdborcl

SQL> sho user; USER is "SCOTT"

SET LINESIZE 254 trimspool on arraysize 100 tab off
COLUMN username HEADING username FORMAT A30
COLUMN password HEADING password FORMAT A15
select username, password from dba_users order by username ;

http://www.rebellionrider.com/how-to-unlock-hr-user-in-oracle-database-18c-by-manish-sharma/ By Manish Sharma - January 5, 2019 Unlocking hr (or any) schema in DB 18c :

  1. Finding out container ID of pluggable DB which contains our schema
    --Conn to container DB CBD$ROOT :
    Sqlplus / as sysdba
    --Query v$PDBS dynamic performance view to see container names and IDs of all pluggable DBs created inside root container :
    SELECT name, con_id FROM v$pdbs;

    NAME                 CON_ID (container ID)
    ------------------ ------------------------------
    PDB$SEED               2    seed pluggable DB
    **XEPDB1**                 3    **name of default pluggable DB** created during installation (ORCLPDB) - **has all our sample (test) schemas**.

    PDB$SEED is the seed pluggable DB & a system supplied template which container DB uses for creating user defined pluggable DBs.

  2. Finding out service name of pluggable DB - usually, pluggable DB and its service shares the same name, but check it:
    SELECT name Service_Name FROM v$active_services WHERE con_id =3; --displays xepdb1

  3. Creating an entry in TNSNAMES.ora network configuration file inside "Network\admin" folder inside our DB_HOME :
    J:\app\oraclexe\product\18.0.0\dbhomeXE\network\admin

    PS C:\WINDOWS\system32> echo %dbhomeXE%
    %dbhomeXE%

    Mode                 LastWriteTime         Length Name
    ----                 -------------         ------ ----
    d-----        25.8.2020.      0:16                sample
    -a----        25.8.2020.      0:19            658 listener.ora
    -a----        25.8.2020.      0:19            464 sqlnet.ora
    -a----        25.8.2020.      0:19            263 sqlnet20082512AM1922.bak
    -a----        25.8.2020.      0:38            625 tnsnames.ora <======

    Using our pluggable database name and its corresponding service we need to create an entry for our listener - using NETCA utility or we can do it manually (is faster).

    tnsnames.ora - XE is not same as ora7

    #J:\app\oraclexe\product\18.0.0\dbhomeXE\network\admin\tnsnames.ora
    #tnsnames.ora Network Configuration File: 
    #Generated by Oracle configuration tools.
    
    #name of Container DB (privileged users like sys)
    XE =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = XE)
        )
      )
    #name of default pluggable DB created during install. (all sample schemas, APEX apps...) :
    XEPDB1 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = xepdb1)
        )
      )
    
    ora7 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = xepdb1)
        )
      )
    
    LISTENER_XE =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521))
    
    ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
        (CONNECT_DATA =
          (SID = CLRExtProc)
          (PRESENTATION = RO)
        )
      )

    In Win CLI with admin rights restart listener : Lsnrctl reload
    lsnrctl status
    lsnrctl start

  4. Switching from Container DB XE CDB$ROOT to Pluggable DB XEPDB1
    High privileged users like sys are placed inside a container DB and all sample schemas are in default pluggable DB, which means in order to unlock them we need to switch to XEPDB1.

    ALTER SESSION SET container = XEPDB1;

  5. Unlocking HR user
    SELECT name, open_mode FROM v$pdbs;

    NAME                                    OPEN_MODE
    ----------------------------  -------------------
    XEPDB1                                READ WRITE

    If open mode is mounted - means DB isn't open. To open your pluggable DB :
    ALTER PLUGGABLE DATABASE open;

    Once you've done all the above steps and configured your DB properly then :
    ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;
    grant CONNECT,DBA,RESOURCE to HR IDENTIFIED BY HR;

    exit
    Sqlplus HR/HR@localhost:1521/XEPDB1

    show con_name --(CDB$ROOT) XEPDB1 sho user --(SYS) USER is "HR"

COUNTRIES ORA-01410: invalid ROWID

conn hr/hr@ora7
set linesize 150
select COUNTRIES.*, rowidtochar(ROWID) as ROWID_ from COUNTRIES;
--err: ORA-01410: invalid ROWID
drop table t;
create table t as (select COUNTRIES.* from COUNTRIES);
select t.*, rowidtochar(ROWID) as ROWID_ from t;

--treba drop tablice zbog ORGANIZATION INDEX, pa ovo ne ide:
--alter table LOCATIONS disable constraint LOC_C_ID_FK;
-- delete COUNTRIES;

alter table LOCATIONS drop constraint LOC_C_ID_FK;
drop table COUNTRIES;
CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, region_id NUMBER
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id)
)
--ORGANIZATION INDEX <-- zbog ovoga javi ORA-01410: invalid ROWID:
;

ALTER TABLE countries
ADD ( CONSTRAINT countr_reg_fk
FOREIGN KEY (region_id)
REFERENCES regions(region_id)
) ;
insert into COUNTRIES (select t.* from t);

--alter table LOCATIONS enable constraint LOC_C_ID_FK;
alter table LOCATIONS add CONSTRAINT "LOC_C_ID_FK"
FOREIGN KEY ("COUNTRY_ID") REFERENCES "HR"."COUNTRIES" ("COUNTRY_ID") ;
--commit;
select COUNTRIES.*, rowidtochar(ROWID) as ROWID_ from COUNTRIES;
prompt kad tablica nije ORGANIZATION INDEX gornji select radi !!

Not working, Why error ?

Sqlplus HR/HR@//sspc2:1521/XEPDB1
conn HR/HR@//sspc2:1521/XEPDB1

ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

sqlnet.ora SQLNET.ALLOWED_LOGON_VERSION=11

#J:\app\oraclexe\product\18.0.0\dbhomeXE\NETWORK\ADMIN\sqlnet.ora
#sqlnet.ora Network Configuration File: 
#Generated by Oracle configuration tools.
#This file is actually generated by netca. But if customers choose to 
#install "Software Only", this file wont exist and without the native 
#authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

#From 12cR2 and beyond oracle enforced higher authentication level 
#Two options:
#1. RESET PASSWORD of these accounts (password_versions) value should be updated, and make sure your application CLIENT IS COMPATIBLE WITH 18c !
#2. LOWER AUTHENTICATION by setting following parameter in sqlnet.ora file for example : **SQLNET.ALLOWED_LOGON_VERSION=11**

SQLNET.ALLOWED_LOGON_VERSION=11

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

From 12cR2 and beyond oracle enforced higher authentication level

SET LINESIZE 254 trimspool on arraysize 100 tab off  
COLUMN username HEADING username FORMAT A30  
COLUMN PASSWORD_VERSIONS HEADING PASSWORD_VERSIONS FORMAT A15  
select USERNAME, PASSWORD_VERSIONS from dba_users order by USERNAME ;

username                       PASSWORD_VERSIO
------------------------------ ---------------
...
HR                             11G 12C
...

Two options:

  1. reset password of these accounts (password_versions) value should be updated, and make sure your application client is compatible with 18c !
  2. lower authentication by setting following parameter in sqlnet.ora file for example : SQLNET.ALLOWED_LOGON_VERSION=11

cd [oracle_home]\bin

lsnrctl status

Shows values of a number of important parameters:

  1. port the listener listens on
  2. list of services registered with the listener. Default services created by Oracle Database XE are XE and XEPDB1 . XE service connects you to the root container of the database ( CDB$ROOT ) and the XEPDB1 service connects you to default pluggable database ( XEPDB1 ) created at the installation time. For each new pluggable DB (PDB) in DB, there will be a new default service created with the same name as the PDB. Note: If DB XE instance is shut down, lsnrctl status command does not show any services you can connect to.
  3. port EM Express listens on
  4. name of the configuration file used by the listener
  5. name of the log file

Easy Connect strings

  1. Multitenant container database: host[:port] SSPC2:1521
  2. Pluggable database: host[:port]/service_name SSPC2:1521/XEPDB1

Specifying port is optional when the listener is setup with default port 1521 . You must specify port number if you use another port.

If you are connecting from a remote computer, you need to provide hostname eg SSPC2 (where XE is installed) instead of localhost.

Eg you can connect to root container of DB from a client computer with SQLPlus using following commands: cd [oracle_home]\bin
sqlplus system@dbhost.example.com:1521

You can connect to default pluggable database XEPDB1 using following commands:
cd [oracle_home]\bin
sqlplus system@dbhost.example.com:1521/XEPDB1

Replace dbhost.example.com with your DB host name. If needed, replace 1521 with port number the listener listens on. You can replace XEPDB1 with name of another PDB you want to connect to.

To shorten connect strings or avoid hardcoding host name and port in app. code and DBA scripts, you can define an alias for connect string in configuration file [oracle_home]\network\admin\tnsnames.ora on DB clients. See Configuring Local Naming Method for more details.

See Also:

  1. 2 Day DBA for more information about listener and network configuration
  2. Oracle Database Development Guide for more information about connection strategies for database applications
  3. Ora. DB Net Services Admins Guide and Ora. DB Net Services Reference for more information about Net Services and their configuration.
  4. Oracle Multitenant Administrator’s Guide for more information about containers and connecting to a container in a CDB.

By default, when Ora. DB service is started, container DB is started and opened, but any pluggable DBs must be opened before use - manually or set to automatically open.

Starting Up / Shutting Down Using Win. Services

Start menu, type services.msc, OracleServiceXE service in Services window, Right-click service name.

OracleOraDB18Home[n]TNSListener , where [n] is a number chosen by Oracle Database XE installer based on other Oracle homes previously installed on the host.

Starting Up and Shutting Down Using SQL Plus

cd [oracle_home]\bin sqlplus / as sysdba SQL> SHUTDOWN IMMEDIATE or
SQL> STARTUP SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable DBs can be set to automatically open when container DB opens by connecting to the container database via SQL Plus (as above), and issuing following SQL:
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL SAVE STATE;




select name, open_mode, cdb from v$database ;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
XE        READ WRITE           YES

show pdbs

was but no more err SP2-0382: The SHOW PDBS command is not available (on 18c XE)

ipconfig

Ethernet adapter Ethernet:

   Connection-specific DNS Suffix  . :
   IPv4 Address. . . . . . . . . . . : 192.168.5.12
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . : 192.168.5.1

Ethernet adapter Ethernet 3:

   Connection-specific DNS Suffix  . :
   Link-local IPv6 Address . . . . . : fe80::a099:c564:36e6:bf89%12
   IPv4 Address. . . . . . . . . . . : 192.168.56.1 (Oracle Linux on Virtual Box)
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . :

sqlplus system/MYPSW@192.168.5.12:1521/XEPDB1
Connected.
or
conn system/MYPSW@localhost:1521/XEPDB1
Connected.

or

conn system/MYPSW@sspc2:1521/XEPDB1

CONN SYS as SYSDBA

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE SCOPE=BOTH;
SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

show con_name
-- XEPDB1
sho user
-- USER is "HR"

ALTER USER hr identified by hr account unlock;
grant CONNECT,DBA,RESOURCE to HR IDENTIFIED BY HR;

conn HR/HR@localhost:1521/XEPDB1
Connected. - case sensitive if we do not SEC_CASE_SENSITIVE_LOGON = FALSE !!

select tname from tab;



SQLdeveloper 19.2.1

https://www.oracle.com/downloads/

Click new connection:
Name: XEPDB1_sys
Username: sys
Password: MYPSW
Save Password: check box
Role: SYSDBA
Hostname: localhost
Port: 1521
Service name: xepdb1

Then click test, should see : Status Success
Click Save -> Click Connect
You are now connected to 18c XE db.



Can optionally update tnsnames.ora file

in s/w base dir/network/admin/tnsnames.ora file to add an connection alias for xepdb1 as below, for me that directory is : J:\app\oracle\product\18.0.0\dbhomeXE\network\admin

XEPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ACER-HOME-PC)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XEPDB1) ) )

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
FILE_NAME_CONVERT=('J:\app\oraclexe\product\18.0.0\dbhomeXE\oradata\XE\PDBSEED\',J:\app\oraclexe\product\18.0.0\dbhomeXE\oradata\XE\PDB2\');

https://smarttechways.com/ https://smarttechways.com/2019/03/05/ora-16000-database-or-pluggable-database-open-for-read-only-acc

https://smarttechways.com/2020/08/30/set-the-oracle-home-environment-variable-in-windows-platform/comment-page-1/#comment-16845



Uninstall Oracle 18c XE and delete database (destructive) – CAUTION

(Will need a reboot at the end to complete, but can reboot later if needed)

As per documentation: https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinw/deinstalling-oracle-database-xe.html#GUID-DC36940C-D201-457B-A3D5-D6E2BB800120

Use Add or Remove Programs to deinstall Oracle Database XE:
In the Windows Control Panel, select Add or Remove Programs.
Select Oracle Database 18c Express Edition.
Click Change/Remove or Uninstall.
Follow the prompts to deinstall Oracle Database Express Edition. After few minutes, the window will close and the deinstallation will be complete.

Deinstalling

If you want to save your data files but remove the Oracle Database XE software and database, then first export the data before you deinstall.
Caution:
When you deinstall Oracle Database XE, all components, including data files, the database, and the software, are removed. Because the deinstallation process removes all files from the directory in which Oracle Database XE is installed, back up any files from the directory (if needed) before you deinstall. The database will no longer be operational after deinstallation. You can deinstall Oracle Database XE using one of the following methods:

  1. Deinstalling Using User Interface
  2. Deinstalling Using Silent Mode

Deinstalling Using User Interface

Use Add or Remove Programs to deinstall Oracle Database XE:

  1. In the Windows Control Panel, select Add or Remove Programs.
  2. Select Oracle Database 18c Express Edition.
  3. Click Change/Remove or Uninstall.
  4. Follow the prompts to deinstall Oracle Database Express Edition. After few minutes, the window will close and the deinstallation will be complete. Alternatively, you can use following msiexec command to perform the deinstall: msiexec /x {C220B7FD-3095-47FC-A0C0-AE49DE6E320A}

12.11.2 Deinstalling Using Silent Mode If the downloaded software is available, you can use setup.exe to deinstall Oracle Database XE. For example, if you downloaded and extracted the files to a directory called xe_temp , enter the following command to deinstall Oracle Database XE:
c:\xe_temp> setup.exe /s /x /v"/qn /Lv c:\test1.log"
If you do not have the downloaded software ( setup.exe ) you can deinstall using: msiexec /qn /x {C220B7FD-3095-47FC-A0C0-AE49DE6E320A}
You can replace /qn with /qb to display the progress bar.
Note:

  1. Silent deinstall runs asynchronously; you can check the log file for the completion status.
  2. After the deinstall is successfully completed, the following message is available in the log file:
    MSI (s) (8C:C4) [Timestamp]: Windows Installer removed the product. Product Name: Oracle Database
    18c Express Edition. Product Version: 18.4.0.0.0. Product Language: [Current Language ID]. Manufacturer: Oracle Corporation. Removal success or error status: 0.

Globalization Support

Oracle Database XE is configured by default to be able to process character data in all supported languages simultaneously:

  1. The database is created with the Unicode AL32UTF8 character set. AL32UTF8 is the recommended database character set suitable for storing data in practically any language. Multiple languages can be mixed even in a single character value. While not a recommended option, you can create the database with another supported database character set by calling setup.exe from the Command Prompt as follows:
    setup.exe /v"CHAR_SET=\"
    where \ is the desired database character set. Supported database character sets are listed in tables A-4 and A-6 in Appendix A of the Database Globalization Support Guide. Character sets from Table A-4 are preferred over character sets from Table A-6 because of more comprehensive character repertoires.

  2. The Oracle Database Express Edition (XE) supports the same globalization features that Oracle Database Enterprise Edition (EE) provides.

13.1 Setting Language and Locale Preferences for Client

Connections
Configure client applications connecting to an Oracle Database according to your locale preferences and your I/O device character set. You must configure client applications connecting to an Oracle Database according to your locale preferences and your I/O device character set. If your applications do not have their own specific methods to configure locale preferences, then the method you use to configure an Oracle database client connection depends on the access API you use to connect to the database. Check your application documentation, before you configure locale preferences for your applications.

For applications that connect to Oracle Databases using Oracle Call Interface (OCI) use NLSLANG and other client settings with names that start with NLS\ to set the locale conventions and client character set for Oracle Database sessions. It is important that you set the character set part of the NLS_LANG value properly. The character set you set must correspond to the character set used by your I/O devices, which in case of Microsoft Windows is either the ANSI Code Page (for GUI applications), such as WE8MSWIN1252, or the OEM Code Page (for Console mode applications), such as US8PC437. By doing this, the OCI API is notified about the character set of data that it receives from the application. OCI can then convert this data correctly to and from the database character set.

NLS_LANG and the other NLS settings can be specified either as environment variables or as Windows Registry settings. Environment variable values take precedence over Registry values. Oracle Universal Installer sets a default value for the NLS_LANG setting in Registry when it creates a new Oracle home. The NLS_LANG value is based on the language of the Windows user interface, which is the language of Windows menu items and dialog box labels.
Caution:
Failure to set the client character set correctly can cause data loss. Java applications that connect to Oracle Databases by using Oracle JDBC do not use NLS_LANG. Instead, Oracle JDBC maps the default locale of the Java VM in which the application runs to the Oracle Database language and territory settings. Oracle JDBC then configures the connected database session using these settings. Because Java works internally in Unicode, the client character set is always set to Unicode.

Unless an application explicitly changes it, the default locale of the Java VM is set based on the locale of the user operating system on which the Java VM runs. Check your Java VM documentation for information about configuring the Java VM default locale.
Note:
In 3-tier architecture deployments, application servers that are database clients can have settings in their configuration files that specify the NLS_LANG value or the Java VM locale. Check the documentation accompanying these servers.

Related Topics
Oracle Database Administrator’s Reference for Microsoft Windows
See Also:
Oracle Database Globalization Support Guide for more information about configuring user locale preferences

users.sql

 prompt **********************************************************
prompt J:\app\oraclexe\product\18.0.0\dbhomeXE\bin\users.sql SAYS
prompt **********************************************************
-- if SQL*Plus writes trailing spaces when spooling data to a file
SET LINESIZE 254 trimspool on arraysize 100 tab off

COLUMN PARAMETER HEADING PARAMETER FORMAT A30
COLUMN value HEADING value FORMAT A100

COLUMN USERNAME HEADING USERNAME FORMAT A20
COLUMN COMP_NAME HEADING COMP_NAME FORMAT A30
COLUMN FILE_NAME HEADING FILE_NAME FORMAT A70
COLUMN tablespace_name HEADING tablespace_name FORMAT A15

prompt ******** 1. nls_database_parameters :
prompt select * from nls_database_parameters ;

prompt ******** 2. francuski e - all ok if this script is in npp "encoding UTF-8" :
prompt select 'š đ č ć ž é' non_eng_letters, ascii('é') from dual ;
select 'š đ č ć ž é' non_eng_letters, ascii('é') from dual ;

prompt ******** 3. nls_session_parameters :
prompt select PARAMETER, VALUE from nls_session_parameters ;

prompt ******** 4. nls_instance_parameters :
prompt select PARAMETER, VALUE from nls_instance_parameters ;

-- delete t_user where user_name != 'MERCEDES'  EE8PC852  US8PC437  EE8MSWIN1250
-- delete t_user where user_alias != 'ora7'

prompt 
prompt 
prompt ********* APEX: ******** 
prompt https://docs.oracle.com/cd/E59726_01/install.50/e39144/pre_require.htm#HTMIG376
prompt name and location of the server parameter file eg SPFILEXE.ORA
prompt or initialization parameter file: (initsid.ora) :

prompt SHOW PARAMETER PFILE  OUTPUTS :
prompt J:\APP\ORACLEXE\PRODUCT\18.0.0\DBHOMEXE\DATABASE\SPFILEXE.ORA
SHOW PARAMETER PFILE

prompt ***** DETERMINE CURRENT VALUES OF MEMORY_TARGET PARAM.: *****
prompt 0 = your db is using manual memory management - Consult Ora DB Admins Guide to learn how to configure an equivalent memory size using MANUAL MEMORY MANAGEMENT,
prompt  instead of continuing with the steps that follow.
prompt If system is using SERVER PARAMETER FILE, set value of the MEMORY_TARGET initialization parameter to at least 300 MB so:
prompt ALTER SYSTEM SET MEMORY_TARGET='300M' SCOPE=spfile; then SHUTDOWN and STARTUP

prompt SHOW PARAMETER MEMORY_TARGET OUTPUTS :
prompt NAME                                 TYPE        VALUE
prompt memory_target                        big integer 0
SHOW PARAMETER MEMORY_TARGET

prompt 
prompt 
select default_tablespace, temporary_tablespace from dba_users where username like 'APEX%'
/

select tablespace_name, file_name
  , (maxbytes - bytes) / 1024/1024 as "Available Space MB"
  , autoextensible
from dba_data_files
--where tablespace_name in ('SYSAUX', 'SYSTEM')
/
prompt E N D  A P E X ********************************************
prompt 
prompt 

select comp_name, version, status from dba_registry where comp_id = 'XDB';

--NLS_LANGUAGE=american NLS_TERRITORY=america 
--NLS_CHARACTERSET=US8PC437 
alter session set 
  nls_numeric_characters=',.'
  nls_date_format='YYYY-MM-DD HH24:MI:SS'
/
-- ne ide: alter session set NLS_LANG=american_america.US8PC437
--          NLS_CHARACTERSET=US8PC437

--SELECT distinct PARAMETER FROM V$NLS_VALID_VALUES
-- CHARACTERSET SORT TERRITORY LANGUAGE
--SELECT PARAMETER, VALUE FROM V$NLS_VALID_VALUES where PARAMETER='CHARACTERSET' order by VALUE
-- EE8ISO8859P2 EE8MSWIN1250 EE8PC852 UTF8
select * from t_tip_doc
/
select a.USER_ID, a.CREATED
     ,  u.NAZIV, u.USER_NAME, u.USER_PASSWORD, u.USER_ALIAS
from all_users a, t_user u
where u.user_name = a.username(+)
order by u.user_name
/

select * from t_user order by user_name
/

prompt D:\>echo %NLS_LANG%  vrati AMERICAN_AMERICA.EE8MSWIN1250
prompt D:\>chcp  vrati Active code page: 852 = console page win CLI-a

prompt U nls_database_parameters: nlsdbcharset= AL32UTF8 ili EE8MSWIN1250 :
select value from nls_database_parameters where parameter ='NLS_CHARACTERSET'
/

prompt select banner from v$version where banner like 'Oracle Database%';
prompt OUTPUTS : Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
select banner from v$version where banner like 'Oracle Database%';

prompt  select instance_name, status, database_status from v$instance;
prompt OUTPUTS : 
prompt INSTANCE_NAME    STATUS       DATABASE_STATUS
prompt xe               OPEN         ACTIVE
select instance_name, status, database_status from v$instance;

select USERNAME, USER_ID, CREATED from all_users order by username
/
prompt After DB 18c install. 35 rows selected. :
prompt system/MYPSW has no APEX... users

prompt
prompt
prompt show con_name displays :
show con_name
prompt sho user displays :
sho user
prompt show pdbs displays :
show pdbs

prompt
prompt
prompt ALTER SESSION SET container = XEPDB1;   show con_id or SELECT name, con_id FROM v$pdbs;
prompt ALTER SESSION SET container = CDB$ROOT; (PDB$SEED=2)   SELECT name Service_Name FROM v$active_services WHERE con_id =3;  
prompt
prompt




Exp / Imp Data between DB XE 11.2 and XE 18c

Exp / Imp Data for non-APEX Users

APEX was not used in your 11.2 XE database.

Exporting Data

  1. To export data from your 11.2 XE DB, perform the following steps:
    1. Create a directory dump_folder on local file system for the DUMP_DIR directory object
    2. Connect to 11.2 XE DB as user SYS using SYSDBA privilege
    3. Create directory object DUMP_DIR and grant READ and WRITE privileges on DUMP_DIR directory to SYSTEM user
      sqlplus / AS SYSDBA
      CREATE DIRECTORY DUMP_DIR AS 'C:\temp\dump';
      GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;
    4. To create a dump folder, run following command from Windows command prompt:
      mkdir C:\temp\dump
    5. Export data from your 11.2 XE database to dump folder
      expdp system/system_password full=Y EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\" directory=DUMP_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log
  2. Deinstall Oracle Database XE 11.2 if installation of 18c XE is planned on the same system. See Deinstalling DB XE Software in DB XE Installation Guide 11g Release 2 (11.2) for Microsoft Windows.
  3. Install Oracle Database XE 18c.

Importing Data

  1. To import data in your 18c XE database, perform the following steps:
    1. Connect to the 18c XE database as user SYS using the SYSDBA privilege.
    2. Create directory object DUMP_DIR and grant READ and WRITE privileges on the directory to the SYSTEM user.
      sqlplus / AS SYSDBA
      ALTER SESSION SET CONTAINER=XEPDB1;
      CREATE DIRECTORY DUMP_DIR AS '\dump_folder';
      GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;
    3. Import data to the 18c XE database from the dump folder.
      impdp system/system_password@localhost:listnerport/xepdb1 full=Y EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\"  directory=DUMP_DIR  dumpfile=DB11G.dmp logfile=impdpDB11G.log  

      You can ignore the following errors:

    4. ORA-39083: Object type TABLESPACE:"SYSAUX" failed to create with error
    5. ORA-31685: Object type USER:"SYS" failed due to insufficient privileges
    6. ORA-39083: Object type PROCACT_SYSTEM failed to create with error
    7. ORA-01917: user or role 'APEX_040000' does not exist
    8. ORA-31684 "already exists" errors

Import data to 18c XE database from dump folder

sqlplus / AS SYSDBA
ALTER SESSION SET CONTAINER=XEPDB1;
CREATE DIRECTORY DUMP_DIR AS '\dump_folder';
GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO SYSTEM;

impdp system/systempassword@localhost:listnerport/xepdb1 full=Y EXCLUDE=SCHEMA:\"LIKE \'APEX%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\" directory=DUMP_DIR dumpfile=DB11G.dmp logfile=impdpDB11G.log

Migrating Data and Applications You can migrate data and applications in and out of your XE database using various methods. You can export tables and data from one XE installation and import them into another. Oracle recommends you use 11g Release 2 and higher to export your data. Migrating from Oracle Database XE 18c to Oracle Database Enterprise Edition 18c is supported via the unplug, plug scenario only. You can unplug a PDB from XE 18c database and plug it into another Enterpise Edition 18c database of the same release. Migrating from Oracle Database XE 18c to any of the other Oracle Database 18c offerings is not supported. Upgrade from Oracle Database XE 11.2 to Oracle Database XE 18c is not supported. Procedure to Unplug a PDB from 18c XE and Plug into 18c EE A Pluggable Database (PDB) created in Oracle Database XE 18c can be migrated to another Multitenant Container Database (CDB) Enterprise Edition (EE) 18c. The standard plug compatibility requirements apply. See Oracle Multitenant Administrator’s Guide for more information about general prerequisites for PDB creation. For example, you want to unplug your xepdb1 out of the XE 18c XE CDB and plug it in another 18c EE CDB. • Use SQL*Plus to close the PDBs before they can be unplugged. Note: The pdb database may not have been opened, so you may receive an error that the PDB is already closed. sqlplus / as sysdba ALTER PLUGGABLE DATABASE xepdb1 CLOSE IMMEDIATE; • Unplug the closed PDB and specify the path and name of the XML file. ALTER PLUGGABLE DATABASE xepdb1 UNPLUG INTO '\tmp\xepdb1.xml'; • Drop the closed PDB and keep the data files. DROP PLUGGABLE DATABASE xepdb1 KEEP DATAFILES; • Log in to your EE environment. The xepdb1 metadata file should be available from that system. • Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether the unplugged PDB is compatible with the CDB. SET SERVEROUTPUT ON DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '\tmp\xepdb1.xml', pdb_name => 'XEPDB1') WHEN TRUE THEN 'YES' ELSE 'NO' END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END; / • If the PDB is compatible with the CDB, you can plug xepdb1 into the 18c EE database. Use the data files of the unplugged PDB to plug the PDB into another CDB without any copy. CREATE PLUGGABLE DATABASE newxepdb USING '\tmp\xepdb1.xml' NOCOPY TEMPFILE REUSE; • Verify the status and open mode of the plugged PDB. SELECT pdb_name, STATUS FROM cdb_pdbs WHERE pdb_name='NEWXEPDB'; SELECT OPEN_MODE FROM V$PDBS WHERE NAME='NEWXEPDB'; • If the PDB is in MOUNTED state, then open the PDB. ALTER PLUGGABLE DATABASE newxepdb OPEN; • This example assumes: – The XML file accurately describes the current locations of the files. Therefore, the SOURCE_FILE_NAME_CONVERT clause or SOURCE_FILE_DIRECTORY clause is not required. – The files are in the correct location. Therefore, NOCOPY is included. – Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required. – A file with the same name as the temp file specified in the XML file exists in the target location. Therefore, the TEMPFILE REUSE clause is required. • Run catalog, catproc and utrlp on the plugged in NEWXEPDB. sqlplus / as sysdba SQL> SHOW PDBS; CON_ID CON_NAME OPEN MODE RESTRICTED


2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 NEWXEPDB READ WRITE YES SQL> ALTER SESSION SET CONTAINER=newxepdb; SQL> SPOOL catalog.log SQL> @%ORACLE_HOME%\rdbms\admin\catalog SQL> SPOOL OFF; SQL> SPOOL catproc.log SQL> @%ORACLE_HOME%\rdbms\admin\catproc SQL> SPOOL OFF; SQL> SPOOL utlrp.log SQL> @%ORACLE_HOME%\rdbms\admin\utlrp SQL> SPOOL OFF; SELECT COMP_NAME, STATUS, VERSION FROM DBA_REGISTRY; COMP_NAME STATUS VERSION


Oracle Database Catalog Views VALID 18.0.0.0.0 Oracle Database Packages and Types VALID 18.0.0.0.0 Oracle Real Application Clusters OPTION OFF 18.0.0.0.0 JServer JAVA Virtual Machine VALID 18.0.0.0.0 Oracle XDK VALID 18.0.0.0.0 Oracle Database Java Packages VALID 18.0.0.0.0 OLAP Analytic Workspace VALID 18.0.0.0.0 Oracle XML Database VALID 18.0.0.0.0 Oracle Workspace Manager VALID 18.0.0.0.0 Oracle Text VALID 18.0.0.0.0 Oracle Multimedia VALID 18.0.0.0.0 Spatial VALID 18.0.0.0.0 Oracle OLAP API VALID 18.0.0.0.0 Oracle Label Security VALID 18.0.0.0.0 Oracle Database Vault VALID 18.0.0.0.0 SQL> SELECT OWNER, OBJECT_NAME FROM DBA_INVALID_OBJECTS WHERE STATUS = 'INVALID'; no rows selected Note: • See Plugging an Unplugged Pluggable Database for more information about plugging in an unplugged pluggable database. • See Oracle Multitenant Administrator’s Guide for more information about plugging in an unplugged PDB.



Export (expdp) vs RMAN

both backup tables, and they both support flashback database, but there are some important differences:

Data Pump Export (expdp) - The export utility is a "logical" backup, usually done by specifying specific tables. If you fail to do a "consistent" export, or if you fail to include related tables and use RESTRICTED and CONSISTENT mode (with referential integrity constraints), you may not be able to recover properly. Export is often used as a supplement to RMAN, usually for the restore of specific tables.

Recovery manager (rman) - RMAN is designed for backup and recovery, a extension of the Enterprise Backup Utility (EBU). RMAN takes full, physical, consistent backups of your database files..

Advantages & disadvantages of Export:

  1. Free, and easy to use
  2. Very slow, compared to RMAN (examines every data block)
  3. Easy restore of a specific table
  4. Does not require ARCHIVELOG mode

Advantages & disadvantages of RMAN:

  1. RMAN has block-level media recovery
  2. Has a catalog for backup tracking and a report utility
  3. Fast - If you dedicate a backup device for each production disk, you can backup terabytes in the time it takes to backup any single disk.
  4. Does hot or cold backups
  5. Backups and restores can be done in parallel
  6. Allows incremental backups (block change tracking)
  7. Interfaces with media management systems (TMS)

Tutorial : https://support.dbagenesis.com/knowledge-base/oracle-data-pump-utility-expdp-impdp/
Video : https://www.youtube.com/watch?v=b0o4tb7P_xg or https://www.youtube.com/watch?v=rPqcnOZK984
Link to articles: https://support.dbagenesis.com eg https://support.dbagenesis.com/knowledge-base/install-oracle-linux-on-virtualbox/



expdp

1. create OS level directory

which will be used by Oracle for performing exports and imports :
# mkdir -p /u02/dp_exp_dir

Create directory inside DB or use existing DATA_PUMP_DIR (see below) :
SQL> create directory datapump as '/u02/dp_exp_dir';
create directory datapumpmy as 'J:\app\oraclexe\product\18.0.0\admin\xe\dpdumpmy\';

Grant permissions on directory :
SQL> grant read,write on directory datapumpmy to hr ;

View directory information :
desc dba_directories

SET LINESIZE 254 trimspool on arraysize 100 tab off
COLUMN PARAMETER HEADING PARAMETER FORMAT A30
COLUMN OWNER HEADING OWNER FORMAT A15
COLUMN DIRECTORY_NAME HEADING DIRECTORY_NAME FORMAT A30
COLUMN DIRECTORY_PATH HEADING DIRECTORY_PATH FORMAT A100
select substr(OWNER,1,30) OWNER, substr(DIRECTORY_NAME,1,30) DIRECTORY_NAME, substr(DIRECTORY_PATH,1,100) DIRECTORY_PATH, ORIGIN_CON_ID from dba_directories;

OWNER            DIRECTORY_NAME                 DIRECTORY_PATH                                              ORIGIN_CON_ID
---------------- ------------------------------ ----------------------------------------------------------- -------------
SYS                ORA_DBMS_FCP_LOGDIR          J:\app\oraclexe\product\18.0.0\dbhomeXE\cfgtoollogs         ALL ARE = 1
SYS                ORA_DBMS_FCP_ADMINDIR        J:\app\oraclexe\product\18.0.0\dbhomeXE\rdbms\admin
SYS                SDO_DIR_WORK
SYS                SDO_DIR_ADMIN                c:\ade\aime_v\oracle/md/admin
SYS                ORACLECLRDIR                 J:\app\oraclexe\product\18.0.0\dbhomeXE\bin\clr
SYS                XMLDIR                       J:\app\oraclexe\product\18.0.0\dbhomeXE\rdbms\xml
SYS                XSDDIR                       J:\app\oraclexe\product\18.0.0\dbhomeXE\rdbms\xml\schema
SYS                ORACLE_OCM_CONFIG_DIR2       J:\app\oraclexe\product\18.0.0\dbhomeXE\ccr\state
SYS                ORACLE_OCM_CONFIG_DIR        J:\app\oraclexe\product\18.0.0\dbhomeXE\ccr\state
SYS                OPATCH_INST_DIR              J:\app\oraclexe\product\18.0.0\dbhomeXE\OPatch
SYS                **DATA_PUMP_DIR**            J:\app\oraclexe\product\18.0.0\admin\xe\dpdump\ 
SYS                OPATCH_SCRIPT_DIR            J:\app\oraclexe\product\18.0.0\dbhomeXE\QOpatch  
SYS                OPATCH_LOG_DIR               J:\app\oraclexe\product\18.0.0\dbhomeXE\rdbms\log 
SYS                ORACLE_BASE                  J:\app\oraclexe\product\18.0.0   
SYS                ORACLE_HOME                  J:\app\oraclexe\product\18.0.0\dbhomeXE
15 rows selected.


2. Perform Data Pump Export

In case you want to know all the options available with expdp utility, you can use below command
$ expdp help=y

To take DB level export
$ expdp directory=datapump dumpfile=fullprod.dmp logfile=fullprod.log full=y

To take schema level export

$ expdp directory=datapump dumpfile=scott_bkp.dmp logfile=scott_bkp.log schemas='SCOTT'

To take table level export
$ expdp directory=datapump dumpfile=emp_bkp.dmp logfile=emp_bkp.log tables='SCOTT.EMP'

To take row level export
$ expdp directory=datapump dumpfile=emprows_bkp.dmp logfile=emprows_bkp.log tables='SCOTT.EMP' query=\"where deptno=10\"


3. Performing Data Pump Import

In case you want to know all the options available with impdp utility, you can use below command
$ impdp help=y

To import full database
$ impdp directory=datapump dumpfile=fullprod.dmp logfile=imp_fullprod.log full=y

To import source schema objects into same schema name
$ impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema='SCOTT:SCOTT'

To import source schema objects into a different schema

$ impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema='HR:HRDPIMP_TEST'

To import a table
$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:SCOTT'

To import a table to another user
$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:SYSTEM'

To import tables to another tablespace (only in datapump)
$ impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:SCOTT' remap_tablespace='MYDATA:MYTBS'

https://support.dbagenesis.com/knowledge-base/oracle-expdp-par-file-example/ A sample data pump export and import :

Data Pump schema level Export:
====================
expdp directory=datapump dumpfile=scott_bkp.dmp logfile=scott_bkp.log schemas='SCOTT'

Data Pump Import:
=================
impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema='SCOTT:SCOTT'

In real-time, you might perform very complex export and import and frequently - daily or weekly. Data Pump jobs can be automated using PAR file :

cat dept_exp.par

Username=hr/hr tables=hr.emp directory=EXP_DIR dumpfile=QUERY_EXP.dmp logfile=QUERY_EXP.log

To call the above export PAR file :

expdp parfile=exp.par




2. INST / DEINST apex_20.1_en.zip ON DB 18c

Top.....Instalac 18c XE.....Instalac APEX 20.1.....Conv. F6i to APEX (Devsuite10g on WinXP)

Requires release 20.1 requires an Oracle Database release 11.2.0.4, Oracle REST Data Services (ORDS) 19.x or later.
NOT ON : Win 10 64 bit Oracle DB 11g XE Release 11.2.0.2.0 - 64bit Production !!!

apex_20.1_en.zip from http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

tnsping xepdb1 or xe

PS J:\app\oraclexe\product\18.0.0\dbhomeXE\bin> tnsping xe
TNS Ping Utility for 64-bit Windows: Version 18.0.0.0.0 - Production on 06-SEP-2020 22:36:18
Copyright (c) 1997, 2018, Oracle.  All rights reserved.
Used parameter files:
J:\app\oraclexe\product\18.0.0\dbhomeXE\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sspc2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)

Find out in which container APEX is currently installed into:
select r.COMP_NAME, r.VERSION, c.NAME, c.CON_ID from CDB_REGISTRY r, V$CONTAINERS c where r.CON_ID=c.CON_ID and r.COMP_ID='APEX' order by CON_ID;
no rows selected

select COMP_ID, VERSION, STATUS from CDB_REGISTRY where COMP_ID='APEX' order by CON_ID;
no rows selected

on Windows 10 pro 64 bit Ver : 10.0.19041 DB 18c
BY VINISH KAPOOR ON APRIL 3, 2019 on Windows and Unix/Linux systems :
https://www.foxinfotech.in/2020/04/installing-oracle-apex-20-1.html
BY VINISH KAPOOR ON APRIL 26, 2020 :
https://www.foxinfotech.in/2019/04/install-oracle-apex-19-1-in-eleven-easy-steps.html

https://matthiashoys.wordpress.com/2019/12/12/installation-of-apex-19-2-on-oracle-18c-xe-express-edition/

  1. Run all Apex 20.1 installation scripts from J:\apex_instl\apex (F:\software\apex19\apex)

  2. cd J:\apex_instl\apex

  3. sqlplus / as sysdba or sqlplus sys/syspsw@xe as sysdba
    @apexins.sql SYSAUX SYSAUX TEMP /i/

  4. Apex run time environment settings :
    @apxrtins.sql SYSAUX SYSAUX TEMP /i/

  5. change password for ADMIN user eg psw=Apex#2020
    @apxchpwd.sql

  6. copy Apex images to apex/images folder

    @apex_epg_config.sql J:\apex_instl\  (F:\software\apex19\)  Do not include apex folder in this path.
    grep ORA- *.log
    grep PLS- *.log
  7. Unlock APEX_PUBLIC_USER account and specify password.
    ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
    ALTER USER APEX_PUBLIC_USER IDENTIFIED BY MYPSW;

    Neded for http://localhost:8181/apex/apex_admin :

    select account_status from dba_users where username = 'ANONYMOUS';
    ACCOUNT_STATUS
    --------------------------------
    EXPIRED & LOCKED

    alter user ANONYMOUS identified by anonymous;
    alter user ANONYMOUS account unlock;

  8. Configure Apex RESTful Services as follows:
    @apex_rest_config.sql

  9. Set HTTP port if using HTTP server as follows:
    EXEC DBMS_XDB.SETHTTPPORT(8181);

  10. Enabling Network Services for Oracle Database 12c and Later

    Begin
        DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
            host => '*',
            ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                               principal_name => 'APEX_200100',
                               principal_type => xs_acl.ptype_db));
    End;
    /
    

http://localhost:8181/apex/apex_admin

set linesize 200
COLUMN USERNAME HEADING USERNAME FORMAT A40
COLUMN USER_ID HEADING USER_ID FORMAT A20
COLUMN DEFAULT_COLLATION HEADING DEFAULT_COLLATION FORMAT A20
SELECT * FROM ALL_USERS WHERE USERNAME LIKE 'APEX%';

USERNAME                                    USER_ID CREATED   COM O INH DEFAULT_COLLATION    IMP ALL
---------------------------------------- ---------- --------- --- - --- -------------------- --- ---
APEX_200100                              ########## 07-SEP-20 YES Y NO  USING_NLS_COMP       NO  NO
APEX_INSTANCE_ADMIN_USER                 ########## 07-SEP-20 YES Y NO  USING_NLS_COMP       NO  NO
APEX_PUBLIC_USER                         ########## 07-SEP-20 YES Y NO  USING_NLS_COMP       NO  NO

When Oracle Application Express installs, it creates three new database accounts:

  1. APEX_180100 - The account that owns the Oracle Application Express schema and metadata.
  2. FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.
  3. APEX_PUBLIC_USER - The minimally privileged account is used for Oracle Application Express configuration with Oracle REST Data Services or Oracle HTTP Server and mod_plsql.

If you configured RESTful Web services, then these additional accounts will be created:

  1. APEX_REST_PUBLIC_USER - The account used when invoking RESTful Services definitions stored in Oracle Application Express.
  2. APEX_LISTENER - The account used to query RESTful Services definitions stored in Oracle Application Express.

DB 18c REST quick start

Step 1: Install cURL that supports SSL

The examples used in this document use the cURL command-line tool to demonstrate how to access the Oracle Database REST API. To connect securely to the REST server, you must install a version of cURL that supports SSL. CURL is a command-line tool and library for transferring data with URLs.

https://www.thewindowsclub.com/how-to-install-curl-on-windows-10
curl --help shows Win 10 Curl : 2020.09.19 curl-7.72.0_4-win64-mingw.zip

C:\WINDOWS\system32>curl --version
curl 7.55.1 (Windows) libcurl/7.55.1 WinSSL
Release-Date: 2017-11-14, security patched: 2019-11-05
"J:\symenu\ProgramFiles\SPSSuite\SyMenuSuite\Cmder_sps\vendor\git-for-windows\mingw64\bin\curl.exe" --version --version
curl 7.71.1 (x86_64-w64-mingw32) libcurl/7.71.1 OpenSSL/1.1.1g (Schannel) zlib/1.2.11 brotli/1.0.7 libidn2/2.3.0 libssh2/1.9.0 nghttp2/1.41.0
Release-Date: 2020-07-01

or :
Git for Windows will install CURL along with it. Add it to your Windows path, and you will be able to execute it from anywhere. Press start button, and type "system var".

or :
To install cURL on your system:

In your browser, navigate to the cURL home page at https://curl.haxx.se/windows/ -> curl for 64 bit
https://curl.haxx.se/windows/dl-7.72.0_4/curl-7.72.0_4-win64-mingw.zip, built and statically linked with :

  1. OpenSSL 1.1.1g [64bit/32bit]
  2. brotli 1.0.9 [64bit/32bit]
  3. libssh2 1.9.0 [64bit/32bit]
  4. nghttp2 1.41.0 [64bit/32bit]
  5. zlib 1.2.11 [64bit/32bit]
  6. zstd 1.4.5 [64bit/32bit]

When running cURL from a Windows command shell only, you must provide an SSL certificate authority (CA) file or bundle to authenticate against the Verisign CS certificate.

Navigate to the cURL CA Extract page at http://curl.haxx.se/docs/caextract.html and download the cacert.pem SSL CA certificate file in the folder where you installed cURL.

Open a command window, navigate to directory where you installed cURL, and set the cURL environment variable, CURL_CA_BUNDLE, to the location of the SSL CA certificate file. For example, on a Windows system you would enter:

C:\curl> set CURL_CA_BUNDLE=cacert.pem

You are now ready to send REST requests to the Database REST API instance using cURL. See Authentication, and Use cURL.

J:\symenu\z_aplpsy\curl\bin

To be able to create workspace in PDB you need configure "Oracle REST Data services" for each PDB you want to use.

Based on "Oracle REST data Services Installation and Configuration Guide (E25066-10)" chapter 2.1 "Configuring Multiple Databases" you should: issue commands from your apex 4.2.x directory:

java -jar ords.war setup --database

Note: For provide PDB service_name or SID, not CDB!

java -jar ords.war map-url --type base-patch --workspace-id

Make sure you restart "Oracle REST Data Services".

Access Apex Administration Services - please use newly configured link:

http:///ords//apex_admin

Enter credentials you used previously during "Configuring Multiple Databases" for PDB apex access. Make note that is just alias, the workspace does not exists yet, but after login you are allowed to create workspace(s) in PDB finally. Additional workspaces in the same PDB can be accessed via link displayed above, so you need configure "Oracle RESTData Listener" for each PDB just once ideally.

This worked for me, hopefully it works for you too.

APEX data is stored in pluggable database, while default connection in SQL Plus is to root container cdb$root.

There is APEX_200100 listed (and you use it). Then:

ALTER SESSION SET CURRENT_SCHEMA = APEX_200100 ;  
alter session set container = XEPDB1;
BEGIN  
    APEX_INSTANCE_ADMIN.UNRESTRICT_SCHEMA(P_SCHEMA => 'HR_PDB1');  
    COMMIT;  
END;  
/ 

Now check whether you can assign the HR_PDB1 schema to your workspace.

BEGIN
   APEX_INSTANCE_ADMIN.ADD_SCHEMA('hr','HR');  //hr=MY_WORKSPACE, HR=shema
END;
/




11111 Old APEX for deinstall

In J:\apex_instl or F:\software\axepdb1pex19 or c\:\temp\apex :
ls shows files and 4 dirs : builder/ images/ core/ utilities/
On Unix/Linux: cd /home/usr1/apex19/apex

sqlplus.exe - avoid eg old sql+ from 2008. 11.1.0.7.0 !!! :

cd C:\oraclexe\app\oracle\product\18.1\server\bin\sqlplus.exe /nolog   
conn sys/MYPSW as sysdba    or  conn sys/MYPSW@XE as SYSDBA  
          SQL*Plus: Release 11.2.0.2.0 Production on Pet Kol 21 11:48:00 2020
          Copyright (c) 1982, 2014, Oracle.  All rights reserved.
--outputs no rows selected :
SELECT username  FROM dba_users where username LIKE 'FLOWS\_______' ESCAPE  '\' 
--outputs APEX_040000 : 
SELECT username  FROM dba_users where username LIKE 'APEX\_______' ESCAPE '\'
              --outputs FLOWS_FILES :
              SELECT username  FROM dba_users where username LIKE 'FLOWS%' 
--outputs no rows selected :
--If the results contain entries in the form  FLOWS_XXXXXX  or  APEX_XXXXXX  where  XXXXXX represents six numbers, those entries are candidates for removal
SELECT username  FROM dba_users
WHERE (
       username LIKE 'FLOWS\_______' ESCAPE  '\' 
       OR username LIKE 'APEX\_______' ESCAPE '\'
  )
  -- **outputs APEX_040000** :
  AND username NOT IN ( SELECT schema FROM dba_registry WHERE comp_id = 'APEX' )
/

-- outputs eg APEX_200100

22222 Deinstall old APEX users

DROP USER APEX_040000 CASCADE;
DROP USER APEX_200100 CASCADE;

33333 Instalac Apex 20.1 on Windows 10 pro 64 bit Ver : 10.0.19041

Run all Apex 20.1 installation scripts from J:\apex_instl\apex (F:\software\apex19\apex)  
-- apex dir is from J:\apex_instl\1_apex_20.1_en.zip !!!

cd J:\apex_instl\apex  
C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe /nolog  
conn sys/MYPSW as sysdba    or  conn sys/MYPSW@XE as SYSDBA  

-- 6 minutes or less :  
@apexins.sql SYSAUX SYSAUX TEMP /i/

--connect to DB again because apexins.sql disconnected you from DB :  
C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe /nolog  
conn sys/MYPSW as sysdba    or  conn sys/MYPSW@XE as SYSDBA  

-- 8080 :  
SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;  

--run apxrtins.sql for Apex run time environment settings :  
@apxrtins.sql SYSAUX SYSAUX TEMP /i/  

                --Why above command Generates Error  (apex_20.1_en.zip, Win 10 64 bit)  ? :
                FAIL - Precondition for Phase 1 failed: APEX_200100 already exists
                . 2 errors occurred
                declare
                *
                ERROR at line 1:
                ORA-20001: Prerequisite checks failed; unable to proceed with APEX installation
                ORA-06512: at line 288
                Disconnected from Oracle Database 11g XE Release 11.2.0.2.0 - 64bit Production

44444 Change password for ADMIN user (cre ADMIN if not exists)

cd C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe /nolog  
conn sys/MYPSW as sysdba    or  conn sys/MYPSW@XE as SYSDBA
@apxchpwd.sql
           ADMIN usr created    slavkoss22@gmail.com  psw=Apex#201

--Created instance administrator ADMIN

55555 Copy Apex images to apex/images folder and postinstal steps

-- in J:\apex_instl\apex folder (same as previous steps !!) :  
C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe /nolog  
conn sys/MYPSW as sysdba    or  conn sys/MYPSW@XE as SYSDBA

--Note: As I copied apex_20.1_en.zip to J:\apex_instl (or F:\software\apex19 or C:\TEMP) folder, so I am specifying the same directory here for apex_epg_config.sql script. Do not include apex folder in this path !!
@apex_epg_config.sql J:\apex_instl\
-- 218 MB !  229.458.985 B :
--outputs : . Loading images directory: J:\apex_instl\/apex/images
--          timing for: Load Images Elapsed: 00:04:53.42

--Unlock APEX_PUBLIC_USER account and specify the password
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY MYPSW;
--? ALTER USER ANONYMOUS ACCOUNT UNLOCK;

--8080 :
SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
  1. Configure Apex RESTful Services as follows:
    -- All MYPSW :
    @apex_rest_config.sql

  2. Set HTTP port if using HTTP server as follows:
    EXEC DBMS_XDB.SETHTTPPORT(8181);

  3. Configure network ACL for Oracle Apex 20.1 as follows:

    Enabling Network Services for Oracle Database 12c and Later:

    /* First connect as the SYS user with SYSDBA role */ 
    sqlplus sys/syspsw@orcl as sysdba
    /* Then execute the below script */
    Begin
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_200100',
                           principal_type => xs_acl.ptype_db));
    End;
    /

Enabling Network Services for Oracle Database prior to 12c:

in J:\apex_instl\apex folder (same as previous steps !!) :

C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe /nolog
conn sys/MYPSW as sysdba    or  conn sys/MYPSW@XE as SYSDBA

Declare
    acl_path Varchar2(4000);
Begin
  -- Look for the ACL currently assigned to '*' and give APEX_200100
  -- the "connect" privilege if APEX_200100 does not have the privilege yet.
    Select
        acl
    Into acl_path
    From
        dba_network_acls
    Where
        host = '*'
        And lower_port Is Null
        And upper_port Is Null;
    If dbms_network_acl_admin.check_privilege(acl_path, 'APEX_200100', 'connect') Is Null Then
        dbms_network_acl_admin.add_privilege(acl_path, 'APEX_200100', true, 'connect');
    End If;
Exception
  -- When no ACL has been assigned to '*'.
    When no_data_found Then
        dbms_network_acl_admin.create_acl('power_users.xml', 'ACL that lets power users to connect to everywhere', 'APEX_200100',
        true, 'connect');
        dbms_network_acl_admin.assign_acl('power_users.xml', '*');
End;
/

--PL/SQL procedure successfully completed.
Commit;

--Now your installation and configuration are complete for Oracle Apex 20.1. You can open the Oracle Apex in a browser using the following URL:

http://localhost:8181/apex/apex_admin

ADMIN usr created slavkoss22@gmail.com psw=Apex#201

Before you get started, please take a moment to create a workspace. A workspace is a shared work area where multiple developers can build applications.

Once created, sign in to your workspace to begin building applications. Return to Administration Services to create additional workspaces or to manage this Application Express instance.

POSSYS
reuse exsisting shema MERCEDES
shema ADMIN/MYPSW

-- Workspace POSSYS provisioned with administrator ADMIN. (also INTERNAL WS)

App develop. : http://localhost:8181/apex/ or http://localhost:8181/apex/f?p=4550:1
A d m i n : http://localhost:8181/apex/apex_admin or http://localhost:8181/apex/f?p=4050:3
or http://127.0.0.1:8080/apex/apex_admin
WS cre. : http://localhost:8181/apex/f?p=4050:10
-- adds : :13967656906380:::::

Cloud :
POSSYS app : https://apex.oracle.com/pls/apex/possys/r/sales-web-app4/login
POSSYS app develop. : https://apex.oracle.com/pls/apex/f?p=4550:1




3. Convert Oracle Forms 6i to APEX using frmf2xml utility

Top.....Instalac 18c XE.....Instalac APEX 20.1.....Conv. F6i to APEX (Devsuite10g on WinXP)

App f6i2apex in own WS wsconvf6 (or both POSSYS)

to convert Oracle Forms 6i (9 or ...) to APEX app modules

  1. Convert fmb 6i to fmb 10

  2. Convert Oracle Forms to XML.

    Run Forms to XML Conversion tool, Forms2XML, to convert Forms modules in your app. This creates XML output files.
    C:\OraDS10Home1\BIN\frmf2xml.bat : frmf2xml.bat [options] file1 [file2...]
    In WinXP (Ora Virt. Box) D:\OraDS10Home1\BIN

    C:\OraDS10Home1\BIN\frmf2xml.bat L:\asg\possys6\tipdok.fmb  
               Oracle Forms 10.1.2 Forms to XML Tool  
               Copyright(c) 2001, 2005, Oracle.  All rights reserved.  
               Processing module C:\OraDS10Home1\forms\test.fmb  
    XML Module saved as L:\asg\possys6\tipdok_fmb.xml  

    or C:\OraDS10Home1\BIN\frmf2xml.bat L:\asg\hr\poplist_dept2.fmb
    XML Module saved as L:\asg\hr\poplist_dept2_fmb.xml

  3. Create an APEX Workspace wsconvf6
    Associate workspace with Oracle Forms application schema.

  4. Create migration project mercedes or hr and load app metadata XML

    Upload Forms Module XML File to APEX

    1. login to wsconvf6/admin/admin

    2. Click App Builder icon.

    3. Click Oracle Forms Migrations on the right side of the page

    4. On App Migrations page, Migr. Tasks region -> App Migrations , click Create Project. Enter project details:

      1. Project unique Name, no white spaces = mercedes or hr or...
      2. Description = mercedes user forms 6i converted to 10g migrated to APEX 20.1 (18.1)
      3. Schema = mercedes
        Default schema is schema associated with your WS. If multiple schemas (mercedes, hr, placa6) are associated with your WS, all associated schemas appear in select list, arranged in alphabetical order. When this situation exists, select schema associated with the Oracle Forms you want to upload.
      4. Forms Module XML File = L:\asg\possys6\tipdok.fmb which contains the information of your converted Oracle FormModule
    5. Click Next. Confirm page appears.

    To add more files, click Upload Another File.

  5. Analyze Oracle Forms app
    From App Migration Workshop, verify and adjust Forms app metadata. Identify business logic required in new APEX app. Refine scope of your conversion.

    File Name = 'tipdok_fmb.xml'

    Component Count Equivalent Component Implementation Details Included File Name Applicable
    1 Alerts 6 - alert message can be stored as Text Message in Shared Components of APEX app. Text Messages can be used to build translatable text strings with substitution variables that can be called from PL/SQL packages, procedures, and functions. - tipdok_fmb.xml yes

Alerts 6 - An alert message can be stored as a Text Message in the Shared Components of an Oracle Application Express application. Text Messages can be used to build translatable text strings with substitution variables that can be called from PL/SQL packages, procedures, and functions. - tipdok_fmb.xml Yes

  1. Generate APEX App
    Create a new app using Create Application Wizard, in App Builder part of APEX, to cover functionality being migrated.
  2. Customize your APEX App
    Open your app and customize your generated app. You can change app attributes or add new pages to app.



Not working if form contains image :
Processing module C:\OraDS10Home1\forms\test.fmb
ERROR - an exception has been encountered: _jni_gra_export_image failed



Links, help...

L:\3_sw_video\4_db\2_oracle\Complete_Oracle_SQL_Course_12c\000_12cX64_APEX_app_migration_guide.pdf   
L:\2_knjige\oracle\apex\8_instal_oracle_10XE_APEX32_devsuite101_MOJ.txt  
L:\2_knjige\oracle\apex\0_apex_dokum\AEMIG\overview-migration-process.htm  

L:\1_instalac\2_instalac_ora\z_ostalo\9_ASG_F6i_f11\1_f6i  
L:\1_instalac\2_instalac_ora\z_ostalo\2_oracleXE\11XE_win  
L:\1_instalac\5_inst\1_instalac_old\0instal\1_moj_WinPostInst\z_mojeF6R6_ASGphp32bit\2_ORA_f10_DeveloperSuite10g  
C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Oracle Database 11g Express Edition
C:\oraclexe\app\oracle\product\11.2.0\server

https://www.foxinfotech.in/2017/03/convert-oracle-form-to-apex-using-frmf2xml-utility.html

APEX 20.1 : was 18.1

APEX forum : https://community.oracle.com/community/database/developer-tools/

http://www.oracle.com/technetwork/developer-tools/apex/application-express/podcasts-090206.html

https://www.youtube.com/watch?v=5kPxqEXM5UM

https://www.youtube.com/watch?v=gg6Gy1VtqmA

You must install Oracle Developer Suite (best on WinXP !!) to convert Oracle Forms applications and Oracle Reports to XML format.

Convert:

  1. Oracle Forms FormModule (. FMB ), ObjectLibrary (. OLB ), or MenuModule (. MMB ) files to XML format using the Forms to XML conversion tool, Forms2XML
  2. Oracle Reports (including, binary (. RDF ), ASCII (. REX ), and . JSP ) to XML format using the File Conversion option in Reports Builder
  3. PL/SQL library . PLL files to . PLD text files using the File Conversion option in Oracle Forms Builder

C:\OraDS10Home1\BIN\frmf2xml.bat

@ECHO OFF
REM
REM  DESCRIPTION
REM    This file is used to call the Forms2XML conversion tool.
REM    It takes .fmb, .mmb, and .olb files and converts them into XML.
REM 
REM  NOTES
REM    It wraps the class oracle.forms.util.xmltools.Forms2XML and passes
REM    any parameters given onto the tool.
REM    You can only use the standard nine parameters, but these can include
REM    wildcards in the filenames.
REM

REM Setup path to include necessary Forms dlls.
set PATH=C:\OraDS10Home1\bin;%PATH%

REM Run the tool with the required jar files added to the classpath
C:\OraDS10Home1\jdk\bin\java -classpath C:\OraDS10Home1\forms\java\frmxmltools.jar;C:\OraDS10Home1\forms\java\frmjdapi.jar;C:\OraDS10Home1\lib\xmlparserv2.jar;C:\OraDS10Home1\lib\xschema.jar oracle.forms.util.xmltools.Forms2XML %*





  1. Videos : https://apex.oracle.com/en/learn/videos/

https://www.tutorialspoint.com/plsql/plsql_dbms_output.htm
DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000); NULL=unlimited

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON
DECLARE
   lines dbms_output.chararr;
   num_lines number;
BEGIN
  DBMS_OUTPUT.DISABLE;
  DBMS_OUTPUT.ENABLE(NULL);

  num_lines := 0; 

  dbms_output.put_line  (user || ' Tables in the database:');  num_lines := num_lines + 1;
  FOR t IN (SELECT table_name FROM user_tables)
     LOOP dbms_output.put_line(t.table_name); num_lines := num_lines + 1; END LOOP;
  dbms_output.put_line('SELECT table_name FROM user_tables :'); num_lines := num_lines + 1;

  dbms_output.get_lines(lines, num_lines);

  FOR i IN 1..num_lines LOOP
     null; 
     --dbms_output.put_line(lines(i));
  END LOOP;
  --DBMS_OUTPUT.DISABLE;
END;
/ 
SET SERVEROUTPUT OFF

Top.....Instalac 18c XE.....Instalac APEX 20.1.....Conv. 6i to APEX (Devsuite10g on WinXP)