Oracle Database 19c Related Various SQL Codes

Find Oracle Apex Workspace Name And Username?

SELECT * FROM all_users;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ----------
XS$NULL                        2147483638 29.05.2014
MIKE                                   71 30.03.2018
SCOTT                                  66 03.03.2018
APEX_040000                            47 29.05.2014   --> this
APEX_PUBLIC_USER                       45 29.05.2014

Oracle DROP USER

DROP USER TEST [CASCADE];

CREATE USER TEST IDENTIFIED BY test1234;

DROP USER test;

Uses Oracle Drop User to delete a user who has a schema object

CREATE USER bar 
    IDENTIFIED BY abcd1234 
    QUOTA 5m ON users;

GRANT 
    CREATE SESSION,
    CREATE TABLE
TO bar;

Delete workspace in runtime development environment

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
connect sys as sysdba

Run the following statement:

ALTER SESSION SET CURRENT_SCHEMA = test

Run the following statement:

BEGIN
APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE(WORKSPACE_NAME, DROP_USER, DROP_TABLESPACE)
END;

The Following DDL (Data Definition Language) Generates A Table Of All Workspaces Requested Before June 28, 2004 But Which Have Been Inactive Since June 10, 2004. In This Example, Inactivity Is Determined By Checking A Key In The Application Express Engine Schema For The Most Recent Update By Each Workspace.

ALTER SESSION SET CURRENT_SCHEMA = APEX_040000;
CREATE TABLE ws_to_purge AS
 SELECT c.security_group_id, c.company_name, c.admin_email, c.request_date,
 SYSDATE last_updated_on, 'Y' ok_to_delete
   FROM wwv_flow_provision_company c
  WHERE
c.request_date <= to_date('20040628','YYYYMMDD') AND
     (  not exists
 (SELECT NULL /* Activity Log */
        FROM wwv_flow_activity_log l
       WHERE l.security_group_id = c.security_group_id
         AND l.time_stamp > to_date('20040610','YYYYMMDD'))
 )
    AND NOT EXISTS
     (SELECT NULL /* workspace applications */
        FROM wwv_flows f
       WHERE f.security_group_id = c.security_group_id
         AND f.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Pages */
        FROM wwv_flow_steps s
       WHERE s.security_group_id = c.security_group_id
         AND s.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Regions */
        FROM wwv_flow_page_plugs p
       WHERE p.security_group_id = c.security_group_id
         AND p.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Items */
        FROM wwv_flow_step_items i
       WHERE i.security_group_id = c.security_group_id
         AND i.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Templates */
        FROM wwv_flow_templates t
       WHERE t.security_group_id = c.security_group_id
         AND t.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Files uploaded */
        FROM wwv_flow_file_objects$ o
       WHERE o.security_group_id = c.security_group_id
         AND o.created_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* SQL Workshop history */
        FROM wwv_flow_sw_sql_cmds s
       WHERE s.security_group_id = c.security_group_id
         AND s.created_on > to_date('20040610','YYYYMMDD'));

Exclude schemas, tablespaces, and data files exclusively used by inactive workspaces from the database. You can detect drop schemes by running a similar query as follows:

SELECT s.schema
  FROM ws_to_purge ws,
       wwv_flow_company_schemas s
WHERE s.security_group_id = ws.security_group_id
   AND ws.ok_to_delete = 'Y';

Deleting inactive workspaces

BEGIN 
     FOR c1 IN (SELECT security_group_id  
                 FROM ws_to_purge
                 WHERE ok_to_delete = 'Y')
     LOOP
         WWV_FLOW_PROVISIONING.TERMINATE_SERVICE_BY_SGID(c1.security_group_id);
     END LOOP;
 END;

🔗 Demo Application-
URL- Demo Application
Username - demo, Pass- demo

I hope everyone will like it. Please watch the full video,
Comment on any of your problems, I will try my best to solve the problem, In-Shah Allah. Everyone's cooperation is desirable. Visit my blog site, new technology related videos, you will get different types of tutorials of Oracle Apex, and hopefully, you can use them in your daily work. database 19c related various
Please stay tuned by subscribing to the YouTube channel, and encourages new videos to be uploaded. database 19c related various
=================
Visit my site to get more collaborative posts about Oracle Apex and subscribe to my YouTube channel. Thanks.
Comment on any of your issues, I will try my best to solve the problem, In-Shah Allah. Everyone's cooperation is desirable.
Visit my blog site, new technology-related videos, you will get different types of tutorials of Oracle Apex, and hopefully, you can use them in your daily work. database 19c related various
==============================

🙍🏾‍ Md jABER HOSSEN
📲 Mobile- +8801760688286
📨 Email- jaberit786@gmail.com
🌐 FB- facebook.com/mdjaber.hossen1
Please Subscribe to My Channel

Many thanks for visiting the site.