Setup login.sql in sqlplus windows envrionment

it would be tiresome to enter environment variables each time you login to sqlplus, good thing is sqlplus allows us to use a script (login.sql) which has the environment variables defined each time we login (user profile)  https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_two.htm#i1133044 .  Once you define the sqlpath sqlplus looks for login.sql in the path directory and executes it once you login successful. This can be also configured at global level called site profile it allows the DBA to set default env for all the users. so when a user connects through sqlplus first the global site profile (glogin.sql) is executed and then the user profile (login.sql) is executed which is defined in SQLPATH environment variable in Windows

 

sqlpath

 

here %USERPROFILE% is an environment variable has default location of the user who is logged into the machine  C:\USERS\Username

we have to make sure we have login.sql available in that location

 

sql plus login sql

 

you can see below sqlprompt has changed to what we defined in login.sql  (‘&_user.@&_connect_identifier.>’)

sqlprompt sqlplus

 

 

Advertisements

Create Table Space and Users in 3 Steps


CREATE TABLESPACE sqljunkie 
logging 
datafile ‘C:\app\oradata\orcl\sqljunkie.dbf’ 
size 32m 
autoextend ON NEXT 32m 
maxsize 2048m extent management local;
CREATE undo TABLESPACE 
sqljunkie_undodatafile ‘C:\app\oradata\orcl\undo.dbf’
size 100M;
CREATE TEMPORARY TABLESPACE sqljunkie_temptempfile 
‘C:\app\oradata\orcl\sqljunkie_temp.dbf’ 
size 32m 
autoextend ON NEXT 32m 
maxsize 2048m extent management local;
CREATE USER sqljunkie 
IDENTIFIED BY sqljunkieDEFAULT 
TABLESPACE sqljunkie 
TEMPORARY TABLESPACE sqljunkie_temp;

Fix: invalid common user or role name ORACLE 12C

In four steps will fix the issue

Error at Command Line:16 Column:13
Error report:
SQL Error: ORA-65096: invalid common user or role name

Determine the current container

SELECT Sys_context (‘USERENV’, ‘CON_NAME’)FROM   dual;
SYS_CONTEXT(‘USERENV’,’CON_NAME’)
——————————————————
CDB$ROOT
List all the pluggable databases

select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDBORCL                        MOUNTED
PDB need to open read write atleast once to inegrate into CDB

ALTER PLUGGABLE DATABASE PDBORCL OPEN READ WRITE;
pluggable database is altered
Now PDB is ready to be used we just need to change the current session

ALTER SESSION SET CONTAINER = PDBORCL
Use this query to make sure you are in right container
SELECT Sys_context (‘USERENV’, ‘CON_NAME’)FROM dual;
 
CREATE USER sqljunkie IDENTIFIED BY sqljunkie DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

user SQLJUNKIE created.


GRANT CREATE PROCEDURE
, CREATE session
, CREATE TABLE
, CREATE type
, CREATE VIEW
, CREATE SYNONYM
, CREATE TRIGGER
, RESOURCE TO sqljunkie;
Once we opened database it runs as its own service we can verify this
lsnctl service modified
 if you are planning to connect using TNSnaming service make sure you have something like below in tnsnames.ora
PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)
pdb connection
there we go

Creating, querying, tuning hierarchical ROLAP cubes in Oracle

This post will explain how to  use  Oracle grouping functions to leverage ROLAP capability in Oracle

To begin with some introduction Multi dimensional data marts or an Enterprise data warehouse has become need than necessity as in the end data that drives the business

Having right staging area and presentation layer is very important, there are many Bi tools out there these tools helps the users to create multi dimensional OLAP cubes up on statging layer by MOLAP, ROLAP, HOLAP

indepth analysis of these storage methods are outside the scope of our discussion

ROLAP stands for Relational Online Analytical Processing

ROLAP features

  • ROLAP is considered to be more scalable in handling large data volumes, especially models with dimensions with very high cardinality (i.e., millions of members).
  • With a variety of data loading tools available, and the ability to fine tune the ETL code to the particular data model, load times are generally much shorter than with the automated MOLAP loads.
  • The data are stored in a standard relational database and can be accessed by any SQL reporting tool (the tool does not have to be an OLAP tool).
  • ROLAP tools are better at handling non-aggregatable facts (e.g., textual descriptions). MOLAP tools tend to suffer from slow performance when querying these elements.
  • By decoupling the data storage from the multi-dimensional model, it is possible to successfully model data that would not otherwise fit into a strict dimensional model.
  • The ROLAP approach can leverage database authorization controls such as row-level security, whereby the query results are filtered depending on preset criteria applied, for example, to a given user or group of users (SQL WHERE clause).

ROLAP simplifies the report and analysis development by 100 times when compared to MOLAP

Oracle Provides following grouping functions

  • ROLLUP
  • CUBE
  • GROUPING SETS

Rolap cube over view

In order to understand we will take a simple example a online retail bike selling we will have simple multidimentional data model with

Dimensions

  • dimgeog(loacation)
  • dimdate(Date)
  • dimproduct(Product)

Fact

  • Sales

oracle cube

lets look at the Dimensional model at staging layer

DATA MODELS

ROLLUP enables us to calculate multiple levels of subtotals across a specified group of dimensions(Hierarchy) , allowing us to create subtotals for different levels for hierarchy’s  in dimensions

ROLLUP creates subtotals for n+1 levels of subtotals where n is the number of grouping columns

Simple Rollup

Simple ROLAP example

rollup examples1

How ROLLUP works

rollup examples2

Understanding NULLS in ROLLUP , Readability and Querying, controlling grouping

NULLS returned by GORUP BY extensions are not always always NULL, a NULL indicate that its a subtotal, in order to differentiate against a traditional NULL value

Oracle provides two functions GROUPING and GROUPING_ID

GROUPING(Column name)  will result 1 if a row is subtotaled on that given column name  and returns 0 if its null value or if row is not subtotaled

Using DECODE and GROUPING  functions we can rewrite above query for better readability

rollup examples3

rollup examples4

In order to build a complete cube we have to also need to include other dimensions as we discussed above geog, product, So we will add to these aggregation levels to our existing query

rollup examples5

SELECT DECODE (grouping(dt.fiscalyear),1,’All years’,dt.fiscalyear) AS YEAR,
DECODE (grouping(dt.fiscalquarter),1,’All Quarters’ ,dt.fiscalquarter) AS quarter,

DECODE (grouping(dt.fiscalsemester),1,’All Semesters’ ,dt.fiscalsemester) AS semister,

DECODE (grouping(dt.weeknumberofyear),1,’All Weeks’ ,dt.weeknumberofyear) AS weeknmyr,
DECODE (grouping(dg.englishcountryregionname),1,’All’,dg.englishcountryregionname) AS country,
DECODE (grouping(dg.stateprovincename),1,’All State”s’ ,dg.stateprovincename) AS state,
DECODE (grouping(dg.city),1,’All City”s’ ,dg.city) AS city,
DECODE (grouping(dp.englishproductname ),1,’All Products’,dp.englishproductname) AS product,
DECODE (grouping(prsubcat.englishproductsubcategoryname),1,’All Categories’
, prsubcat.englishproductsubcategoryname) AS productsubcat,
SUM( fs.salesamount) AS sales,
GROUPING_ID(fiscalyear, dt.fiscalquarter,dt.fiscalsemester
,dt.weeknumberofyear,englishcountryregionname,stateprovincename,city) AS GROUPID
FROM factsales fs
INNER JOIN dimdate dt
ON fs.orderdatekey = dt.datekey
INNER JOIN dimgeog dg
ON dg.geographykey = fs.salesterritorykey
INNER JOIN dimproduct dp
ON dp.productkey = fs.productkey
INNER JOIN dimproductsubcat prsubcat
ON prsubcat.productsubcategorykey = dp.productsubcategorykey
WHERE dp.englishproductname IN (‘Road Tire Tube’)
GROUP BY rollup(fiscalyear, dt.fiscalquarter,dt.fiscalsemester,dt.weeknumberofyear),
ROLLUP( prsubcat.englishproductsubcategoryname,dp.englishproductname),
ROLLUP(englishcountryregionname,stateprovincename,city);

Now we have the query we have build a cube using Materialized views

First step is to build materialized views logs

Second build materialized view

Script can be download from here

https://www.dropbox.com/s/9eljxv6p91s8hkz/materialize%20views.sql

oralce materialized view

Now we have the ROLAP cube ready we can query it using native sql

Querying ROLAP cube can be leveraged using GROUPING_ID output

ROLAP cube 1

Using grouping_id to query to ROLAP cube

Q) Retrieve total sales for all years  and country “Australia”, all city’s all states  for “Road Tire Tube”

To find group id

GROUPING_ID(FISCALYEAR, FISCALQUARTER, FISCALSEMESTER, WEEKNUMBER,COUNTRY, STATE, CITY,PRODUCTSUBCATEGORY, PRODUCTNAME)

1                                 1                                          1                                     1                    0                     1             1                         0                                               1

groupid query 1

Q)  Total Sales in 2008 , for Australia  and state New South Wales all city’s

groupid query 2

GROUPING SETS 

Grouping sets allow selectively group in to sets and can also be used with ROLLUP to customize result

grouping sets

grouping sets equavalent

How to get current date and time in PL/SQL and TSQL

–SQL Server 2008
SELECT ‘SYSDATETIME’ AS FunctionName, SYSDATETIME() AS DateTimeFormat
UNION ALL
SELECT ‘SYSDATETIMEOFFSET’, SYSDATETIMEOFFSET()
UNION ALL
SELECT ‘SYSUTCDATETIME’, SYSUTCDATETIME()
UNION ALL
SELECT ‘CURRENT_TIMESTAMP’, CURRENT_TIMESTAMP
UNION ALL
SELECT ‘GETDATE’, GETDATE()
UNION ALL
SELECT ‘GETUTCDATE’, GETUTCDATE()

.

.

.

.

–Oracle PL/SQL
select sysdate from dual
union all
select current_date from dual