File: fwphp/glomodul/mkd/01/001_db/01_oracle_DB_18c_instalac.txt

Recommend this page to a friend!
  Classes of Slavko Srakocic  >  B12 PHP FW  >  fwphp/glomodul/mkd/01/001_db/01_oracle_DB_18c_instalac.txt  >  Download  
File: fwphp/glomodul/mkd/01/001_db/01_oracle_DB_18c_instalac.txt
Role: Documentation
Content type: text/plain
Description: Documentation
Class: B12 PHP FW
Manage database records with a PDO CRUD interface
Author: By
Last change: ver 7.0.1 mnu, msg, mkd FUNCTIONAL namespaces, CRUD PDO trait, pretty URL-s
Date: 1 month ago
Size: 89,811 bytes
 

Contents

Class file image Download
<a name="top"></a>2020.09.14  
***  
**[Top](#top)**.....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....[ Links](#links)  
***  
## 1. Oracle DB 18c instalac intro 
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;  

**On PC where 18c XE is installed (if listener port is default = 1521)**  
11g xe: Sqlplus hr/hr@xe  or ora7  
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. 


```

```

 # Exp / Imp Data between DB XE 11.2 and XE 18c
 ## Exp / Imp Data for non-APEX Users using data pump utilities
Eg if APEX was not used in your 11.2 XE DB.

>https://oracle-base.com/articles/10g/oracle-data-pump-10g  also valid for > 10g  
>Unlike the original exp and imp utilities all data pump ".dmp" and ".log" files are created on the Oracle server, not the client machine.  

>Data pump utils DB 20.1c expdp/impdp (~same since 10g) - Up to 40x faster than Exp/Imp Data. Very high-speed movement of data and metadata from one DB to another for a complete DB  or subsets of a DB.  In addition to basic import and export functionality **data pump provides a PL/SQL API** and support for external tables.  **Data pump like export** main purpose is **data transfer** not recovery, they are not backup tools because  :
>1. are not incremental
>2. lose data (everything since the last time you ran an export). 

### cd C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\bin  =  [oracle_home]\bin
**sqlplus "/ as sysdba"**  
>11.2.0.2.0  (last 11g XE)
```
select
  ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
  ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
  ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
  ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
  from  dual;  
```
>displays : Size in GB  1,81570435

select name, log_mode from v$database;          
>displays : **XE, NOARCHIVELOG**  

SELECT comp_id, version, status FROM dba_registry;  
```
COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ -----------
APEX                           4.0.2.00.09                    VALID
XDB                            11.2.0.2.0                     VALID
CONTEXT                        11.2.0.2.0                     VALID
CATALOG                        11.2.0.2.0                     VALID
CATPROC                        11.2.0.2.0                     VALID
```




<br /><br /><br /><a name="expdp_11gXE"></a>  
***  
[Top](#top).....**[expdp 11g XE](#expdp_11gXE)**......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....[ Links](#links)  
***  
# expdp - Exporting Data from 11g XE DB
Steps:

   1. As DBA Win user create directory  **J:\apl\expdp_db and share it with Everyone read/write**  
      on server (not on client system)  **for exp_schema DB directory object which is pointer to OS dir.**   

   2. **Create DB directory object  exp_schema** 
   ```
   sqlplus / AS SYSDBA
   CREATE DIRECTORY exp_schema AS 'J:\apl\expdp_db';  
   GRANT READ, WRITE ON DIRECTORY exp_schema TO HR; 
   GRANT DATAPUMP_EXP_FULL_DATABASE TO HR;
   ```
   >Why should we grant DATAPUMP_EXP_FULL_DATABASE role (and DATAPUMP_IMP_FULL_DATABASE) to user (eg hr or to SYSTEM) :
    >1. Specify a single schema other than your own or a **list of schema names**
   >2. Export additional non-schema object information for each specified schema so that schemas can be re-created at import time : user definitions themselves and all associated system and role grants, user password history...  
   >This means that the **users which dont have this role can only export own schema**. 
   >3. Export Schema (user) HR and MERCEDES from  11.2 XE database to dump folder  - **from Win CLI as admin** :

3. See below how to create forgoten file in forgoten dir xsl : C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml\xsl\kupostdt.xsl  
   forgoten file in not forgoten dir xsl : J:\app\ss\product\11.2.0\dbhome_1\rdbms\xml\xsl\kupostdt.xsl  (install 11g not xe...)  
   
4. From Win CLI as admin :  
   cd C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\bin  
   ### Works :  conn hr/hr@SSPC2/xe  
   ### expdp hr/hr@xe DIRECTORY= exp_schema DUMPFILE=exp_schm_hr.dmp LOGFILE=hr_lg.log SCHEMAS=hr
   expdp hr/hr@SSPC2/xe DIRECTORY= exp_schema DUMPFILE=exp_schm_mercedes.dmp LOGFILE=mercedes_lg.log SCHEMAS=mercedes
   or  
   expdp hr/hr@xe DIRECTORY=exp_schema  DUMPFILE=exp_schm_hr_mercedes.dmp LOGFILE=hr_mercedes_lg.log SCHEMAS=hr,mercedes  

      Last parameter is "SCHEMAS" - tells data pump that : we want to perform a schema export ee we want to **run data pump expdp utility in schema mode**. 
      
      This error was happened because i change character set to WE8MSWIN1252, but when i changed back into default XE : AL32UTF8, expdp can running well (https://willyristanto.blogspot.com/2015/01/error-expdp-ora-39213-ora-31609.html).
  
      Export: Release 11.2.0.2.0 - Production on Sub Ruj 12 13:09:05 2020    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  
      Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production  
      ORA-39006: internal error  
      **ORA-39213: Metadata processing is not available**  
      Connect AS SYSDBA and** exec dbms_metadata_util.load_stylesheets** to reload stylesheets.
      For more information please check **Oracle Doc ID 402242.1**
      ```
DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000);  NULL=unlimited
SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON
DECLARE
    cnst_ARE_STYLESHEETS_LOADED CONSTANT VARCHAR2(50) :=
       CASE dbms_metadata_util.ARE_STYLESHEETS_LOADED
       WHEN TRUE THEN 'STYLESHEETS LOADED'
       ELSE 'STYLESHEETS NOT LOADED'
       END;
BEGIN
   --DBMS_OUTPUT.PUT_LINE(dbms_metadata_util.ARE_STYLESHEETS_LOADED);
    DBMS_OUTPUT.PUT_LINE(cnst_ARE_STYLESHEETS_LOADED);
END;
/
SET SERVEROUTPUT OFF
   ```
   Displays :  
   META:13:51:05.324: **bad stylesheet: kucolumn**  
   STYLESHEETS NOT LOADED  
   PL/SQL procedure successfully completed.  

   **exec dbms_metadata_util.load_stylesheets**  
   Displays :  
   ERROR at line 1:  
   **ORA-31609: error loading file "kucolumn.xsl" from file system directory**  
   "C:\oraclexe\app\oracle\product\11.2.0\server/rdbms/xml/xsl"    **$ORACLE_HOME/rdbms/xml/xsl**
   ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2397  
   
   **J:\app\ss\product\11.2.0\dbhome_1\rdbms\xml\xsl**  copy to same XE dir  and **add one xsl more** :
```
<?xml version="1.0"?>
<!--
    NAME
       C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml\xsl\kupostdt.xsl
    DESCRIPTION
       XSLT stylesheet for XML => DDL conversion of ku$_post_data_table_t ADTs
    NOTES
    Do NOT modify this file under any circumstance. Copy the file
    if you wish to use this stylesheet with an external XML/XSL parser
    MODIFIED        MM/DD/YY
    sdavidso 06/08/09 - new stylesheet - for minimize records_per_bock
-->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    
    <!-- Import required scripts -->
    <xsl:import href="kucommon.xsl"/>
    
    <!-- Top-level parameters -->
    <xsl:param name="PRETTY">1</xsl:param>
    <xsl:param name="SQLTERMINATOR">1</xsl:param>
    
    <!-- params for parse -->
    <xsl:param name="PRS_DDL">0</xsl:param>
    <xsl:param name="PRS_DELIM">\{]`</xsl:param>
    <xsl:param name="PRS_VERB">0</xsl:param>
    <xsl:param name="PRS_OBJECT_TYPE">0</xsl:param>
    <xsl:param name="PRS_SCHEMA">0</xsl:param>
    <xsl:param name="PRS_NAME">0</xsl:param>
    <xsl:param name="PRS_GRANTEE">0</xsl:param>
    <xsl:param name="PRS_GRANTOR">0</xsl:param>
    <xsl:param name="PRS_BASE_OBJECT_TYPE">0</xsl:param>
    <xsl:param name="PRS_BASE_OBJECT_SCHEMA">0</xsl:param>
    <xsl:param name="PRS_BASE_OBJECT_NAME">0</xsl:param>
    
    <xsl:template match="POST_DATA_TABLE_T">
    
        <!-- *******************************************************************
        Template: POST_DATA_TABLE_T - template for table attributes
        which can only be set after table data is loaded.
        ******************************************************************** -->
        <xsl:call-template name="DoParse">
        <xsl:with-param name="Verb">ALTER</xsl:with-param>
        <xsl:with-param name="ObjectType">POST_DATA_TABLE</xsl:with-param>
        <xsl:with-param name="SchemaNode" select="SCHEMA_OBJ/OWNER_NAME"/>
        <xsl:with-param name="NameNode" select="SCHEMA_OBJ/NAME"/>
        </xsl:call-template>
        <xsl:if test="(SPARE1 mod 65536)>=32768">
            <xsl:text> ALTER TABLE </xsl:text>
            <xsl:apply-templates select="SCHEMA_OBJ"/>
            <xsl:text>MINIMIZE RECORDS_PER_BLOCK</xsl:text>
        </xsl:if>
    
    </xsl:template>
</xsl:stylesheet>
```
### SQL> exec dbms_metadata_util.load_stylesheets
PL/SQL procedure successfully completed.

PS C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\bin> expdp hr/hr@xe DIRECTORY= exp_schema DUMPFILE=exp_schm_hr.dmp LOGFILE=hr_lg.log SCHEMAS=hr  
ORA-39070: Unable to open the log file.  
If we are on a Windows machine, make sure that both listener and DB have been started with same username.  

```
declare
  fle utl_file.file_type;
begin
  fle := utl_file.fopen ('exp_schema', 'test.txt', 'w');
  utl_file.put_line(fle, 'test');
  utl_file.fclose(fle);
end;
/
```
ORA-29280: invalid directory path  
Oracle cant write to that directory at all, probably because of Windows file permissions. Check which Windows user(s) the Oracle services are running as, and change the folder permissions to allow them write access.

You can also disable the logfile completely by using NOLOGFILE=Y, but you'll have to monitor the impdp output on your console, because it won't get saved anywhere else. THEN err :

ORA-31641: unable to create dump file "J:\apl\expdp_db\exp_schm_hr.dmp"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.

See other possible err :  https://kmpdba.wordpress.com/2012/05/02/ora-39213-metadata-processing-is-not-available/
   

   There is a 2011 discussion about this at https://community.oracle.com/thread/2278841. It says you have to copy the directory  $ORACLE_HOME/rdbms/xml/xsl from a working installation. **No xsl dir under dml in my 11g XE dirs**, same 11g on Oracle linux  
   /u01/app/oracle/product/11.2.0/xe/rdbms/xml/.  

   /etc/hosts file looks like this :  
   127.0.0.1       localhost.localdomain localhost  
   ::1     localhost6.localdomain6 localhost6  


<br /><br /><br />

**Deinstall DB XE 11.2 if installation of 18c XE is planned on same system.** See Deinstalling DB XE Software in DB XE Installation Guide 11g Release 2 (11.2) for  Microsoft Windows.

**Install DB 18c XE**





<br /><br /><br /><a name="impdp_18cXE"></a>  
***  
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......**[impdp 18c XE](#impdp_18cXE)**.....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....[ Links](#links)  
***   
# Importing data to (18c) XE DB (or to 11g )
1. See **expdp - Exporting Data from 11g XE DB :** cre J:\apl\expdp_db (or \dump_folder) OS dir
2. forgoten file in forgoten dir xsl :       C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml\xsl\kupostdt.xsl  (**11g XE deinstalled to install 11c XE**)
   forgoten file in not forgoten dir xsl : J:\app\ss\product\11.2.0\dbhome_1\rdbms\xml\xsl\kupostdt.xsl    
3. 
   ```
sqlplus / AS SYSDBA
--not for 11g :
ALTER SESSION SET CONTAINER=XEPDB1; 
--
--Create dir. object  exp_schema  and grant  READ  and  WRITE  privileges on it to the  HR or SYSTEM  user.
CREATE DIRECTORY exp_schema AS 'J:\apl\expdp_db';
GRANT READ, WRITE ON DIRECTORY exp_schema TO HR;
GRANT DATAPUMP_EXP_FULL_DATABASE TO HR;
GRANT DATAPUMP_IMP_FULL_DATABASE TO HR;
   ```
4. Import data to 18c XE (or to 11g) database from dump folder :  
   cd J:\app\ss\product\11.2.0\dbhome_1\bin  
   #### impdp hr/hr@localhost:1522/orcl DIRECTORY=exp_schema DUMPFILE=exp_schm_mercedes.dmp LOGFILE=mercedes_imp_lg.log EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\"  
   impdp system/system_password@localhost:listnerport/xepdb1 **full=Y EXCLUDE=**SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\"  directory=exp_schema  dumpfile=DB11G.dmp logfile=impdpDB11G.log  
   was exported so :  
   expdp hr/hr@SSPC2/xe DIRECTORY= exp_schema DUMPFILE=exp_schm_mercedes.dmp LOGFILE=mercedes_lg.log SCHEMAS=mercedes
   
   " See **J:\apl\expdp_db\mercedes_imp_lg.log**  

5.    conn mercedes/m1@localhost:1522/orcl  

   You can ignore the following errors:
   1. ORA-39083: Object type TABLESPACE:"SYSAUX" failed to create with error
   2. ORA-31685: Object type USER:"SYS" failed due to insufficient privileges
   3. ORA-39083: Object type PROCACT_SYSTEM failed to create with error
   4. ORA-01917: user or role 'APEX_040000' does not exist
   5. ORA-31684 "already exists" errors



<br /><br /><br />
## Data pump imp/exp PL/SQL API
 select * from dba_datapump_jobs; --to monitor current jobs.
 
 Along with the data pump utilities Oracle provide an PL/SQL API. The following is an example of how this API can be used to perform a **schema export**.
```
DECLARE
  l_dp_handle       NUMBER;
BEGIN
  -- Open an schema export job.
  l_dp_handle := DBMS_DATAPUMP.open(
    operation   => 'EXPORT',
    job_mode    => 'SCHEMA',
    remote_link => NULL,
    job_name    => 'SCOTT_EXPORT',
    version     => 'LATEST');

  -- Specify the dump file name and directory object name.
  DBMS_DATAPUMP.add_file(
    handle    => l_dp_handle,
    filename  => 'SCOTT.dmp',
    directory => 'TEST_DIR');

  -- Specify the log file name and directory object name.
  DBMS_DATAPUMP.add_file(
    handle    => l_dp_handle,
    filename  => 'expdpSCOTT.log',
    directory => 'TEST_DIR',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  -- Specify the schema to be exported.
  DBMS_DATAPUMP.metadata_filter(
    handle => l_dp_handle,
    name   => 'SCHEMA_EXPR',
    value  => '= ''SCOTT''');

  DBMS_DATAPUMP.start_job(l_dp_handle);

  DBMS_DATAPUMP.detach(l_dp_handle);
END;
/
```

Once the job has started the status can be checked using `system@db10g> select * from dba_datapump_jobs;`

The following is an example of how this API can be used to perform a **schema import with a schema remap operation**.
```
DECLARE
  l_dp_handle       NUMBER;
BEGIN
  -- Open an schema import job.
  l_dp_handle := DBMS_DATAPUMP.open(
    operation   => 'IMPORT',
    job_mode    => 'SCHEMA',
    remote_link => NULL,
    job_name    => 'SCOTT_IMPORT',
    version     => 'LATEST');

  -- Specify the dump file name and directory object name.
  DBMS_DATAPUMP.add_file(
    handle    => l_dp_handle,
    filename  => 'SCOTT.dmp',
    directory => 'TEST_DIR');

  -- Specify the log file name and directory object name.
  DBMS_DATAPUMP.add_file(
    handle    => l_dp_handle,
    filename  => 'impdpSCOTT.log',
    directory => 'TEST_DIR',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  -- Perform a REMAP_SCHEMA from SCOTT to SCOTT2.
  DBMS_DATAPUMP.metadata_remap(
    handle     => l_dp_handle,
    name       => 'REMAP_SCHEMA',
    old_value  => 'SCOTT',
    value      => 'SCOTT2');

  DBMS_DATAPUMP.start_job(l_dp_handle);

  DBMS_DATAPUMP.detach(l_dp_handle);
END;
/
```








<br /><br /><br /><a name="deinst_11gXE"></a>  
***  
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....**[Deinstall 11g XE](#deinst_11gXE)**
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....[ Links](#links)  
***  
# Deinstall DB XE (11.2)
if intend inst. some other ver. XE on same PC  
Export data files (Schemas) before you deinstall DB XE software and database.  
Caution:  When you deinstall Oracle Database XE, all components, including data files, the database, and the software, are removed.  

### Deinstalling method Using User Interface
Use **Revo (Add or Remove Programs)** to deinstall Oracle Database XE:
1. In Windows Control Panel, select Add or Remove Programs.
2. Select Oracle Database 18c Express Edition -> Click Change/Remove or Uninstall.

Alternatively, you can use following  msiexec  command to perform the deinstall:  
msiexec /x {C220B7FD-3095-47FC-A0C0-AE49DE6E320A}  

### Deinstalling method 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  xe_temp ,
enter the following command to deinstall Oracle Database XE: 
`setup.exe /s /x /v"/qb /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 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.


<br /><br /><br />
## Unplug / Plug - Migrating Data and Apps in and out of your XE DB
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 fromDB XE 18c to Oracle Database **Enterprise** Edition 18c is supported via the **unplug, plug scenario only !**. You can unplug a PDB from XE 18c DB and plug it into another Enterpise Edition 18c DB of the **same release**.

Migrating from XE 18c to any of the other 18c is not supported.

Upgrade from XE 11.2 to XE 18c is not supported.

### Procedure to Unplug a PDB from 18c XE and Plug into 18c EE 
A Pluggable DB (PDB) created in XE 18c can be migrated to another Multitenant Container DB (CDB) Enterprise Edition (EE) 18c.
The standard plug compatibility requirements apply. See **Oracle Multitenant Administrators Guide** for more info 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.
1. Use SQLPlus 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;  
   
2. Unplug closed PDB and specify path and name of XML file :
   ALTER PLUGGABLE DATABASE xepdb1 UNPLUG INTO '\tmp\xepdb1.xml';

3. Drop the closed PDB and keep the data files :
   DROP PLUGGABLE DATABASE xepdb1 KEEP DATAFILES;

4. Log in to your EE environment. The xepdb1 metadata file should be available from that system.

5. Run 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;
/
```

6. If the PDB is compatible with the CDB, you can plug xepdb1 into the 18c EE database.
   Use 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;

7. 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';

8. If PDB is in  MOUNTED  state, then open the PDB.  
   ALTER PLUGGABLE DATABASE newxepdb OPEN;

9. This example assumes:  
   1. 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.
   2. The files are in the correct location. Therefore, NOCOPY is included.
   3. Storage limits are not required for the PDB. Therefore, the STORAGE clause is not required.
   4. 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.

10. Run catalog, catproc and utrlp on the plugged in NEWXEPDB.  
   sqlplus / as sysdba  
   **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
--
ALTER SESSION SET CONTAINER=newxepdb;
--
SPOOL catalog.log
@%ORACLE_HOME%\rdbms\admin\catalog
SPOOL OFF;
--
SPOOL catproc.log
@%ORACLE_HOME%\rdbms\admin\catproc
SPOOL OFF;
--
SPOOL utlrp.log
@%ORACLE_HOME%\rdbms\admin\utlrp
SPOOL OFF;
--
SET LINESIZE 254 trimspool on arraysize 100 tab off
COLUMN COMP_NAME HEADING COMP_NAME FORMAT A30
COLUMN STATUS HEADING paSTATUSssword FORMAT A15
COLUMN VERSION HEADING VERSION FORMAT A30
SELECT COMP_NAME, STATUS, VERSION FROM DBA_REGISTRY;
--
COMP_NAME                      STATUS          VERSION
------------------------------ --------------- ------------------------------
Oracle Application Express     VALID           4.0.2.00.09
Oracle XML Database            VALID           11.2.0.2.0
Oracle Text                    VALID           11.2.0.2.0
Oracle Database Catalog Views  VALID           11.2.0.2.0
Oracle Database Packages and T VALID           11.2.0.2.0
ypes
--
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
```

SELECT OWNER, OBJECT_NAME FROM DBA_INVALID_OBJECTS WHERE STATUS = 'INVALID';  
no rows selected  
Note:
1. See Plugging an Unplugged Pluggable Database for more information about plugging in an unplugged pluggable database.
2. See Oracle Multitenant Administrator?s Guide for more information about plugging in an unplugged PDB.




### Backup DB 11g XE - 3 ways
To protect against data loss.

ARCHIVELOG mode disabled - main benefit of **consistent backup** is that **DB does not require recovery after it is restored** - - you can open database immediately. All committed changes are written to the datafiles during shutdown, so the **datafiles are in a transaction-consistent state** .

1. In **SQLdeveloper** in the **View menu select DBA to get access to the dba tools like RMAN and DATAPUMP**. Very nice tool. Don't forget that the backup files are** written by Ora DB server processes**. This means that the **backups are written on DB server, not on your client**. Also, currently it **only generates a backup script. You should run the generated script using RMAN**.

2. An other way to run and configure the backup is by using **dbconsole or grid control**.

3. **datapump** - to transfer data as it was during export. Might be usefull for test sets that have to be refreshed with a specific version of the data. Not really usefull if you want to recover **production transactions to last moment** before disaster stroke.



### ??? Win icon -> type "database" -> Backup database

Warning: Log archiving **(ARCHIVELOG mode) is currently disabled**. If you restore the database from this backup, any transactions that take place between this backup and the next backup will be lost. It is recommended that you enable ARCHIVELOG mode before proceeding so that all transactions can be recovered upon restore. See the section
'Enabling ARCHIVELOG Mode...' in the online help for instructions. Backup with log archiving disabled will shut down and restart DB. Are you sure [Y/N]? Y

Backup in progress...  
Recovery Manager: Release 11.2.0.2.0 - Production on Pet Ruj 11 16:30:17 2020  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  
connected to target database: XE (DBID=2913134267)  
RMAN> set echo on;  
2> shutdown immediate;  
3> startup mount;  
4> configure controlfile autobackup format for device type disk clear;  
5> configure retention policy to redundancy 2;  
6> configure controlfile autobackup on;  
7> sql "create pfile=''C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SPFILE2INIT.ORA'' from spfile";  
8> backup as backupset device type disk database;  
9> configure controlfile autobackup off;  
10> alter database open;  
11> delete noprompt obsolete;  
12>  
echo set on  

using target database control file instead of recovery catalog  
database closed  
database dismounted  
Oracle instance shut down  

connected to target database (not started)  
Oracle instance started  
database mounted  
...  
sql statement: create pfile=''C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SPFILE2INIT.ORA'' from spfile  

**Starting backup at 11.SEPT.20**  
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
```
input datafile file number=00002 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
input datafile file number=00001 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
input datafile file number=00003 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
input datafile file number=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
channel ORA_DISK_1: starting piece 1 at 11.09.20
channel ORA_DISK_1: finished piece 1 at 11.09.20
piece handle=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\BACKUPSET\2020_09_11\O1_MF_NNNDF_TAG20200911T163048_HOQ2JRZ2_.BKP tag=TAG20200911T163048 comment=NONE
```
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 11.09.20

**Starting Control File and SPFILE Autobackup at 11.09.20 **  
    piece handle=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\AUTOBACKUP\2020_09_11\O1_MF_S_1050856233_HOQ2KLBG_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 11.09.20
...  
database opened  
no obsolete backups found  

Recovery Manager complete.  
**Backup of the database succeeded.  **  
Log file is at C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\OXE_BACKUP_CURRENT.LOG.  
Press any key to continue . . .  

### C:\oraclexe\app\oracle\fast_recovery_area\XE

Datafile location :         C:\oraclexe\app\oracle\oradata\XE  
Control file location : 

https://dba.stackexchange.com/questions/179989/oracle-11g-rman-restore-with-another-database-name-from-backup-in-windows-platfo  
Copy full backups, archivelogs, and autobackup folders **from old server's fast recovery area**. Note that you only need to go back to the most recent full backup, no need to get weeks worth of files.




J:\apl\possys6\instlcija\instalac\1_instal_F6i_R6i\1_f6i\STEP05_11g32_64bit
<br /><br /><br /><a name="inst_18cXE"></a>
***  
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....**[Install 18c XE](#inst_18cXE)**.....[ Links](#links)  
***  
# INST / DEINST DB 18c XE 
release 18.0.0.0.0 ver 18.4.0.0.0   from OracleXE184_Win64.zip

J:  
     cd J:\app\ss\product\18.0.0\dbhomeXE\bin
sqlplus /nolog     or
sqlplus / as sysdba  

ALTER SESSION SET container=XEPDB1;
**ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;**  
grant CONNECT,DBA,RESOURCE to HR IDENTIFIED BY HR;  
grant CONNECT,DBA,RESOURCE to MERCEDES IDENTIFIED BY M1;  

**conn HR/HR@XEPDB1 -- case sensitive !!**
**conn MERCEDES/M1@XEPDB1**

**select tname from tab;  or  SELECT table_name FROM user_tables**
exit  
or  
@users  
quit  or ALTER SESSION SET container=CDB$ROOT;  

conn HR/HR@localhost:1521/XEPDB1  
See **COUNTRIES ORA-01410: invalid ROWID** in this txt.  


## Importing data to (18c) XE DB (or to 11g )
1. See **expdp - Exporting Data from 11g XE DB :** cre J:\apl\expdp_db (or \dump_folder) OS dir
2. forgoten file in forgoten dir xsl :       C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml\xsl\kupostdt.xsl  (**11g XE deinstalled to install 11c XE**)
   forgoten file in not forgoten dir xsl : J:\app\ss\product\11.2.0\dbhome_1\rdbms\xml\xsl\kupostdt.xsl  
   J:\app\ss\product\18.0.0\dbhomeXE\rdbms\xml\xsl
3. 
   ```
sqlplus / AS SYSDBA
--not for 11g :
ALTER SESSION SET CONTAINER=XEPDB1;
@users
--
--Create dir. object  exp_schema  and grant  READ  and  WRITE  privileges on it to the  HR or SYSTEM  user.
CREATE DIRECTORY exp_schema AS 'J:\apl\expdp_db';
GRANT READ, WRITE ON DIRECTORY exp_schema TO HR;
GRANT DATAPUMP_EXP_FULL_DATABASE TO HR;
GRANT DATAPUMP_IMP_FULL_DATABASE TO HR;
exit
   ```
4. Import data to 18c XE (or to 11g) database from dump folder :  
   cd J:\app\ss\product\18.0.0\dbhomeXE\bin      (J:\app\ss\product\11.2.0\dbhome_1\bin)  
   #### impdp HR/HR@//SSPC2:1521/XEPDB1 DIRECTORY=exp_schema DUMPFILE=exp_schm_mercedes.dmp LOGFILE=mercedes_imp_lg.log EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\"  
   impdp system/system_password@localhost:listnerport/xepdb1 **full=Y EXCLUDE=**SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\"  directory=exp_schema  dumpfile=DB11G.dmp logfile=impdpDB11G.log  
   was exported so :  
   expdp hr/hr@SSPC2/xe DIRECTORY= exp_schema DUMPFILE=exp_schm_mercedes.dmp LOGFILE=mercedes_lg.log SCHEMAS=mercedes
   
   "See **J:\apl\expdp_db\mercedes_imp18c_lg.log**  

5. conn mercedes/m1@localhost:1521/XEPDB1  
   **select tname from tab;  or  SELECT table_name FROM user_tables**  

   You can ignore the following errors:
   1. ORA-39083: Object type TABLESPACE:"SYSAUX" failed to create with error
   2. ORA-31685: Object type USER:"SYS" failed due to insufficient privileges
   3. ORA-39083: Object type PROCACT_SYSTEM failed to create with error
   4. ORA-01917: user or role 'APEX_040000' does not exist
   5. ORA-31684 "already exists" errors



In Win CLI with admin rights we may restart listener : lsnrctl reload  
lsnrctl start  

#### PS J:\app\ss\product\18.0.0\dbhomeXE\bin> lsnrctl status  

```
LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 14-SEP-2020 23:06:10

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sspc2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 18.0.0.0.0 - Production
Start Date                14-SEP-2020 20:18:05
Uptime                    0 days 2 hr. 48 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   J:\app\ss\product\18.0.0\dbhomeXE\network\admin\listener.ora
Listener Log File         J:\app\ss\product\18.0.0\diag\tnslsnr\sspc2\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sspc2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=J:\APP\SS\PRODUCT\18.0.0\admin\XE\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "703df018f8fa4675abacf4445a795702" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully
PS J:\app\ss\product\18.0.0\dbhomeXE\bin>
```


### 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\ss\product\18.0.0\dbhomeXE\network\admin\tnsnames.ora`

### tnsnames.ora - XE and ora7 are not same in 18c (are same in 11g) 
   ```
# tnsnames.ora Network Configuration File: J:\app\ss\product\18.0.0\dbhomeXE\network\admin\tnsnames.ora
# 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  
   








conn system/MYPSW
**NO NEED :** ALTER USER system IDENTIFIED BY MYPSW ACCOUNT UNLOCK  
ALTER USER system IDENTIFIED BY MYPSW  



# On Windows 10 pro 64 bit  Ver : 10.0.19041
### L:\2_knjige\oracle\2020_oracle-database_19\2019_18c_XE_instalac_guide-ms_win.pdf
>Tutorial (also on local PC) : https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinw/  

https://www.aflorestorres.com/2019/03/install-oracle-database-18c-express-and.html  
https://dbahelp2018.wordpress.com/install-oracle-18c-xe-on-windows-10/  

>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  

Download :  OracleXE184_Win64.zip  
**https://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html**  
and unzip in  `J:\apl\possys6\instlcija\ora18c\OracleXE184_Win64`  
**Take ownership of OracleXE184_Win64 folder**


1. expdp schemas from 11g XE, **Deinstall 11g XE (615 MB)** , check environment variables - Win key and type env - **delete any ORACLE_HOME env. var.**
2. **run as admin** `J:\apl\possys6\instlcija\ora18c\OracleXE184_Win64\setup.exe` 
   1. DESTIN_DIR :        `J:\app\ss\product\18.0.0\`
   2. ORACLE_HOME :  DESTIN_DIR\\dbhomeXE : `J:\app\ss\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 username identified by password;)
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


<br /><br />
## 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);
```












## Test statements, possible strange errors if not take ownership
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\ss\product\18.0.0\dbhomeXE\bin
sqlplus /nolog     or
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  









<br /><br /><br /><br />
```
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 - see XE and ora7 are not same in 18c (are same in 11g)
   ```
    #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;  









<br /><br /><br />
**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;

```

<br /><br />
### 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.  

<br /><br />
### 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\ss\product\18.0.0\dbhomeXE\network\admin\tnsnames.ora`

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


<br /><br />
## 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=\<character_set>"  
where  \<character_set>  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 NLS_LANG 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
 ```




 
<br /><br />
 ## 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/  



<br /><br />
## 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.
```

<br />
### 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\"  

"
<br />
### 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





<br /><br /><br /><a name="inst_11g"></a>  
***  
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....**[Install 11g](#inst_11g)**.....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....[ Links](#links)  
***  








# Install Ora 11g, ver. 11.2.0.1.0
**on Windows 10 64 bit**  
You need an Oracle account and a **license** that allows you to upgrade to 11.2.0.4

Download for Windows :  https://www.oracle.com/database/technologies/112010-win64soft.html  

Tutorial for Windows : **https://www.geeksforgeeks.org/how-to-install-oracle-database-11g-on-windows/**  
See https://support.oracle.com/portal/  

Extract :  
Extract both files win64_11gR2_database_1of2.zip and 2of2 in same directory `J:\apl\possys6\instlcija\instlcija_11g32_64bit\database`

Install :
1. database Folder -> Right click setup.exe -> Run as Administrator
2. "Envir. does not meet min requir." - Click Yes to continue. This will start Oracle Universal Installer
3. leave these fields empty and click on Next  or Provide your Email Address to receive alerts from Oracle
4. Select any of the three different Installation Options 
   1. **cre and conf db** - Install Oracle Server Software and want to Create Database also.
   2.  or only db sw -  to Install Oracle Server only.
   3.  or upgrade existing  db
5. Choose between Server Class and **Desktop Class** Ignore warning related to Admin Password.
6. Configure basic settings :
   1. Oracle base : **J:\app\ss**
   2. SW location : **J:\app\ss\product\11.2.0\dbhome_1**
   3. DB file location : J:\app\ss\oradata
   4. DB edition : Enterprise 3,34 GB
   5. Char set :Default (EE8MSWIN1250)
   6. Global DB name : **orcl**
   7. Admin psw : MYPSW
7. Oracle Universal Installer(OUI) will check for the Prerequisites such as Hardware compatibility

8. Click on Finish to start Installation process. 
9. Click OK to finish the installation.
10. Copy localhost link provided to open your Enterprise Manager. 
   Database Control URL is **https://localhost:1158/em**
   Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: J:/app/ss/product/11.2.0/dbhome_1/localhost_orcl/sysman/config/emkey.ora.   Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
   
   For security reasons, all users are locked except for the following users: SYS and SYSTEM If you have enabled Local Management with Oracle Enterprise Manager, the SYSMAN and DBSNMP accounts are also unlocked. These accounts provide Enterprise Manager with access to the database so it can gather monitoring data and so you can perform administration tasks with Enterprise Manager. If you have enabled Central Management with Oracle Enterprise Manager, the DBSNMP is unlocked, as well as the SYS and SYSTEM user accounts. You can use this page of the Assistant to unlock other database users; or, you can also unlock users at a later time using Oracle Enterprise Manager or the command line tool, SQLPlus. The following table describes the columns in the table of database users.
   User Name   Lock Account? - Select or clear this column to unlock the account and reset the user password. New Password If you unlock a database user account, you must enter a password for the user in this field. Confirm Password Enter the password again to verify the password. 
   
   Click Close Button and you are done with the Installation Process.

Getting Started with Oracle Enterprise Manager :  
There are two ways to start Oracle Enterprise Manager:  
1. One way https://localhost:1158/em is using the Link provided in last step of Installation Process.
   Note: In case if you misplace this link in the future, follow the next step to start it the other way.

2. Go to Start Menu and Search Oracle Folder -> Click on Database Control -> Oracle (Your Global Database Name)  
   This will take you to the Login Screen of your Oracle Enterprise Manager. Click Advanced -> Proceed To Localhost if  
   ERR_CERT_AUTHORITY_INVALID -> Login with your Credentials and begin using 11g DB.


### User HR :
#### PS C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\bin>

#### PS J:\app\ss\product\11.2.0\dbhome_1\bin>
In Win CLI with admin rights we may restart listener : lsnrctl reload  
lsnrctl start  

#### lsnrctl status  

```
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 14-RUJ-2020 15:01:23

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                14-RUJ-2020 00:01:50
Uptime                    0 days 14 hr. 59 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   J:\app\ss\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         j:\app\ss\diag\tnslsnr\sspc2\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
PS J:\app\ss\product\11.2.0\dbhome_1\bin>
```



####  Sqlplus system/MYPSW@orcl
--sqlplus / as sysdba  
--conn system/MYPSW@orcl  
ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;  
grant CONNECT,DBA,RESOURCE to hr IDENTIFIED BY hr;  
exit  

#### Sqlplus hr/hr@localhost:1522/orcl  
-- orcl in 11g, XEPDB1  in 18c

sho user --(SYS) USER is "HR"    show con_name --n/a in 11g,  CDB$ROOT or XEPDB1 

SELECT name FROM v$database;  
--orcl  

See **COUNTRIES ORA-01410: invalid ROWID** in this txt.












SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;  
--0  

By default, the XML database is configured with http port set to 8080 and FTP port to 2100. 
```
--Set FTP and HTTP port
begin
   dbms_xdb.setFTPPort(new_port => 2122);
   dbms_xdb.setHTTPPort(new_port => 8089);
end;
/


--Get new values
declare
   v_ftpport number;
   v_httpport number;
begin
   select dbms_xdb.getFTPPort into v_ftpport from dual;
   select dbms_xdb.getHTTPPort into v_httpport from dual;
   dbms_output.put_line(a => 'FTP port:'||v_ftpport);
   dbms_output.put_line(a => 'HTTP port:'||v_httpport);
end;
/
```





<br /><br /><br /><a name="deinst_11g"></a>  
***  
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....**[Deinstall 11g](#deinst_11g)**.....[Install 18c XE](#inst_18cXE).....[ Links](#links)  
***  
# Deinstall DB 11g (not XE)
http://www.rebellionrider.com/how-to-uninstall-oracle-database-11g-from-windows-10-64bit/     **Manish Sharma** January 10, 2019
1. This PC Properties -> Advance System Settings -> System properties (go to the Advance Tab) -> Environment Variable-> Path -> delete ...product\\11.2.0... or "Environment variable path is too long"

2. Delete the registries of oracle database 11g Winkey + R -> regedit
   1. delete the registry for Oracle Software HKEY_LOCAL_MACHINE ?> SOFTWARE ?> ORACLE - Right click ORACLE dir and then select EXPORT - then right click and Delete ORACLE dir **after you  make sure** only oracle database 11g software is installed in your system. In case you have other software from oracle installed then take a good look at the content of this folder and delete only those associated with 11g.

   2. delete registries which are responsible for running oracle database services HKEY_LOCAL_MACHINE -> SYSTEM -> CurrentControlSet -> SERVICES  - Right click CurrentControlSet and select export.
      Under services directory you will have to find all the registries listed below and delete them. List of Oracle Database Registries for background services :
      ....11g Enterprise................................................11g XE
      1. Oracle11
      2. OracleDBConsoleorcl
      3. OracleJobSchedulerORCL
      4. OracleMTSRecoveryService
      5. OracleOraDb11g_home1ClrAgent
      6. OracleOraDb11g_home1TNSListener
      7. OracleServiceORCL
      8. OracleVssWriterORCL
      
      ORCL is default SID for the Oracle database 11g Enterprise Edition.

3. Restart your PC

4. Wipe out oracle mess - delete all the folders of oracle database 11g from your windows 10 system. 
   1. **Oracle home (dir where ora DB SW is installed)** is located in the C drive or in the main drive in which your OS is installed and **named as APP**. 
   2. Thereafter go to the directory with your username. Here select all the files and delete them. If you want to save backups then do not delete flash_recovery_area.
   3. go to your Program File folder which is placed in your main drive where OS is installed and here locate a folder with the name ?Oracle? and delete it.
   4.  delete the entry from your start menu . copy and paste the path given below either in the Run command box or directly into your file explorer :
           C:\ProgramData\Microsoft\Windows\Start Menu\Programs- > find oracle database 11g folder eg "Oracle - OraDb11g_home1" - delete this folder.

5. Restart your PC













<br /><br /><br /><a name="links"></a>  
***  
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....**[ Links](#links)**  
***  
## http://www.rebellionrider.com/category/oracle-database-18c/
1. expdp/impdp : http://www.rebellionrider.com/data-pump-expdp-how-to-export-schema-oracle/
          http://www.rebellionrider.com/system-privileges-with-admin-option-oracle/
2. Install : http://www.rebellionrider.com/how-to-install-oracle-database-18c-on-windows-10/  
   11g : https://www.youtube.com/watch?v=d_CyuCLC3Ls
   find-out-the-sid : http://www.rebellionrider.com/how-to-find-out-the-sid-and-db-home-in-oracle-database/  
3. unlock-hr-user http://www.rebellionrider.com/how-to-unlock-hr-user-in-oracle-database-18c-by-manish-sharma/
4. connect-with-sample-schema-hr http://www.rebellionrider.com/how-to-connect-with-sample-schema-hr-in-oracle-database-18c-by-manish-sharma/
5.connect-with-pluggable-db  http://www.rebellionrider.com/connect-with-pluggable-database-using-sql-developer-in-oracle-database-18c-by-manish-sharma/
6. uninstall http://www.rebellionrider.com/how-to-uninstall-oracle-database-18c-by-manish-sharma/



<br /><br /><br />

create table sstest (name varchar(10));  
insert into sstest values(?slavko s.?);  
commit;  

https://www.tutorialspoint.com/plsql/plsql_dbms_output.htm  

-- DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000);  
--if buffer_size=NULL=unlimited  
```
PS J:\app\ss\product\11.2.0\dbhome_1\bin>
Sqlplus hr/hr@localhost:1522/orcl

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON
DECLARE
   lines_chararr dbms_output.chararr;
   num_lines number;
   num_of_tbls number;
   num_of_txt_lines number;
BEGIN
    DBMS_OUTPUT.DISABLE;
    DBMS_OUTPUT.ENABLE(NULL);
  
    num_lines := 0; 
    num_of_tbls := 0 ;

    -- 1. txt lines :
    dbms_output.put_line  (user || ' Tables in DB :');  num_lines := num_lines + 1;
    dbms_output.put_line('SELECT table_name FROM user_tables :'); num_lines := num_lines + 1;
    num_of_txt_lines := num_lines ;

    --2. tbl names :
    FOR t IN (SELECT table_name FROM user_tables)
         LOOP dbms_output.put_line(t.table_name); num_lines := num_lines + 1;
         num_of_tbls := num_of_tbls +1 ;
    END LOOP;

    --3. put 1. and 2. in lines_ chararr :
    dbms_output.get_lines(lines_chararr, num_lines);

    --4. display lines_ chararr :
    FOR ii IN 1..num_lines LOOP
       if ii > num_of_txt_lines then 
       dbms_output.put_line((ii - num_of_txt_lines)||'. '||lines_chararr(ii));
       else dbms_output.put_line(lines_chararr(ii));
       end if ;
    END LOOP;
    --DBMS_OUTPUT.DISABLE;
END;
/ 
SET SERVEROUTPUT OFF

HR Tables in DB :
SELECT table_name FROM user_tables :
1. REGIONS
2. LOCATIONS
3. DEPARTMENTS
4. JOBS
5. EMPLOYEES
6. JOB_HISTORY
7. T
8. COUNTRIES
```


<br /><br /><br />
___
<a name="md2html"></a>[Top](#top)
>### MD to HTML converters on inet
1. **https://www.tutorialspoint.com/online_markdown_editor.php**        or     https://markdowntohtml.com/
2. or   https://www.browserling.com/tools/markdown-to-html (many converters)  
3. or files convert :  https://products.aspose.app/pdf/conversion/md-to-html  to many formats  
4. Links not working :     http://demo.showdownjs.com/ (no HTML source)  
  NOT WORKING : https://daringfireball.net/projects/markdown/dingus    or   https://pandoc.org/try/ 

>https://www.w3schools.com/html/html_symbols.asp   

>https://www.degraeve.com/reference/specialcharacters.php



<br /><br /><br /><a name="eof"></a>  
***  
[Top](#top).....[expdp 11g XE](#expdp_11gXE)......[impdp 18c XE](#impdp_18cXE).....[Deinstall 11g XE](#deinst_11gXE)
.....[Install 11g](#inst_11g).....[Deinstall 11g](#deinst_11g).....[Install 18c XE](#inst_18cXE).....[ Links](#links)  
***  

For more information send a message to info at phpclasses dot org.