Database Day 06: sqlplus Oracle commands (2)

1. Purge vs cascade constraints options 

purge and cascade constraints options in drop table command

purge clause is used to remove table completely, without purse the deleted tables are still recoverable.

Specify CASCADE CONSTRAINTS to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause, and such referential integrity constraints exist, then the database returns an error and does not drop the table [3].

"Oracle Database 10g introduces a new feature for dropping tables. When you drop a table, the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin, where it can later be recovered with the FLASHBACK TABLE statement if you find that you dropped the table in error. If you want to immediately release the space associated with the table at the time you issue the DROP TABLE statement, then include the PURGE clause as follows. DROP TABLE employees PURGE; Specify PURGE only if you want to drop the table and release the space associated with it in a single step. If you specify PURGE, then the database does not place the table and its dependent objects into the recycle bin. NOTE: You cannot roll back a DROP TABLE statement with the PURGE clause, and you cannot recover the table if you drop it with the PURGE clause. This feature was not available in earlier release."[1].

2. Format of the Date object

Date storage is often a problem with various DBMS's - for Oracle use the date data format of: 'DD-MMM-YYYY' in single quotes within an insert statement eg. '09-AUG-2014'.

3. Update statement
E.g.

4. Date related SQL statements

4.1 To format date as 'DD-MM-YYYY' on display
E.g. SELECT ProductName, Price, FORMAT(Now(),'DD-MM-YYYY') AS PerDate
FROM Products;

4.2 The Oracle/PLSQL TO_DATE function converts a string to a date.
E.g. to_date('2014-08-22', 'yyyy-mm-dd') would return a date value of August 22, 2014
E.g. TO_DATE('2014/08/22', 'yyyy/mm/dd') would return a date value of August 22, 2014
E.g. TO_DATE('082214', 'MMDDYY') would return a date value of August 22, 2014
E.g. TO_DATE('20140822', 'yyyymmdd') would return a date value of August 22, 2014

5. "Columns defined with the VARCHAR2 datatype store variable-length alphanumeric data (including text, numbers, and special characters). The maximum length of the data is specified in the parentheses. VARCHAR2 is the most commonly used datatype and can store up to 4,000 bytes.

In contrast, the CHAR datatype (not shown in Figure 7) allows for fixed-length alphanumeric data only and stores a maximum of 2,000 bytes.

I recommend choosing VARCHAR2 over CHAR when you select an alphanumeric datatype to define text columns. With VARCHAR2, particularly if you choose an appropriate maximum length for your columns, you won’t waste storage space.

(Choosing a maximum of 4,000 bytes makes sense only if you truly believe that your column will ever contain data values that reach that size limit.) CHAR can waste storage space, because the data is always padded with blank space to the specified fixed length before it is stored." [2].

6. SQL data types

SQL data types referene [4].

7. Oracle data types

1. numeric
2. number
3. char
4. varchar
5. date

8. set echo on and off commands

Add set echo on to allow commands to be echoed during execution

set echo on

add set echo off to turn off

set echo off

9. Roles and privileges

--select all the role name and granted role
select *
from role_role_privs
order by 1;

--Show role for the current user
SELECT * FROM USER_ROLE_PRIVS;

--Show role for username 'FOO'
select *
from user_role_privs
where username='FOO';

--show all the privileges for 'TUTORROLE'
select *
from role_sys_privs
where role='TUTORROLE'
order by 1;

--show all the priviledges for 'TUTORROLE'
select *
from role_sys_privs
where role='STUDENTROLE'
order by 1;

--list all current tables in the db
select * from cat;

References:
[1]. http://stackoverflow.com/questions/7713432/difference-between-drop-and-drop-purge-in-oracle, accessed date 22 August 2014.
[2]. http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512018.html, accessed date 22 August 2014.
[3]. http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm, accessed date 22 August 2014.
[4]. http://www.w3schools.com/sql/sql_datatypes_general.asp accessed date 25/08/2014.