Time Dimension – SQL Query
This query generates a relational table storing the information needed to create an OLAP time dimension.
CREATE TABLE TIMES AS
SELECT
— DAY LEVEL
TIME_ID AS DAY_ID,
INITCAP(TO_CHAR(TIME_ID,’FMMONTH DD, YYYY’)) AS DAY_DESC,
INITCAP(TO_CHAR(TIME_ID, ‘FMDAY’)) AS DAY_NAME,
TO_NUMBER(TO_CHAR(TIME_ID – 1, ‘D’)) AS DAY_OF_WEEK,
TO_NUMBER(TO_CHAR(TIME_ID, ‘DD’)) AS DAY_OF_MONTH,
TO_NUMBER(TO_CHAR(TIME_ID, ‘DDD’)) AS DAY_OF_YEAR,
1 AS DAYS_IN_DAY,— MONTH LEVEL
TO_CHAR(TIME_ID, ‘YYYY”-M”MM’) AS MONTH_ID,
TO_CHAR(TIME_ID, ‘FMMONTH YYYY’) AS MONTH_DESC,
DECODE(MOD(TO_NUMBER(TO_CHAR(TIME_ID, ‘MM’)), 4), 0, 4, MOD(TO_NUMBER(TO_CHAR(TIME_ID, ‘MM’)), 4)) AS MONTH_OF_QUARTER,
TO_NUMBER(TO_CHAR(TIME_ID, ‘MM’)) AS MONTH_OF_YEAR,
TO_CHAR(TIME_ID, ‘FMMONTH’) AS MONTH_NAME,
LAST_DAY(TIME_ID) AS END_OF_MONTH,
TO_CHAR(LAST_DAY(TIME_ID),’DD’) AS DAYS_IN_MONTH,— QUARTER LEVEL
TO_CHAR(TIME_ID, ‘YYYY”-Q”Q’) AS QUARTER_ID,
INITCAP(TO_CHAR(TIME_ID, ‘FMQTH “QUARTER,” YYYY’)) AS QUARTER_DESC,
TO_NUMBER(TO_CHAR(TIME_ID, ‘Q’)) AS QUARTER_OF_YEAR,
TRUNC(ADD_MONTHS(TIME_ID,3), ‘Q’) – 1 AS END_OF_QUARTER,
(TRUNC(ADD_MONTHS(TIME_ID,3), ‘Q’) – 1) – (TRUNC(TIME_ID, ‘Q’) – 1) AS DAYS_IN_QUARTER,— YEAR LEVEL
TO_NUMBER(TO_CHAR(TIME_ID, ‘YYYY’)) AS YEAR_ID,
(TRUNC(ADD_MONTHS(TIME_ID,12), ‘YYYY’) – 1) – (TRUNC(TIME_ID, ‘YYYY’) – 1) AS DAYS_IN_YEAR,
TRUNC(ADD_MONTHS(TIME_ID,12), ‘YYYY’) – 1 AS END_OF_YEAR,— THIS IS THE SAME FOR ALL WEEKS
7 AS DAYS_IN_WEEK,— CALENDAR WEEK LEVEL
TO_CHAR(TIME_ID, ‘IYYY’) || ‘-CW’ || TO_CHAR(TIME_ID, ‘IW’) AS CAL_WEEK_ID,
INITCAP(TO_CHAR(TIME_ID, ‘FMIWTH “WEEK OF” IYYY’)) || ‘, ENDING ‘ || TO_CHAR(TRUNC(TIME_ID + 7, ‘IW’) – 1, ‘FMMONTH DD, YYYY’) AS CAL_WEEK_DESC,
TO_NUMBER(TO_CHAR(TIME_ID, ‘IW’)) AS CAL_WEEK_OF_YEAR,
TRUNC(TIME_ID + 7, ‘IW’) – 1 AS END_OF_CAL_WEEKFROM (SELECT TO_DATE(’01/01/2008′,’MM/DD/YYYY’) + ROWNUM – 1 AS TIME_ID
FROM ALL_OBJECTS
WHERE ROWNUM <= 1492) — ADD ONE MORE DAY BY SUBTRACTING MIN-MAX DATES
ORDER BY TIME_ID;
Secure Database Passwords in an Oracle Wallet
Read article:
http://www.idevelopment.info/data/Oracle/DBA_tips/Security/SEC_15.shtml
Moreover,
SQL> connect /@orcl
impdp /@orcl
Oracle Datapump Parameter REMAP_SCHEMA
Loads all objects from the source schema into a target schema.
Syntax
REMAP_SCHEMA=source_schema:target_schema
Suppose that you execute the following Export and Import commands to remap the hr schema into the scott schema:
expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp
impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott
In this example, if user scott already exists before the import, then the Import REMAP_SCHEMA command will add objects from the hr schema into the existing scott schema. You can connect to the scott schema after the import by using the existing password (without resetting it).If user scott does not exist before you execute the import operation, import automatically creates it with an unusable password. This is possible because the dump file, hr.dmp, was created by SYSTEM, which has the privileges necessary to create a dump file that contains the metadata needed to create a schema. However, you cannot connect to scott on completion of the import, unless you reset the password forscott on the target database after the import completes.
Send Report via EMail Attachment in Oracle Forms 10g
DECLARE
report_id REPORT_OBJECT := find_report_object(‘MYREPORT’);
report_message VARCHAR2(100) :=”;
rep_status VARCHAR2(100) :=”;
v_error Exception;
thelist paramlist;
BEGIN
— Create parameters list
thelist:= Get_Parameter_List(‘rep_paramlist’);
IF NOT Id_Null (thelist) THEN
DESTROY_PARAMETER_LIST(thelist);
END IF;
thelist := Create_Parameter_List (‘rep_paramlist’);
— Add report paremeters
ADD_PARAMETER(thelist, ‘FROM’,TEXT_PARAMETER, ‘abc@abc.com’);
ADD_PARAMETER(thelist, ‘SUBJECT’,TEXT_PARAMETER, ‘MRF NO:’||:GLOBAL.SIFNO);
ADD_PARAMETER(thelist, ‘PROJECT_NBR’,TEXT_PARAMETER, 968404);
— Set report object properties
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE, SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME, ‘C:\workingDirectory\MRF.RDF’);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER, ‘rep_XP_oracleasFRM’);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE, MAIL );
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT, ‘PDF’);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_OTHER,’paramform=no mrfno=’||:GLOBAL.SIFNO);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESNAME,’abc@abc.com’ );
— Check report status
report_message := run_report_object (report_id, thelist);
rep_status := report_object_status (report_message);
BEGIN
WHILE
rep_status in (‘RUNNING’,’OPENING_REPORT’,’ENQUEUED’)
LOOP
rep_status := report_object_status(report_message);
END LOOP;
EXCEPTION
WHEN v_error then
message(‘Error in sending Action Checklist email.’|| rep_status);
END;
END;
Configure Oracle Application Express (APEX) in Oracle 11gR2
In order to access the APEX application, either the embedded PL/SQL gateway or Oracle HTTP server with mod_plsql is needed. For simplicity, I’ve decided to go with the former. By using the embedded PL/SQL gateway, it will run using the Oracle XML DB HTTP server which is already in Oracle database, so there is no need to install a separate HTTP server. The Oracle’s document here explains about this as well as provides the detailed information on the post-installation.
1. To configure the embedded PL/SQL gateway:
Go to the $ORACLE_HOME/apex directory.
2. Use SQL/Plus to connect as SYS to 11g database where APEX is installed.
SYS AS SYSDBA@DB11G> @apxconf
PORT———-8080
Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value
Enter a password for the ADMIN user [] admin_password
Enter a port for the XDB HTTP listener [ 8080]
…changing HTTP Port
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.
…changing password for ADMIN
PL/SQL procedure successfully completed.
Commit complete.
3. Unlock the ANONYMOUS account.
SYS AS SYSDBA@ DB11G > ALTER USER ANONYMOUS ACCOUNT UNLOCK;
User altered.
4. Enable Oracle XML DB HTTP server
SYS AS SYSDBA@ DB11G > EXEC DBMS_XDB.SETHTTPPORT(8080);
PL/SQL procedure successfully completed.
SYS AS SYSDBA@ DB11G > COMMIT;
Commit complete.
5. We’re now ready to access APEX.
http://host:port/apex/apex_admin — for admin page
Port in this case is 8080 which is the default.
Upgrade Oracle Database from 10g to 11g with Data Pump
As described in “Oracle Database Upgrade Guide 11g Release 1 (11.1)“, there are three upgrade methods offered while you want to upgrade database from 10g to 11g.
- Database Upgrade Assistant (DBUA)
- Manual Upgrade (Oracle provided scripts)
- Export/Import (exp/imp or expdp/impdp)
Besides benefits mentioned in above upgrade document, I’m thinking we are beneficial from using export/import approach. The reasons we decided using this method are:
- We want to set up identical testing databases on development server first
- We want to keep original production databases unchanged anyway for safety reason
With this approach, we first installed Oracle 11g software on development server and created empty database by using provided standard template New_Database.dbt, which could be found at $ORACLE_HOME/assistants/dbca/templates. After that, we followed the following steps to move data from 10g database to newly created 11g database.
Step 1: Pre-create tablespaces on target 11g database
- Using the Generate_Create_Tablespaces.sql to generate tablespace script
- Editing generated script to remove entries for SYSTEM, USERS tablespaces
Step 2: Export full database of source 10g database
expdp system@database directory=DATA_PUMP_DIR full=y dumpfile=export.dmp logfile=export.log
Step 3: Copy dumpfile over network to development server
Step 4: Grant IMPORT_FULL_DATABASE system privilege to user SYSTEM of target 11g database
Step 5: Import full database to target 11g database
Note: If you have co-existing oracle home/version on development server, please make sure switching to Oracle 11g environment, which will assure to use data pump at 11g version to import data.
Impdp system@database directory=DATA_PUMP_DIR full=y dumpfile=export.dmp logfile=import.log
