Tuesday 26 October 2010

Order by Month


Sometime we stuck to get our desire output.So its good practice we kept such information where its easily available.
Good Luck.
e.g.
SELECT TO_CHAR (TRUNC (dateordered, 'mm'), 'Month'),
MEDIAN (dateshipped - dateordered)
FROM orders
GROUP BY TRUNC (dateordered, 'mm')

ORDER BY TRUNC (dateordered, 'mm');

e.g.
select a.id,a.name,TO_CHAR(trunc(ah.date_viewed,'mm'),'Mon-yyyy') "Month Downloaded",count(*) "No of times Viewed"
from articleviewhistory ah,article a
where ah.article_id = a.id
and ah.article_id in (1085,984,961,900,638,630,628,624,613,611)
group by a.id, a.name, trunc(ah.date_viewed,'mm')
order by 2,trunc(ah.date_viewed,'mm');


e.g.
select to_char(day,'MON-YYYY'), sum(daily_growth_mb)
from db_growth
group by to_char(day,'MON-YYYY')
order by to_date(to_char(day,'MON-YYYY'),'MM-YYYY') desc;
or best one

select to_char(trunc(date_created,'MM'),'MON-YYYY'),count(*) from twitter
group by trunc(date_created,'MM')
order by trunc(date_created,'MM')

Wednesday 13 October 2010

Access Control List (ACL) Problems when using the UTL_TCP Package for FTP in Oracle 11g

I am sure you have read this. But pay attention that it is address to:
- particular IP address or host name
- particular Oracle user

First, create an ACL:

begin
        dbms_network_acl_admin.create_acl (
                acl             => 'utlpkg.xml',
                description     => 'Normal Access',
                principal       => 'CONNECT',
                is_grant        => TRUE,
                privilege       => 'connect',
                start_date      => null,
                end_date        => null
        );
end;

Here the parameter principal => 'CONNECT' indicates that the ACL applies to the CONNECT role. You can define a user or a role here. The ACL is created as a file called utlpkg.xml. 
After the creation, you can check to make sure the ACL was added:

SELECT any_path
FROM resource_view
WHERE any_path like '/sys/acls/%.xml';

The output is: 
ANY_PATH
----------------------------------------------------------------------------
/sys/acls/ANONYMOUS/ANONYMOUS3553d2be53ca40e040a8c0680777c_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f93feb8dde040a8c068075b7_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f944b8dde040a8c068075b7_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f948b8dde040a8c068075b7_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f94cb8dde040a8c068075b7_acl.xml
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/ro_all_acl.xml
/sys/acls/ro_anonymous_acl.xml
/sys/acls/utlpkg.xml

Note the last line in the output, which shows the ACL you just created. Next, add a privilege to this ACL. In this example, you are trying to limit this ACL to the user SCOTT. You can also define start and end dates. 
begin
  dbms_network_acl_admin.add_privilege ( 
  acl             => 'utlpkg.xml',
  principal       => 'SCOTT',
  is_grant       => TRUE, 
  privilege       => 'connect', 
  start_date       => null, 
  end_date       => null); 
end;

Assign hosts and other details that will be subject to this ACL: 
begin
  dbms_network_acl_admin.assign_acl (
  acl => 'utlpkg.xml',
  host => '
www.proligence.com',
  lower_port => 22,
  upper_port => 55);
end;

In this example, you are specifying that "the user SCOTT can call only the host 
www.proligence.com and only for the ports 22 through 55, and not outside it." Now, let's try it: 
SQL> grant execute on utl_http to scott
  2  /
 
Grant succeeded.
 
SQL> conn scott/tiger
Connected.
SQL> select utl_http.request('
http://www.proligence.com') from dual;
select utl_http.request('
http://www.proligence.com') from dual
       *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1

Note the error "ORA-24247: network access denied by access control list (ACL)." The user called the http server on port 80, which is outside the allowed range 22-55. Therefore the action was prevented. 
Now, add another rule to allow the communication:

  1  begin
  2    dbms_network_acl_admin.assign_acl (
  3    acl => 'utlpkg.xml',
  4    host => '
www.proligence.com',
  5    lower_port => 1,
  6    upper_port => 10000);
  7* end;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> conn scott/tiger
Connected.
SQL> select utl_http.request('
http://www.proligence.com') from dual;
 
UTL_HTTP.REQUEST('
HTTP://WWW.PROLIGENCE.COM')
--------------------------------------------------------------------------------
</iframe><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML><HEAD><TITLE>Proligence Home</TITLE>
<META http-equiv=Content-Language content=en-us>

Tuesday 12 October 2010

Saturday 9 October 2010

ORA-12162: TNS:net service name is incorrectly specified

[oracle@Grid-EM ~]$ echo $ORACLE_SID

oraworld

[oracle@Grid-EM ~]$ echo $ORACLE_HOME

/oracle/product/11.2.0/dbhome_1

[oracle@Grid-EM ~]$ echo $TNS_ADMIN

[oracle@Grid-EM ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 9 13:59:01 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> conn / as sysdba

ERROR:

ORA-12162: TNS:net service name is incorrectly specified

]$ cat initoraworld.ora

###########################################

# Database Identification

###########################################

db_domain=Grid-Em.co.uk

db_name=oraworld


*************** parameter file is case sensitive it follow ORACLE_SID variable value.

it means if ORACLE_SID=ORAWORLD then parameter file should be initORAWORLD.ora , spfileORAWORLD.ora

and if ORACLE_SID=oraworld then parameter file should be initoraworld.ora ,spfileoraworld.ora ******************************************************

[oracle@Grid-EM ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-OCT-2010 14:06:39

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Grid-EM.co.uk)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 09-OCT-2010 13:13:37

Uptime 0 days 0 hr. 53 min. 2 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /oracle/diag/tnslsnr/Grid-EM/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Grid-EM.co.uk)(PORT=1521)))

Services Summary...

Service "Oracle8" has 1 instance(s).

Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...

Service "oraworld" has 1 instance(s).

Instance "oraworld", status UNKNOWN, has 1 handler(s) for this service...

Service "oraworld.Grid-Em.co.uk" has 1 instance(s).

Instance "oraworld", status READY, has 1 handler(s) for this service...

Service "oraworldXDB.Grid-Em.co.uk" has 1 instance(s).

Instance "oraworld", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@Grid-EM ~]$ export ORACLE_SID=oraworld

[oracle@Grid-EM ~]$ export ORACLE_HOME=/oracle/product/11.2.0/db_home1

--- sometimes even though "echo" display value of "ORACLE_SID & ORACLE_HOME" but they are not defined in environment. So it better we "export" it again manually and try it which I did in above steps.

[oracle@Grid-EM ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 9 14:08:20 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> conn / as sysdba

Connected to an idle instance.

everything was fine here Listener.ora,TnsNames.ora,Init.ora but Only Environment variable ORACLE_SID was not "export" properly.

******************** **********Some times small mistake create big chaos***************

if you lost your init.ora you may get some where here "/oracle/admin/<ORACLE_SID>/pfile"