The SQL cheat sheet provides you with the most commonly used SQL statements for your reference. You can download the SQL cheat sheet as follows:
Admin / password@localhost / database2: Read help about command syntax: HELP ‘ select’ h SELECT: HELP SELECT: Execute shell command! Pwd: Hard-to-explain Oracle scheduler: Execute SQL script from file: source mydir / test. Sql i mydir / test. Sql: echo @mydir / test. Sql sqlplus username / password@connect (only from. SQL Cheat Sheet The SQL cheat sheet is designed to provide a quick reference for the most common SQL statements you use. It is one-page A4 printable document in both PDF and JPEG format. You can print it out and stick it to your desk.
Querying data from a table
Query data in columns c1, c2 from a table
Query all rows and columns from a table
Query data and filter rows with a condition
Query distinct rows from a table
Sort the result set in ascending or descending order
Skip offset of rows and return the next n rows
Group rows using an aggregate function
Filter groups using HAVING clause
Querying from multiple tables
Inner join t1 and t2
Left join t1 and t1
Right join t1 and t2
Perform full outer join
Produce a Cartesian product of rows in tables
Another way to perform cross join
Join t1 to itself using INNER JOIN clause
Using SQL Operators
Combine rows from two queries
Return the intersection of two queries
Subtract a result set from another result set
Query rows using pattern matching %, _
Query rows in a list
Query rows between two values
Check if values in a table is NULL or not
Managing tables
Create a new table with three columns
Delete the table from the database
Add a new column to the table
Drop column c from the table
Add a constraint
Drop a constraint
Rename a table from t1 to t2
Rename column c1 to c2
Remove all data in a table
Using SQL constraints
Set c1 and c2 as a primary key
Set c2 column as a foreign key
Make the values in c1 and c2 unique
Ensure c1 > 0 and values in c1 >= c2
Set values in c2 column not NULL
Modifying Data
Insert one row into a table
Insert multiple rows into a table
Insert rows from t2 into t1
Update new value in the column c1 for all rows
Update values in the column c1, c2 that match the condition
Delete all data in a table
Delete subset of rows in a table
Managing Views
Create a new view that consists of c1 and c2
Create a new view with check option
Sqlplus Cheat Sheet Template
Create a recursive view
Create a temporary view
Delete a view
Managing indexes
Create an index on c1 and c2 of the t table
Create a unique index on c3, c4 of the t table
Drop an index
Managing triggers
Create or modify a trigger
WHEN
- BEFORE – invoke before the event occurs
- AFTER – invoke after the event occurs
EVENT
- INSERT – invoke for INSERT
- UPDATE – invoke for UPDATE
- DELETE – invoke for DELETE
TRIGGER_TYPE
- FOR EACH ROW
- FOR EACH STATEMENT
Delete a specific trigger
Some of our customers’ applications are built around Oracle, so we have to fight the beast from time to time. Unfortunately, some of the surprizes the beast has to offer are quite random and rare, and due to this we tend to simply forget how we fixed and/or circumvented the issues previously.
So this is just an attempt of a small cheat sheat to help our overloaded brains
- expired and locked accounts – the basics
Now, as of version 11g, Oracle has enabled account expiration per default for many vital accounts (such as SYSMAN, SYS, …). Quite a weird idea in my view, but who knows what hyper security things some wise engineer had in mind when doing so.Beware that we run Oracle under various Linux flavours, so things might be different for you.- become database admin
First log into your host running oracle and become the oracle user.
% sqlplus /nolog
SQL> connect / as SYSDBA
Connected
SQL> - find out which accounts are expired
select username, account_status from dba_users where ACCOUNT_STATUS LIKE '%EXPIRED%'; - unexpire an account
once an account has been expired, it can only be revived by assigning it a new password:
ALTER USER scott IDENTIFIED BY password; - unlock an account
ALTER USER scott ACCOUNT UNLOCK; - disable default password expiry [2]
this all depends on the profile a user belongs to, to disable password expiry for all users assigned the default user profile do this:
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
- become database admin
- Enterprise Manager: unable to connect to instance
One of the worst things that can happen in that course is if Enterprise Manager reports “Failed to connect to database instance: ORA-28001: the password has expired (DBD ERROR: OCISessionBegin)” and then simply rejects to work with the database.So far, we’ve found two accounts to be potential culprits for the problem: SYSMAN andDBSNMP- unexpire SYSMAN [3]
Quite tricky, because you have to change things on two sides: First unexpire the account as explained above. And now tweak oracle configuration.
As oracle user:- unexpire and unlock the account as explained above
- % emctl stop dbconsole
- change into
ORACLE_HOME/<HostName_SID>/sysman/config, for us this would be for example:
% cd /opt/oracle/111/klotho_ABSDEV/sysman/config - edit the emoms.properties file
and change the oracle.sysman.eml.mntr.emdRepPwd property to the new password you gave the SYSMAN user.
Then change the oracle.sysman.eml.mntr.emdRepPwdEncrypted property fromTRUE to FALSE (sidenote: Oracle will revert to TRUE automagically once it is restarted). - change into
ORACLE_HOME/<HostName_SID>/sysman/emd, for us this would be for example:
% cd /opt/oracle/111/klotho_ABSDEV/sysman/emd - edit the target.xml file
and edit those two properties:
<Property NAME='UserName' VALUE='SYSMAN' ENCRYPTED='FALSE'/>
<Property NAME='password' VALUE='TheNewPassword' ENCRYPTED='FALSE'/> - % emctl start dbconsole
- under “normal” circumstances, everything should be fine now
- unexpire SYSMAN [3]
Oracle Sql Syntax Cheat Sheet
So, as said above this is just a “small” cheat sheet for some annoyances we have met with the great oracle, of course there is much much more to know about the beast