References

1) www.orafaq.com/wiki/Main_Page

2) www.orafaq.com/wiki/Scripts

3) www.orafaq.com/wiki/SQL_FAQ

OS-Version

How to Identify OS or Oracle 64 bit or 32 bit

select * from v$version;

---------------------------------------------------------------------------

MOVE

Re: alter table move

Processing the code


select * from tab where P1 = 110

last a few seconds. After the code


alter table tab enable row movement
alter table tab move

the select-code above last about an hour! I thought this might have to do with the index (does the move-statement affect the index?), but when I run the code


create index Ix_P1 on tab (P1)

I get the ORA-00955: name is already used by an existing object.


Re: alter table move


When you do alter table .. move all the indexes goes into unusable state.
You need to rebuild all the indexes of the table after issuing alter table move commands....

SQL> create table dummy(x int);
Table created.

SQL> create index dummy_idx on dummy(x);

Index created.

SQL> insert into dummy values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> alter table dummy move;

Table altered.

SQL> select index_name,status from user_indexes where table_name = 'DUMMY';

INDEX_NAME STATUS
------------------------------ --------
DUMMY_IDX UNUSABLE

Alphabets,Level, Analytic Query

Alphabets

WITH AlphaBet AS
( SELECT CHR((ROWNUM + 64)) AS Character
FROM DUAL CONNECT BY level <= 157)
SELECT * FROM AlphaBet;

------------------------------------------------------------------------
Some Analytic Functions

SELECT G.*,ROW_NUMBER() OVER(PARTITION BY G.DEPTNO ORDER BY G.EMPNO)
FROM EMP G;

SELECT G.DEPTNO,G.ENAME,SUM(G.SAL) OVER (PARTITION BY G.DEPTNO ORDER BY G.DEPTNO) FROM EMP G ;

SELECT FIRST_VALUE(G.EMPNO) OVER() FROM EMP G;

---------------------------------------------------------------------------
Level Query


select level,lpad(' ',3*(level)) || VC_designation s,nn_desgcode dsg,nn_repdesgcode rcd,
SYS_CONNECT_BY_path (nn_repdesgcode,'/') op,
substr(SYS_CONNECT_BY_path (nn_repdesgcode,'/'),3,
length((SYS_CONNECT_BY_path (nn_repdesgcode,'/')))) hjdd
from hr_designation_mast where nn_repdesgcode is not null
start with nn_repdesgcode is not null
connect by prior nn_desgcode=nn_repdesgcode

Calender,Dates,Objects created

Calender of a year


select lpad( Month, 20-(20-length(month))/2 ) month
, "Su" , "Mo" , "Tu" , "We" , "Th" , "Fr" , "Sa"
from ( select to_char(dt,'fmMonthfm YYYY') month
, to_char(dt+1,'iw') week
, max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su"
, max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo"
, max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu"
, max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We"
, max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th"
, max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr"
, max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
from ( select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12)
-
trunc(sysdate,'y')
)
group by to_char(dt,'fmMonthfm YYYY')
, to_char( dt+1, 'iw' )
)
order by to_date( month, 'Month YYYY' )
, to_number(week)

--------------------------------------------------
To get all Dates in a Year

select mydate,to_char(mydate,'Day') from(
select (level-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from dual
connect by level <=to_date('31-12-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy')-to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') +1)

OR


select mydate,to_char(mydate,'Day') from(
select (rownum-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from all_objects
where
rownum <=to_date('31-12-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') - to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') +1)

The above query assumes that all_objects returns at least 365 records.

----------------------------------------------------------------------------
Number of objects created in a month of year

select to_char(trunc(created,'MONTH'),'YYYY-MON') "Month", count(*) "Nb"
from user_objects group by trunc(created,'MONTH')
order by trunc(created,'MONTH');

Lock,Month,Week Query

Query to identify Locked Tables

SELECT e.*, (select terminal from v$session r where e.SID = r.SID) terminal,
(select name from sys.obj$ where obj# = e.ID1) objname
FROM sys.v_$lock e

-----------------------------------------------------------------
To get all months in a year

select to_char(add_months(TRUNC(SYSDATE,'Y'), level-1), 'Month') from dual
connect by level <= months_between(sysdate, TRUNC(SYSDATE,'Y'))+1
order by add_months(TRUNC(SYSDATE,'Y'), level-1);

select TO_CHAR(TO_DATE(rownum,'MM'),'Mon') Month_Val from dual connect by rownum <= 12;

-----------------------------------------------------------------
To get Weeks in a year

select trunc(dte,'W') week_start,trunc(dte,'W')+6 week_end,to_char(dte,'Month') Mnth
,to_char(dte,'YYYY') Yr
from (select trunc(sysdate,'YYYY')+(7*(level-1)) dte
from dual
connect by level <= ceil((add_months(trunc(sysdate,'YYYY'),12)-trunc(sysdate,'YYYY'
))/7));
-----------------------------------------------------------------