Data Integration and Virtualisation
Category : Supporting Scripts
A gitignore file to ignore standard OS (usually Windows) generated files. Often you’d use this in addition to a more technology specific gitignore set.
See .gitignore for more info.
# OS generated files #
######################
.DS_Store
.DS_Store?
._*
.*.sw[op]
.Spotlight-V100
.Trashes
ehthumbs.db
Thumbs.dbCategory : Supporting Scripts
Create a MySQL or MariaDB view to show the size of each table in the database:
See database size for more information.
CREATE OR REPLACE VIEW table_size AS
SELECT table_schema
, table_name
, round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM information_schema.tables
WHERE table_schema IN ('dv', 'da', 'hue')
GROUP BY table_schema
, table_name
ORDER BY table_size_mb DESCCategory : Supporting Scripts
Create a MySQL or MariaDB view to show the aggregated size of each schema in the database:
See database size for more information.
CREATE OR REPLACE VIEW schema_size AS
SELECT table_schema
, round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM information_schema.tables
WHERE table_schema IN ('dv', 'da', 'hue')
GROUP BY table_schema
ORDER BY table_size_mb DESCCategory : Supporting Scripts
CREATE SEQUENCE seq_test
MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20
/
CREATE OR REPLACE FUNCTION func_seq_test (p_sequence in VARCHAR2) RETURN NUMBER IS
l_nextval NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select ' || p_sequence || '.nextval from dual'
INTO l_nextval;
RETURN l_nextval;
END;
/
CREATE OR REPLACE VIEW view_seq_test AS
SELECT func_seq_test('seq_test') seq_id
, dummy
FROM dual
/
SELECT * FROM view_seq_test
/
DROP SEQUENCE seq_test
/
DROP FUNCTION func_seq_test
/
DROP VIEW view_seq_test
/
Category : Supporting Scripts
Please see Install Oracle Java In Debian/ Ubuntu using apt-get for more information.
apt install -y software-properties-common apt-add-repository -y ppa:webupd8team/java apt update echo oracle-java8-installer shared/accepted-oracle-license-v1-1 select true | /usr/bin/debconf-set-selections apt install -y oracle-java8-installer
Category : Supporting Scripts
This script is used to set up the test table for the following blog posts:
DROP TABLE interval_test PURGE
/
CREATE TABLE interval_test
(
data_from_date DATE NOT NULL
, data_to_date DATE
, business_key NUMBER
, partition_column AS (TRUNC(data_to_date, 'MM') - 1) -- change this
)
PARTITION BY RANGE (partition_column) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION p_interval_test_0 VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
ENABLE ROW MOVEMENT
/
INSERT INTO interval_test (data_from_date, data_to_date, business_key)
SELECT DATE '2000-01-01' + (ROWNUM) data_from_date
, DATE '2000-01-01' + (ROWNUM + 1) data_to_date
, ROWNUM table_key
FROM dual
CONNECT BY LEVEL <= 365
/
COMMIT
/
BEGIN
Sys.DBMS_Stats.Gather_Table_Stats(
Ownname => null
, Tabname => 'interval_test'
, Estimate_Percent => 100
, Degree => Sys.DBMS_Stats.Auto_Degree
, Granularity => 'AUTO'
, Cascade => Sys.DBMS_Stats.Auto_Cascade
, Force => true
);
END;
/
EXPLAIN PLAN FOR SELECT * FROM interval_test WHERE DATE '2001-01-01' BETWEEN data_from_date AND data_to_date
/
SELECT plan_table_output FROM TABLE(dbms_xplan.display(format=>'ALL -PROJECTION'))
/