Oracle Cheat sheet

[jimjim]

Dears,

Check the below link,

Viktor's Home Page: Oracle Cheat Sheet (http://www.vttoth.com/oracle.htm)


Regards,

[Palupi]

have you some a little bit description?

[jimjim]

Hello there,

This is a quick reference sheet for most frequently used commands in sqlplus. Oracle is a mjor item in most of the systems we work with, and as system administrator we encounter some times situation where we need to deal with Oracle. The link is a very nice collection to have when in urgent need for help.

However in order not to lose credit, find the contents below,

Some useful commands

To start a session as sysdba:sqlplus sys@tnsname as sysdba;To start a sysdba session under Windows (9iAS):sqlplus "/as sysdba"To list all tables in current schema:SELECT table_name FROM user_tables;or, all tables current user has access to:SELECT table_name FROM all_tables;To list all schemas:SELECT username FROM all_users ORDER BY username;To turn pause on:SET PAUSE ON;To list top n rows of a table in order:SELECT * FROM (SELECT * FROM t ORDER BY c) WHERE ROWNUM <= n;Show current database:SELECT * FROM global_name;Use database:CONNECT schema/password@tnsname;Show who I am:SHOW USER;Describe table:DESC tablename;Set display rows:SET PAGESIZE 66;Read field constraints:SELECT constraint_name,search_condition FROM user_constraints WHERE table_name='tablename';Copy table from foreign host to here:COPY FROM user@tnsname CREATE tablename USING SELECT * FROM tablename;Start SQLPLUS without login:SQLPLUS /NOLOGChange a user's password:ALTER USER user IDENTIFIED BY password;Unlock an accountALTER USER user ACCOUNT UNLOCK;I found some of these at http://www.johntopley.com/kb/oracle/index.html (http://www.johntopley.com/kb/oracle/index.html), a few others at https://gdsg.ngdc.noaa.gov/tiki/tiki-index.php?page=SqlNotes (https://gdsg.ngdc.noaa.gov/tiki/tiki-index.php?page=SqlNotes). There is also Mark Rittman's Oracle Weblog article about what to do (http://www.rittman.net/archives/000835.html) if an Oracle application server fails to start after a crash.
2. SSO issues

The easiest way to add Oracle Single Sign On authentication to a Web page/application just uploaded to an Oracle 9i application server is to hand-edit mod_osso.conf, inserting the following lines:

<Location /base-URL> require valid-user AuthType Basic</Location>See also http://www.oracle.com/technology/sample_code/deploy/security/Usingmod_osso.htm (http://www.oracle.com/technology/sample_code/deploy/security/Usingmod_osso.htm).
To administer SSO accounts, try http://your.oracle9iAS.server:7777/oiddas/ui/. If the relevant components are installed, this may work. The Oracle portal (if installed) is at http://your.oracle9iAS.server:7777/pls/portal/. Also, if you can log on to the application server database, you should be able to list all SSO users using

SELECT user_name FROM orasso.wwsec_person$;If you receive the following error:

Oracle SSO Warning - Unable to process request
Either the requested URL was not specified in terms of a fully-qualified host name or OHS single sign-on is incorrectly configured.
Please notify your administrator.
then poke around in the Apache/Apache subdirectory in your Oracle9i AS installation to see if you can find a log file explaining what happens. You may find that the problem is due to you using the wrong URL (e.g., numeric IP addresses where Oracle's SSO module expects a symbolic URL.)
3. Autonumbers

Oracle has no autonumbers like SQL Server, Access, or MySQL. One way to do autonumbers is by using a combination of a sequence and a trigger, as in the following example:

CREATE SEQUENCE sequence-name;CREATE OR REPLACE TRIGGER trigger-nameBEFORE INSERT ON table-nameFOR EACH ROWWHEN (NEW.field-name IS NULL OR NEW.field-name = '<new>')BEGINSELECT 'PR-' || sequence-name.NEXTVAL INTO :NEW.field-name FROM DUAL;END;/4. JDeveloper

To create a new JDeveloper application that will contain "raw" JSP code, follow these steps:

Create New Workspace
Create New Empty Project
Create New Class
Create New JSP
You may find that you need to specify additional libraries with which to link your project. To do so, select Project Settings in the menu and insert additional CLASSPATHs, e.g., I had to add S:\INSTALLS\ORACLE\JDeveloper-9i2\jdbc\lib\classes12.jar.
To create a new database application using Oracle's classes, follow these steps:

Create New Workspace
Create New Project with Business Components
Default package: mypackage1
Create New BC4J JSP Browse & Edit Form
5. Deploying an application

Before any of these steps, you must create a deployment profile in JDeveloper, and deploy the project to an EAR file. Then:

Connect to the application server's Enterprise Manager (TCP port 1810)
Select the server
Click OC4J_Home
Click Deploy EAR File
Specify file location, application name
Specify base URL
Specify JAZN LDAP authentication for SSO
Your application will then appear under the URL http://your.oracle9iAS.server:7777/base-URL. (The port number may be something other than 7777 depending on your AS setup.) You may need to restart OC4J_Home if, after deployment, the application misbehaves (Java compiler errors, out of memory errors, etc.)
If the application fails to run due to missing libraries, check your deployment profile. Make sure all libraries are included. If all else fails, then instead of using the dependency analyzer, choose to include all necessary libraries in their entirety.
6. Some default passwords



NamePasswordsyschange_on_installsystemmanagerorcladminwelcome

Regards,

:)