Problem with Oracle 11.2.0.1

Following works on oracle 10g not on oracle11g.

SELECT column1,
 column2
FROM
 (SELECT 1 column1,2 column2,3 column3 FROM dual
 )
GROUP BY column1;

I found the same issue on Oracle Technology Forum (OTN). It seems there is a bug in Oracle 11.2.0.1.

this would be fixed by suggested workaround in the oracle forum.

Following should be set by Database administrator.
    set "_optimizer_group_by_placement"=FALSE

Reference: http://forums.oracle.com/forums/thread.jspa?threadID=1128695
 

Creating Database link

CREATE DATABASE LINK "<linkdemo>" CONNECT TO <user> IDENTIFIED BY <password> '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = <host name> )(PORT = < Port Number>)) (CONNECT_DATA = (SID = <SID>)))'


linkdemo = Name of the link.
host name= where the database is installed
Port Number=TNS listener port of the database
SID=database name
user=Database user
password=Database password

QUERY TO VIEW THE PROCEDURE

USER_SOURCE describes the text source of the stored objects owned by the current user.

SELECT * FROM USER_SOURCE;

NAME: Name of the object
TYPE: Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY
LINE: Line number of this line of source
TEXT: Text source of the stored object

More:-
ALL_SOURCE: describes the text source of the stored objects accessible to the current user.
DBA_SOURCE: describes the text source of all stored objects in the database.

View oracle table structure

SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='MY_TABLE';

In SQLPLUS, DESCRIBE command can be used.

DESCRIBE 'MY_TABLE';

Search for column names in Oracle

SELECT * FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='COLUMN_NAME' ;
SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME='COLUMN_NAME' ;

Try the following also:

• DBA_TAB_COLUMNS
  
• all_constraints (user_constraints, dba_constraints)
• all_indexes
• all_tables
• all_tab_columns

Copy table structure

CREATE TABLE MY_NEW_TABLE AS
SELECT * FROM MY_EXISTING_TABLE WHERE 1=2;

above SQL will create new table MY_NEW_TABLE having structure same as MY_EXISTING_TABLE.

How to Delete All Objects for a User in Oracle

Normally, it is simplest to drop and add the user. This is the preferred method if you have system or sysdba access to the database.

If you don't have system level access, and want to scrub your schema, the following sql will produce a series of drop statments, which can then be executed.

select 'drop '||object_type||' '|| object_name|| 
DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects;

Then, I normally purge the recycle bin to really clean things up. To be honest, I don't see a lot of use for oracle's recycle bin, and wish i could disable it... but anyway:

purge recyclebin;

This will produce a list of drop statements. Not all of them will execute - if you drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with:

select * from user_objects

Ref:
http://forums.oracle.com/forums/message.jspa?messageID=1057359

A warning message in Tomcat console

A warning message in Tomcat console. The message is

WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:AppName' did not find a matching property.

The solution to this problem is very simple.
- Double click on your tomcat server. It will open the server configuration.
- Under server options check 'Publish module contexts to separate XML files' checkbox.
- Restart your server.

This time your page will come without any issues.

SQL Oracle Command

CREATE DATABASE link
--------------------
CREATE DATABASE link link_name CONNECT TO user_nameIDENTIFIED BY use_pwd USING 'host:port/service_name';

Test the created Link
----------------------
select * from dual@"link_name";