Silver Pockets Full -

Yesterday, I came across Chet Justice’s blog post on the phenomenon of “Silver Pockets Full”. Interesting!!!
It claims that – a month with 5 Fridays and 5 Saturdays and 5 Sundays – occurs once in every 823 years.

So, the problem statement was to find out the correctness of this supposition. And, also to find out the next months which satisfies this criteria.

I believe, it can be done through any language (technical, of course) be it – SQL or Java or PERL or Python or etc. I prefer SQL and got a query ready.

Let’s try it out.

with t1 as
(
	select trunc(sysdate,'yyyy')+level-1 lvl
	from dual
	connect by level <= 50000
)
---
, t2 as
(
	select 
		to_char(lvl,'yyyymm') mx, 
		sum(decode(to_char(lvl,'dy'),'sun',1,'sat',1,'fri',1,0)) count_x
	from t1
	group by to_char(lvl,'yyyymm')
)
---
select to_char(to_date(mx,'yyyymm'),'yyyy-Month') yyyy_Month
from t2
where count_x = 15
order by mx;

Output:

YYYY_Month
------------
2014-August
2015-May
2016-January
2016-July
2017-December
2019-March
2020-May
2021-January
2021-October
2022-July
2023-December
2024-March
2025-August
2026-May
2027-January
2027-October
2028-December

... rest removed for brevity

138 rows selected 

My favorites -

General concepts -

1. Why there is implicit commit before and after executing DDL Statements
2. Implicit commit with DDLs – AskTom
3. Why not use Redo log for consistent read
4. Difference between redo logs and undo tablespace
5. What does redo log files hold?
6. Why Hash Join goes slower?
7. TKPROF: high SQL*Net message from client + excessive parsing
8. What is the meaning of SQL*Net message from client – Ask Tom
9. Will Oracle lock the whole table while performing a DML statement or just the row? – Stack Overflow
 
10
 SQL and PL/SQL – 

1.
2.

How did you learn… Cary Millsap ?

One not-so-fine evening, while at work, I was stuck with a SQL performance issue. Over the internet, I found an article by Cary Millsap titled Why You Should Focus on LIOs Instead of PIOs. Going through the content, I got some further doubts. Finally, as always, I resorted to the OTN forums and posted my doubts.

For those who are not aware about OTN, it stands for Oracle Technology Network. A technical discussion forum where technologists and enthusiasts meet and discuss their concerns.

Back to the topic…

In no time, I was helped by some of the most renowned database experts including the author himself, Cary. :)

After my doubts were cleared, overwhelmed by his knowledge and efforts in helping me, I contacted him. Then I asked him few further technical doubts and finally, a peculiar (silly!) question: “How did you learn so much stuff about Oracle”. :P

And, here was his reply – http://carymillsap.blogspot.in/2014/02/how-did-you-learn-so-much-stuff-about.html

A complete post on his blog. :) Thanks a lot, Cary!!!

Learning about ASH and AWR -

Lately, I have become a l’il more serious and concerned about SQL (and database) performance tuning, which at some point or the other, becomes an absolute necessity.
So, to take the tuning activity to the next higher level, beyond the execution plans, I thought of delving into the concepts of AWR (Automatic Workload Repository) and ASH (Active Session History).

I believe, this is the point, where a Developer meets a DBA course of activity.

So, my quest for some good starter material, halted on this amazing video tutorial on YouTube “Performance Tuning with ASH and AWR data” by Kellyn Pot’vin, a former Oracle ACE Director.

Towrads the end, she advised to go through some nice resources/blogs, and I have noted the same here for my future reference purpose:

1) Karl Arao — http://karlarao.wordpress.com

2) Tyler Muth — http://tylermuth.wordpress.com

3) Kyle Hailey, John Beresniewicz, Graham Wood — http://ashmasters.com

4) Kellyn Pot’vin — http://dbakevlar.com/2011/02/for-the-love-of-awr-and-ash/

Really appreciate Kellyn for the wonderful video tutorial. Thanks!!!

What is SYS_CONTEXT ??

SYS_CONTEXT is an Oracle function which can be used to retrieve useful information about the Oracle environment. This can be used both in SQL and PL/SQL.

WITH user_environment(param, param_value) AS
(
select 'AUDITED_CURSORID', SYS_CONTEXT('USERENV','AUDITED_CURSORID') from dual UNION ALL
select 'AUTHENTICATED_IDENTITY', SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY') from dual UNION ALL
select 'AUTHENTICATION_DATA', SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') from dual UNION ALL
select 'AUTHENTICATION_METHOD', SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD') from dual UNION ALL
select 'BG_JOB_ID', SYS_CONTEXT('USERENV','BG_JOB_ID') from dual UNION ALL
select 'CLIENT_IDENTIFIER', SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') from dual UNION ALL
select 'CLIENT_INFO', SYS_CONTEXT('USERENV','CLIENT_INFO') from dual UNION ALL
select 'CURRENT_BIND', SYS_CONTEXT('USERENV','CURRENT_BIND') from dual UNION ALL
select 'CURRENT_EDITION_ID', SYS_CONTEXT('USERENV','CURRENT_EDITION_ID') from dual UNION ALL
select 'CURRENT_EDITION_NAME', SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') from dual UNION ALL
select 'CURRENT_SCHEMA', SYS_CONTEXT('USERENV','CURRENT_SCHEMA') from dual UNION ALL
select 'CURRENT_SCHEMAID', SYS_CONTEXT('USERENV','CURRENT_SCHEMAID') from dual UNION ALL
select 'CURRENT_SQL', SYS_CONTEXT('USERENV','CURRENT_SQL') from dual UNION ALL
select 'CURRENT_SQLn', SYS_CONTEXT('USERENV','CURRENT_SQLn') from dual UNION ALL
select 'CURRENT_SQL_LENGTH', SYS_CONTEXT('USERENV','CURRENT_SQL_LENGTH') from dual UNION ALL
select 'CURRENT_USER', SYS_CONTEXT('USERENV','CURRENT_USER') from dual UNION ALL
select 'CURRENT_USERID', SYS_CONTEXT('USERENV','CURRENT_USERID') from dual UNION ALL
select 'DATABASE_ROLE', SYS_CONTEXT('USERENV','DATABASE_ROLE') from dual UNION ALL
select 'DB_DOMAIN', SYS_CONTEXT('USERENV','DB_DOMAIN') from dual UNION ALL
select 'DB_NAME', SYS_CONTEXT('USERENV','DB_NAME') from dual UNION ALL
select 'DB_UNIQUE_NAME', SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') from dual UNION ALL
select 'DBLINK_INFO', SYS_CONTEXT('USERENV','DBLINK_INFO') from dual UNION ALL
select 'ENTRYID', SYS_CONTEXT('USERENV','ENTRYID') from dual UNION ALL
select 'ENTERPRISE_IDENTITY', SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY') from dual UNION ALL
select 'FG_JOB_ID', SYS_CONTEXT('USERENV','FG_JOB_ID') from dual UNION ALL
select 'GLOBAL_CONTEXT_MEMORY', SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') from dual UNION ALL
select 'GLOBAL_UID', SYS_CONTEXT('USERENV','GLOBAL_UID') from dual UNION ALL
select 'HOST', SYS_CONTEXT('USERENV','HOST') from dual UNION ALL
select 'IDENTIFICATION_TYPE', SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE') from dual UNION ALL
select 'INSTANCE', SYS_CONTEXT('USERENV','INSTANCE') from dual UNION ALL
select 'INSTANCE_NAME', SYS_CONTEXT('USERENV','INSTANCE_NAME') from dual UNION ALL
select 'IP_ADDRESS', SYS_CONTEXT('USERENV','IP_ADDRESS') from dual UNION ALL
select 'ISDBA', SYS_CONTEXT('USERENV','ISDBA') from dual UNION ALL
select 'LANG', SYS_CONTEXT('USERENV','LANG') from dual UNION ALL
select 'LANGUAGE', SYS_CONTEXT('USERENV','LANGUAGE') from dual UNION ALL
select 'MODULE', SYS_CONTEXT('USERENV','MODULE') from dual UNION ALL
select 'NETWORK_PROTOCOL', SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') from dual UNION ALL
select 'NLS_CALENDAR', SYS_CONTEXT('USERENV','NLS_CALENDAR') from dual UNION ALL
select 'NLS_CURRENCY', SYS_CONTEXT('USERENV','NLS_CURRENCY') from dual UNION ALL
select 'NLS_DATE_FORMAT', SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') from dual UNION ALL
select 'NLS_DATE_LANGUAGE', SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') from dual UNION ALL
select 'NLS_SORT', SYS_CONTEXT('USERENV','NLS_SORT') from dual UNION ALL
select 'NLS_TERRITORY', SYS_CONTEXT('USERENV','NLS_TERRITORY') from dual UNION ALL
select 'OS_USER', SYS_CONTEXT('USERENV','OS_USER') from dual UNION ALL
select 'POLICY_INVOKER', SYS_CONTEXT('USERENV','POLICY_INVOKER') from dual UNION ALL
select 'PROXY_ENTERPRISE_IDENTITY',SYS_CONTEXT('USERENV','PROXY_ENTERPRISE_IDENTITY') from dual UNION ALL
select 'PROXY_USER', SYS_CONTEXT('USERENV','PROXY_USER') from dual UNION ALL
select 'PROXY_USERID', SYS_CONTEXT('USERENV','PROXY_USERID') from dual UNION ALL
select 'SERVER_HOST', SYS_CONTEXT('USERENV','SERVER_HOST') from dual UNION ALL
select 'SERVER_NAME', SYS_CONTEXT('USERENV','SERVICE_NAME') from dual UNION ALL
select 'SESSION_EDITION_ID', SYS_CONTEXT('USERENV','SESSION_EDITION_ID') from dual UNION ALL
select 'SESSION_EDITION_NAME', SYS_CONTEXT('USERENV','SESSION_EDITION_NAME') from dual UNION ALL
select 'SESSION_USER', SYS_CONTEXT('USERENV','SESSION_USER') from dual UNION ALL
select 'SESSION_USERID', SYS_CONTEXT('USERENV','SESSION_USERID') from dual UNION ALL
select 'SESSIONID', SYS_CONTEXT('USERENV','SESSIONID') from dual UNION ALL
select 'SID', SYS_CONTEXT('USERENV','SID') from dual UNION ALL
select 'STATEMENTID', SYS_CONTEXT('USERENV','STATEMENTID') from dual UNION ALL
select 'TERMINAL', SYS_CONTEXT('USERENV','TERMINAL') from dual
)
---------
SELECT 
   param, 
   param_value
FROM user_environment;

Indexes on any table -

This is a small query I always keep handy, given the table name as input, it lists down 
all the Indexes and its constituent column(s).


WITH indx AS(
	SELECT
		index_owner,
		index_name,
		table_name,
		LISTAGG(column_name, ', ') WITHIN GROUP (
			ORDER BY column_position ASC
		) lst 
	FROM
		all_ind_columns
	WHERE
		table_name IN ('TABLE_1','TABLE_2') -->>-- input: Table name(s)
	GROUP BY
		index_owner,
		index_name,
		table_name
)
SELECT
	x.*,
	DECODE(REGEXP_COUNT(x.lst,','),
		0,'Normal Index',
		'Composite Index'
	) idx_type -->>-- identifies the Index is a Normal or Composite index
FROM indx x
ORDER BY x.table_name;

Utility to generate DB Object scripts -

Hi,

Caution: Work-in-Progress

Trying to develop an utility to generate DB Object scripts customized for my personal use.

This might fall in the category called *reinventing-the-wheel* as DBMS_METADATA package does a very similar work, but not same.

Let’s see how it goes:

DECLARE
	TYPE curtyp IS REF CURSOR;

	--------------------------------------- 
	--	declaring all Nested Table types 
	---------------------------------------
	TYPE colname_ntt IS TABLE OF all_tab_columns.column_name%TYPE;
	TYPE tabname_ntt IS TABLE OF all_tab_columns.table_name%TYPE;
	TYPE data_typ_len_ntt IS TABLE OF VARCHAR2(20);

	v_tab_name VARCHAR2(50 CHAR) := 'TABLE_1,TABLE_2,ranit_biswas';

	table_not_found EXCEPTION;
	---------------------------------------
	curs curtyp;
	cols colname_ntt;
	tabs tabname_ntt;
	data_typ_len data_typ_len_ntt; 
	---------------------------------------
	v_table_exists NUMBER := 0;
	

BEGIN

	FOR x IN (
	  SELECT RTRIM(REGEXP_SUBSTR(v_tab_name,'(,?)([^,]*)(,?)',1,LEVEL),',') v_tab_name1
	  FROM dual
	  CONNECT BY LEVEL &lt;= (LENGTH(v_tab_name)-LENGTH(REPLACE(v_tab_name,',')))+1
	)
	LOOP
		BEGIN
			SELECT COUNT(1) INTO v_table_exists
			FROM USER_TABLES
			WHERE table_name = x.v_tab_name1;

				IF(v_table_exists  0) THEN
					OPEN curs FOR
					SELECT 
						table_name, 
						column_name,
						data_type||'('||data_length||')' 
					FROM all_tab_columns
					WHERE table_name = x.v_tab_name1;

					FETCH curs 
					BULK COLLECT INTO tabs, cols, data_typ_len;
					CLOSE curs;

					DBMS_OUTPUT.PUT_LINE('PROMPT CREATE TABLE '||LOWER(x.v_tab_name1));
					DBMS_OUTPUT.PUT_LINE('CREATE TABLE '||LOWER(x.v_tab_name1)||'(');

					FOR i IN tabs.first .. tabs.last
					LOOP
						DBMS_OUTPUT.PUT_LINE(
							CHR(9)||CHR(9)||(CASE WHEN i  tabs.FIRST THEN ', ' END)||LOWER(cols(i))||
							CHR(9)||(
                CASE 
                  WHEN (data_typ_len(i) LIKE '%DATE%') THEN 'DATE' 
                  WHEN (data_typ_len(i) LIKE '%NUMBER%') THEN 'NUMBER'
                  ELSE data_typ_len(i) 
                END
              ) 
						);
					END LOOP;

					DBMS_OUTPUT.PUT_LINE(');'||CHR(10)||CHR(10));
				
				ELSE
					RAISE table_not_found;
				END IF;
		EXCEPTION
		WHEN table_not_found THEN
			DBMS_OUTPUT.PUT_LINE(
'
---+----------------------------------------------------------------------------------------------------------+
---+--'||CHR(9)||'Table: ['||UPPER(x.v_tab_name1)||'] is not found.
---+----------------------------------------------------------------------------------------------------------+');
		END;
	END LOOP;

END;

END;

But there are several pending action items which makes it *Work-in-Progress*.
– Have to merge the “Index Creation” logic and “Alter Table” to add Primary Keys.
– Foreign Key relationship

-----------------------------------------------------------
-- PRIMARY KEY
-----------------------------------------------------------
WITH pkey AS(
    SELECT 
      LOWER(table_name) tbl_name,
      LOWER(index_name) idx_name,
      LISTAGG(CHR(9)||LOWER(column_name),','||CHR(10)) WITHIN GROUP (ORDER BY column_position) lst
    FROM user_ind_columns uic
    WHERE 
      EXISTS(
        SELECT 1 FROM user_constraints uc
        WHERE 
          uic.index_name = uc.constraint_name
          AND uc.constraint_type = 'P'
      )
    GROUP BY table_name,index_name
)
SELECT 
'
ALTER TABLE '||tbl_name||'
ADD CONSTRAINT '||idx_name||' PRIMARY KEY('
||CHR(10)||LST||CHR(10)||
');'
FROM pkey
WHERE UPPER(tbl_name) = ''; 



-----------------------------------------------------------
-- INDEXES // to be re-structured
-----------------------------------------------------------
WITH pkey AS(
    SELECT 
      LOWER(table_name) tbl_name,
      LOWER(index_name) idx_name,
      LISTAGG(CHR(9)||LOWER(column_name),','||CHR(10)) WITHIN GROUP (ORDER BY column_position) lst
    FROM user_ind_columns uic
    WHERE 
      NOT EXISTS(
        SELECT 1 FROM user_constraints uc
        WHERE 
          uic.index_name = uc.constraint_name
          AND uc.constraint_type = 'P'
      )
    GROUP BY table_name,index_name
)
SELECT 
'
CREATE INDEX '||idx_name||CHR(10)||' ON '||tbl_name||' ( 
  '||lst||'
);'
FROM pkey
WHERE UPPER(tbl_name) = ''; 



-----------------------------------------------------------
-- FOREIGN KEYS // yet to start
-----------------------------------------------------------

Thanks and Regards,
— Ranit

Follow

Get every new post delivered to your Inbox.