SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='MY_TABLE';
In SQLPLUS, DESCRIBE command can be used.
DESCRIBE 'MY_TABLE';
Wednesday, September 8, 2010
Tuesday, September 7, 2010
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:
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.
SELECT * FROM MY_EXISTING_TABLE WHERE 1=2;
above SQL will create new table MY_NEW_TABLE having structure same as MY_EXISTING_TABLE.
Wednesday, September 1, 2010
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.
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:
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:
Ref:
http://forums.oracle.com/forums/message.jspa?messageID=1057359
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
Wednesday, May 5, 2010
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.
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.
Thursday, January 28, 2010
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";
--------------------
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";
Thursday, September 10, 2009
Vertical Scroll
Folloing code used to show a message box to scroll information text.
Subscribe to:
Posts (Atom)