E
Especially when you come to a new environment, you might want to check existing apps. What apps are mapped to what schema, how big they are, do they use authorization schemes? Which pages are the main, which pages are not used? Which pages has forms or grids? Which pages has dynamic actions or hardcoded JavaScript? Etc. Following scripts can help you to figure out the structure of your applications/pages.
Get list of applications with some details:
SELECT
w.workspace,
--w.workspace_id
a.owner,
a.application_group AS app_group,
a.application_id AS app_id,
a.alias AS app_alias,
a.application_name AS app_name,
a.pages,
a.application_items AS items,
a.application_processes AS processes,
a.application_computations AS computations,
a.application_settings AS settings,
a.lists,
a.lists_of_values AS lovs,
a.web_services AS ws,
a.translation_messages AS translations,
a.build_options,
--
a.authorization_schemes AS authz_schemes,
CASE WHEN a.authentication_scheme_type != 'No Authentication' THEN a.authentication_scheme END AS authn_scheme,
--a.availability_status,
CASE WHEN a.db_session_init_code IS NOT NULL THEN 'Y' END AS has_init_code,
CASE WHEN a.db_session_cleanup_code IS NOT NULL THEN 'Y' END AS has_cleanup,
CASE WHEN a.friendly_url = 'Yes' THEN 'Y' END AS has_friendly_url,
CASE WHEN a.debugging = 'Allowed' THEN 'Y' END AS has_debugging,
CASE WHEN a.error_handling_function IS NOT NULL THEN 'Y' END AS has_error_fn,
--
a.compatibility_mode,
a.created_on AS created_at,
a.last_updated_on AS changed_at
FROM apex_applications a
JOIN apex_workspace_schemas s
ON s.workspace_id = a.workspace_id
AND s.schema = a.owner
JOIN apex_workspaces w
ON w.workspace_id = a.workspace_id
WHERE a.owner NOT LIKE 'APEX%'
--WHERE a.owner = ''
ORDER BY
a.application_id;
Get list of pages with some details (for specific application):
WITH d AS (
SELECT
d.application_id,
d.page_id,
COUNT(*) AS count_da
FROM apex_application_page_da d
GROUP BY
d.application_id,
d.page_id
),
r AS (
SELECT
r.application_id,
r.page_id,
SUM(CASE WHEN r.source_type_plugin_name = 'NATIVE_FORM' THEN 1 ELSE 0 END) AS count_forms,
SUM(CASE WHEN r.source_type_plugin_name = 'NATIVE_IG' THEN 1 ELSE 0 END) AS count_grids,
SUM(CASE WHEN r.source_type_plugin_name IN ('NATIVE_IR', 'NATIVE_SQL_REPORT') THEN 1 ELSE 0 END) AS count_reports,
SUM(CASE WHEN r.source_type_plugin_name = 'NATIVE_JET_CHART' THEN 1 ELSE 0 END) AS count_charts
FROM apex_application_page_regions r
GROUP BY
r.application_id,
r.page_id
)
SELECT
--p.workspace,
--p.application_id,
p.page_group,
p.page_id,
p.page_alias,
p.page_name,
--p.page_title,
--
NULLIF(p.regions, 0) AS regions,
NULLIF(r.count_reports, 0) AS reports,
NULLIF(r.count_grids, 0) AS grids,
NULLIF(p.report_columns, 0) AS columns,
NULLIF(r.count_charts, 0) AS charts,
NULLIF(r.count_forms, 0) AS forms,
NULLIF(p.items, 0) AS items,
NULLIF(p.buttons, 0) AS buttons,
NULLIF(p.computations, 0) AS computations,
NULLIF(p.processes, 0) AS processes,
NULLIF(p.validations, 0) AS validations,
NULLIF(p.branches, 0) AS branches,
NULLIF(d.count_da, 0) AS d_actions,
--
CASE WHEN (p.javascript_code IS NOT NULL OR p.javascript_code_onload IS NOT NULL) THEN 'Y' END AS has_javascript,
CASE WHEN p.inline_css IS NOT NULL THEN 'Y' END AS has_css,
--
CASE WHEN p.page_requires_authentication = 'Yes' AND p.authorization_scheme IS NOT NULL THEN 'Y' END AS has_auth,
CASE WHEN p.page_access_protection IN ('No URL Access', 'Arguments Must Have Checksum') THEN 'Y' END AS has_protection,
--
p.authorization_scheme,
p.build_option,
CASE WHEN p.page_mode != 'Normal' THEN 'Y' END AS is_modal,
-- check dialog_attributes + dialog_chained for modal pages
p.page_template,
p.page_comment,
p.created_on AS created_at,
p.last_updated_on AS changed_at
FROM apex_application_pages p
JOIN r
ON r.application_id = p.application_id
AND r.page_id = p.page_id
JOIN d
ON d.application_id = p.application_id
AND d.page_id = p.page_id
WHERE p.application_id = &APP_ID
ORDER BY
p.application_id,
p.page_id;
And similar to Tracking developers activity you can check which pages are actually used by users or not:
WITH x AS (
SELECT /*+ MATERIALIZE */
&APP_ID AS app_id
FROM DUAL
),
a AS (
SELECT /*+ MATERIALIZE */
a.application_id,
a.page_id,
TRUNC(a.view_date) AS view_date,
a.page_view_type,
a.elapsed_time,
a.apex_user,
a.apex_session_id
FROM apex_workspace_activity_log a
JOIN x
ON x.app_id = a.application_id
),
s AS (
SELECT /*+ MATERIALIZE */
a.application_id,
a.page_id,
a.view_date,
--
NULLIF(SUM(CASE WHEN a.page_view_type = 'Rendering' THEN 1 ELSE 0 END), 0) AS rendering,
NULLIF(SUM(CASE WHEN a.page_view_type = 'Processing' THEN 1 ELSE 0 END), 0) AS processing,
NULLIF(SUM(CASE WHEN a.page_view_type = 'Ajax' THEN 1 ELSE 0 END), 0) AS ajax,
--
COUNT(*) AS activity,
COUNT(DISTINCT a.apex_user) AS users,
COUNT(DISTINCT a.apex_session_id) AS sessions,
--
ROUND(AVG(a.elapsed_time),2) AS avg_time,
ROUND(MAX(a.elapsed_time), 2) AS max_time
FROM a
GROUP BY
a.application_id,
a.page_id,
a.view_date
ORDER BY 1, 3 DESC
)
--SELECT * FROM s; -- check this out too
SELECT
p.application_id,
p.page_id,
p.page_name,
--
CASE WHEN SUM(NVL(s.activity, 0)) > 0 THEN 'Y' END AS is_used,
--
NULLIF(SUM(NVL(s.activity, 0)), 0) AS total,
--
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 0 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS today,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 1 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t1, -- yesterday
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 2 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t2,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 3 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t3,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 4 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t4,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 5 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t5,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 6 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t6,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 7 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t7,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 8 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t8,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 9 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t9,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 10 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t10,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 11 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t11,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 12 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t12,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 13 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t13,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 14 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t14,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 15 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t15,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 16 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t16,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 17 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t17,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 18 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t18,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 19 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t19,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 20 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t20,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 21 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t21,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 22 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t22,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 23 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t23,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 24 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t24,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 25 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t25,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 26 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t26,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 27 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t27,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 28 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t28,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 29 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t29,
NULLIF(SUM(CASE WHEN s.view_date = TRUNC(SYSDATE) - 30 THEN NVL(s.activity, 0) ELSE 0 END), 0) AS t30
FROM apex_application_pages p
JOIN x
ON x.app_id = p.application_id
AND p.page_id NOT IN (0)
LEFT JOIN s
ON s.application_id = p.application_id
AND s.page_id = p.page_id
GROUP BY
p.application_id,
p.page_id,
p.page_name
ORDER BY
p.application_id,
p.page_id;
And you can check which page is flooding your debug messages in past week:
SELECT
d.page_id,
--
COUNT(DISTINCT(d.apex_user)) AS users,
MAX(d.elapsed_time) AS max_elapsed_time,
MAX(d.execution_time) AS max_execution_time,
NULLIF(COUNT(*), 0) AS total, -- number of messages
--
NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) - 0 THEN 1 ELSE 0 END), 0) AS today,
NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) - 1 THEN 1 ELSE 0 END), 0) AS t1, -- yesterday
NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) - 2 THEN 1 ELSE 0 END), 0) AS t2,
NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) - 3 THEN 1 ELSE 0 END), 0) AS t3,
NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) - 4 THEN 1 ELSE 0 END), 0) AS t4,
NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) - 5 THEN 1 ELSE 0 END), 0) AS t5,
NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) - 6 THEN 1 ELSE 0 END), 0) AS t6,
NULLIF(SUM(CASE WHEN TRUNC(d.message_timestamp) = TRUNC(SYSDATE) - 7 THEN 1 ELSE 0 END), 0) AS t7
FROM apex_debug_messages d
WHERE d.application_id = &APP_ID
AND d.page_id IS NOT NULL
GROUP BY d.page_id
ORDER BY d.page_id;
Comments
Post a Comment