Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
Purpose
Creates and maintains analytic cubes
AUTHID
CURRENT_USER
Data Types
TYPE id_array ... definition TBD
Dependencies
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_CUBE' UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_CUBE' ORDER BY 1;
To create a cube materialized view requires:
CREATE [ANY] MATERIALIZED VIEW, CREATE [ANY] DIMENSION, and ADVISOR
To access cube materialized views from another schema using query rewrite, you must have these privileges:
GLOBAL QUERY REWRITE, SELECT on the elational source tables, SELECT on the analytic workspace (AW$name) that supports the cube materialized view,
SELECT on the cube, and SELECT on the cube's dimensions.
Loads data into one or more cubes and dimensions, and prepares the data for querying
This procedure is very poorly named as it builds nothing ... rather it loads data.
dbms_cube.build(
script IN VARCHAR2,
method IN VARCHAR2 DEFAULT NULL,
refresh_after_errors IN BOOLEAN DEFAULT FALSE,
parallelism IN BINARY_INTEGER DEFAULT 0,
atomic_refresh IN BOOLEAN DEFAULT FALSE,
automatic_order IN BOOLEAN DEFAULT TRUE,
add_dimensions IN BOOLEAN DEFAULT TRUE,
scheduler_job IN VARCHAR2 DEFAULT NULL,
master_build_id IN BINARY_INTEGER,
rebuild_freepools IN BOOLEAN,
nested IN BOOLEAN,
job_class IN VARCHAR2 DEFAULT NULL);
Script Commands
CLEAR [ VALUES | LEAVES | AGGREGATES ]
Prepares the cube for a data refresh. It can also be used on dimensions, but CLEAR removes all dimension keys, and thus deletes all data values for cubes that use the dimension.
The optional arguments control the refresh method:
VALUES: Clears all data in the cube. All facts must be reloaded and all aggregates must be recomputed.
This option supports the COMPLETE refresh method. (Default for the C and F methods).
LEAVES: Clears the detail data and retains the aggregates. All facts must be reloaded, and the aggregates for any new or changed facts must be computed.
This option supports the FAST refresh method.
AGGREGATES: Retains the detail data and clears the aggregates. All aggregates must be recomputed.
Methods
A full or a fast (partial) refresh. In a fast refresh, only changed rows are inserted in the cube and the affected areas of the cube are re-aggregated.
You can specify a method for each cube in sequential order, or a single method to apply to all cubes. If you list more cubes than methods, then the last method applies to the additional cubes.
C: Complete refresh clears all dimension values before loading. (Default)
F: Fast refresh of a cube materialized view, which performs an incremental refresh and re-aggregation of only changed rows in the source table.
?: Fast refresh if possible, and otherwise a complete refresh.
P: Recomputes rows in a cube materialized view that are affected by changed partitions in the detail tables.
S: Fast solve of a compressed cube. A fast solve reloads all the detail data and re-aggregates only the changed values.
BEGIN
dbms_cube.build('GLOBAL.TIME USING (LOAD NO SYNCH, COMPILE),
GLOBAL.CHANNEL, GLOBAL.UNITS_CUBE USING (CLEAR LEAVES, LOAD, SOLVE, ANALYZE)', '?', FALSE, 2, FALSE, TRUE, FALSE, 'Units Cube');
END;
/
dbms_cube.build_slave(
script IN VARCHAR2,
partition_member IN VARCHAR2,
scheduler_job IN VARCHAR2
master_build_id IN BINARY_INTEGER,
job_class IN VARCHAR2,
slave_build_number IN BINARY_INTEGER);
dbms_cube.create_cube_rewrite_mview(
schema_name IN VARCHAR2,
cube_name IN VARCHAR2,
rewrite_type IN VARCHAR2,
refresh_type IN VARCHAR2,
using_constraints_clause IN VARCHAR2);
dbms_cube.create_export_options(
out_options_xml IN OUT CLOB,
target_version IN VARCHAR2,
suppress_owner IN BOOLEAN,
suppress_namespace IN BOOLEAN,
preserve_table_owners IN BOOLEAN,
metadata_changes IN CLOB);
SELECT object_type, COUNT(*)
FROM user_objects
WHERE created > SYSDATE-15/1440
GROUP BY object_type
ORDER BY 1;
SELECT object_name, object_type, created
FROM user_objects
WHERE created > SYSDATE-15/1440
ORDER BY 2,1;
desc CB$CAL_MONTH_SALES
SELECT COUNT(*) FROM CB$CAL_MONTH_SALES;
desc CB$TIMES_DIM_D1_CAL_ROLLUP
SELECT COUNT(*) FROM CB$TIMES_DIM_D1_CAL_ROLLUP;
desc CR$CAL_MONTH_SALES
SELECT COUNT(*) FROM CR$CAL_MONTH_SALES;
SELECT object_name, object_type, created
FROM user_objects
WHERE created > SYSDATE-10/1440
AND object_type = 'CUBE'
ORDER BY 2,1;
SELECT object_name, object_type, created
FROM user_objects
WHERE created > SYSDATE-10/1440
AND object_type = 'CUBE DIMENSION'
ORDER BY 2,1;
desc user_cube_dimensions
SELECT * FROM user_cube_dimensions;
BEGIN
dbms_cube.build('CAL_MONTH_SALES');
END;
/
SELECT COUNT(*) FROM CB$CAL_MONTH_SALES;
SELECT COUNT(*) FROM CB$TIMES_DIM_D1_CAL_ROLLUP;
SELECT COUNT(*) FROM CR$CAL_MONTH_SALES;
ALTER MATERIALIZED VIEW cal_month_sales_mv DISABLE QUERY REWRITE;
EXPLAIN PLAN FOR
SELECT t.calendar_quarter_desc, SUM(s.amount_sold) AS dollars
FROM sales s,
times t
WHERE s.time_id = t.time_id
AND t.calendar_quarter_desc LIKE '2001%'
GROUP BY t.calendar_quarter_desc
ORDER BY t.calendar_quarter_desc;
dbms_cube.export_xml_to_file(
object_ids IN VARCHAR2,
output_dirname IN VARCHAR2,
output_filename IN VARCHAR2);
TBD
Overload 2
dbms_cube.export_xml_to_file(
object_ids IN VARCHAR2,
options_dirname IN VARCHAR2,
options_filename IN VARCHAR2,
output_dirname IN VARCHAR2,
output_filename IN VARCHAR2);
-- import xml
dbms_cube.import_xml(in_xml, out_xml);
dbms_output.put_line(out_xml);
END;
/
Overload 3
dbms_cube.import_xml(
dirname IN VARCHAR2,
filename IN VARCHAR2);
BEGIN
dbms_cube.import_xml('CTEMP', 'sales_cube.xml');
END;
/
Overload 4
dbms_cube.import_xml(
dirname IN VARCHAR2,
filename IN VARCHAR2,
out_xml IN OUT CLOB);
TBD
Overload 5
dbms_cube.import_xml(
in_xml IN CLOB,
options_xml IN CLOB,
out_xml IN OUT CLOB);
TBD
Overload 6
dbms_cube.import_xml(
input_dirname IN VARCHAR2,
input_filename IN VARCHAR2,
options_dirname IN VARCHAR2,
options_filename IN VARCHAR2,
out_xml IN OUT CLOB);
Processes analytic workspaces created in Oracle OLAP 10g so they can be used by Oracle OLAP 12c clients.
Processes all analytic workspaces in the current schema.
Overload 1
dbms_cube.initialize_cube_upgrade;
exec dbms_cube.initialize_cube_upgrade;
Overload 2
dbms_cube.initialize_cube_upgrade(pouttblname IN OUT VARCHAR2);
dbms_cube.refresh_mview(
mvowner IN VARCHAR2,
mvname IN VARCHAR2,
method IN VARCHAR2 DEFAULT NULL,
refresh_after_errors IN BOOLEAN DEFAULT FALSE,
parallelism IN BINARY_INTEGER DEFAULT NULL,
atomic_refresh IN BOOLEAN DEFAULT FALSE,
scheduler_job IN VARCHAR2 DEFAULT NULL,
sam_parameters IN CLOB DEFAULT NULL,
nested IN BOOLEAN);
-- see build procedure for method parameters
conn sh/sh@pdbdev
set serveroutput on
-- use the default settings to refresh a cube materialized view
exec dbms_cube.refresh_mview('SH', 'CB$FWEEK_PSCAT_SALES');
-- changes the refresh method to use fast refresh if possible, continue
-- refreshing after an error, and use two parallel processes
exec dbms_cube.refresh_mview('SH', 'CB$FWEEK_PSCAT_SALES', '?', TRUE, 2);
