Object Privileges

GRANT {objpriv | ALL [privs]} [(column,column)]
ON object TO {user | role}
[WITH GRANT OPTION][WITH HIERARCHY OPTION];

i.e.,

GRANT select ON emp TO lynn;
REVOKE ALL ON emp FROM lynn;

Table	View	Materialized View	Sequence	Procedure, Function, Package, Operator, Index type, Library	Directory (for BFile or External)	Abstract data type
All	--	--	--	--	--	--
Alter (table --all or specific columns)	--	--	Alter	--	--	--
Delete	Delete	Delete (if updateable)	--	--	--	--
--	--	--	--	Execute	--	Execute
Debug	--	--	--	Debug	--	Debug
Flashback	Flashback	Flashback	--	--	--	--
Index (columns in a table)	--	--	--	--	--	--
Insert	Insert	Insert (if updateable)	--	--	--	--
On Commit Refresh	--	--	--	--	--	--
Query Rewrite	--	--	--	--	--	--
--	--	--	--	--	Read	--
References	--	--	--	--	--	--
Select	Select	Select	Select	--	--	--
--	Under	--	--	--	--	Under
Update (all or specific columns)	Update (all or specific columns)	Update (all or specific columns) (if updateable)	--	--	--	--
--	--	--	--	--	Write	--

Cluster

• Store tables related to each other
• Cluster Key - how tables are joined
• Can't use 'Tablespace' or 'Storage' clause when creating tables

------------------------------------------------------------------------
-- Drop all tables before dropping constraint
------------------------------------------------------------------------
Drop Table Emp Cascade Constraints;
Drop Table Dept Cascade Constraints;
Drop Index EmpAndDeptIdx ;
Drop Cluster EmpAndDept;
------------------------------------------------------------------------
-- The column name is irrelevant; the type should match the key type and length.
------------------------------------------------------------------------
Create Cluster EmpAndDept (ClusterKey Number(2));

------------------------------------------------------------------------
-- The Index must be created before insterting rows.
-- Key is stored once for both tables.
------------------------------------------------------------------------
Create Index EmpAndDeptIdx on Cluster EmpAndDept;

------------------------------------------------------------------------
-- Create the Dept Table and show it as being part of the cluster.
------------------------------------------------------------------------
Create Table Dept
(
 Deptno              Number(2) Not Null,
 Dname               Varchar2(14),
 Loc                 Varchar2(13),
 Constraint Dept_Deptno_Pk Primary Key (Deptno)
)
Cluster EmpAndDept(Deptno);

Insert Into Dept Values (10,'ACCOUNTING','NEW YORK');
Insert Into Dept Values (20,'RESEARCH','DALLAS');
Insert Into Dept Values (30,'SALES','CHICAGO');
Insert Into Dept Values (40,'OPERATIONS','BOSTON');

------------------------------------------------------------------------
-- Create the Emp Table and show it as being part of the cluster.
------------------------------------------------------------------------
Create Table Emp
(
 Empno               Number(4) Not Null,
 Ename               Varchar2(10),
 Job                 Varchar2(9),
 Mgr                 Number(4) ,
 Hiredate            Date,
 Sal                 Number(7,2),
 Comm                Number(7,2),
 Deptno              Number(2) Not Null,
 Constraint Emp_Deptno_Fk Foreign Key (Deptno) References Dept (Deptno),
 Constraint Emp_Empno_Pk Primary Key (Empno)
)
Cluster EmpAndDept(Deptno);

Insert Into Emp Values(7369,'SMITH','CLERK',7902,'17-DEC-1980',800,NULL,20);
Insert Into Emp Values(7499,'ALLEN','SALESMAN',7698,'20-FEB-1981',1600,300,30);
Insert Into Emp Values(7521,'WARD','SALESMAN',7698,'22-FEB-1981',1250,500,30);
Insert Into Emp Values(7566,'JONES','MANAGER',7839,'2-APR-1981',2975,NULL,20);
Insert Into Emp Values(7654,'MARTIN','SALESMAN',7698,'28-SEP-1981',1250,1400,30);
Insert Into Emp Values(7698,'BLAKE','MANAGER',7839,'1-MAY-1981',2850,NULL,30);
Insert Into Emp Values(7782,'CLARK','MANAGER',7839,'9-JUN-1981',2450,NULL,10);
Insert Into Emp Values(7788,'SCOTT','ANALYST',7566,'09-DEC-1982',3000,NULL,20);
Insert Into Emp Values(7839,'KING','PRESIDENT',NULL,'17-NOV-1981',5000,NULL,10);
Insert Into Emp Values(7844,'TURNER','SALESMAN',7698,'8-SEP-1981',1500,0,30);
Insert Into Emp Values(7876,'ADAMS','CLERK',7788,'12-JAN-1983',1100,NULL,20);
Insert Into Emp Values(7900,'JAMES','CLERK',7698,'3-DEC-1981',950,NULL,30);
Insert Into Emp Values(7902,'FORD','ANALYST',7566,'3-DEC-1981',3000,NULL,20);
Insert Into Emp Values(7934,'MILLER','CLERK',7782,'23-JAN-1982',1300,NULL,10);

------------------------------------------------------------------------
-- Get the data from the cluster
------------------------------------------------------------------------
Select *
  From Emp
  Join Dept
Using (DeptNo);

------------------------------------------------------------------------
-- Show the data dictionary information
------------------------------------------------------------------------
Column Single_Table Format A6

Select Cluster_Name, Tablespace_Name, Pct_Free, Pct_Used, Key_Size, Ini_Trans, Max_Trans, Initial_Extent, Next_Extent, Min_Extents, Max_Extents, Pct_Increase
  From User_Clusters;

Select Freelists, Freelist_Groups, Avg_Blocks_Per_Key, Cluster_Type, Function, Hashkeys, Degree, Instances, Cache, Buffer_Pool, Single_Table, Dependencies
  From User_Clusters;
  1  Select Column_name, Comments
  2    From All_Col_Comments
  3*  Where Table_Name = 'USER_CLUSTERS'
SYSTEM > 
SYSTEM > /

Column
Name                 Comments
-------------------- ------------------------------------------------------------------
CLUSTER_NAME         Name of the cluster
TABLESPACE_NAME      Name of the tablespace containing the cluster
PCT_FREE             Minimum percentage of free space in a block
PCT_USED             Minimum percentage of used space in a block
KEY_SIZE             Estimated size of cluster key plus associated rows
INI_TRANS            Initial number of transactions
MAX_TRANS            Maximum number of transactions
INITIAL_EXTENT       Size of the initial extent in bytes
NEXT_EXTENT          Size of secondary extents in bytes
MIN_EXTENTS          Minimum number of extents allowed in the segment
MAX_EXTENTS          Maximum number of extents allowed in the segment
PCT_INCREASE         Percentage increase in extent size
FREELISTS            Number of process freelists allocated in this segment
FREELIST_GROUPS      Number of freelist groups allocated in this segment
AVG_BLOCKS_PER_KEY   Average number of blocks containing rows with a given cluster key
CLUSTER_TYPE         Type of cluster: b-tree index or hash
FUNCTION             If a hash cluster, the hash function
HASHKEYS             If a hash cluster, the number of hash keys (hash buckets)
DEGREE               The number of threads per instance for scanning the cluster
INSTANCES            The number of instances across which the cluster is to be scanned
CACHE                Whether the cluster is to be cached in the buffer cache
BUFFER_POOL          The default buffer pool to be used for cluster blocks
SINGLE_TABLE         Whether the cluster can contain only a single table
DEPENDENCIES         Should we keep track of row level dependencies?
SYSTEM > 

Merge

You cannot update the same row more than once in a Merge statement.
Fine-grained access control is not implemented during MERGE. If needed, use Insert and Update.

Clear Screen
------------------------------------------------------
-- Create Dept
------------------------------------------------------
Drop Table Dept Cascade Constraints;
Create Table Dept (
 Deptno              Number(2) Not Null,
 Dname               Varchar2(14),
 Loc                 Varchar2(13),
     Constraint Dept_Deptno_Pk Primary Key (Deptno) );

Insert Into Dept Values (10,'ACCOUNTING','NEW YORK');
Insert Into Dept Values (20,'RESEARCH','DALLAS');
Insert Into Dept Values (30,'SALES','CHICAGO');
Insert Into Dept Values (40,'OPERATIONS','BOSTON');

------------------------------------------------------
-- Create Dept2 - holds records to be inserted/updated
------------------------------------------------------
Drop Table Dept2 Cascade Constraints;

Create Table Dept2 As Select * From Dept Where 1=2;

Insert Into Dept2 Values (10,'ACCOUNTING'  ,'PITTSBURGH');
Insert Into Dept2 Values (40,'OPERATIONS'  ,'BOSTON');
Insert Into Dept2 Values (50,'HOUSEKEEPING','WASHINGTON DC');
Commit;

------------------------------------------------------
-- Display the table data before the Merge
------------------------------------------------------
Prompt Table: Dept -- Before Merge
Select * From Dept Order By Deptno;

Prompt
Prompt Table: Dept2
Select * From Dept2 Order By Deptno;

------------------------------------------------------
-- Merge Dept2 into Dept
------------------------------------------------------
Merge Into Dept D1
Using (Select Deptno, Dname, Loc
         From Dept2) D2
   On (D1.Deptno = D2.Deptno)
 When     Matched Then Update Set D1.Loc = D2.Loc
                       Delete Where (Loc = 'BOSTON')
 When Not Matched Then Insert (D1.Deptno, D1.Dname, D1.Loc)
                       Values (D2.Deptno, D2.Dname, D2.Loc);
Commit;

------------------------------------------------------
-- Show dept table data after merge
------------------------------------------------------
Prompt
Prompt Table: Dept -- After Merge
Select * From Dept Order By Deptno;

Multitable Insert

Multitable inserts are subject to the following restrictions:

* not for views or materialized views, remote tables, no table collection expression
* all of the insert_into_clauses cannot combine to specify more than 999 target columns.
* are not parallelized if any target table is index organized or has a bitmap index .
* The subquery cannot use a sequence.

Example on Left:

Clear Screen
Drop Table Dept10  Purge;
Drop Table Dept20  Purge;
Drop Table Dept00  Purge;

Create Table Dept10 As Select * From Emp Where 1=2;
Create Table Dept20 As Select * From Emp Where 1=2;
Create Table Dept00 As Select * From Emp Where 1=2;

Insert First When Deptno = 10 Then Into Dept10
       When Deptno = 20 Then Into Dept20
       Else                  Into Dept00
Select *
From Emp;

Set Echo On
Select * from Dept10;
Select * from Dept20;
Select * from Dept00;
Set Echo Off

---

Example on Right:

Drop Table Dept10  Purge;
Drop Table Dept20  Purge;
Drop Table FullPay Purge;

Create Table Dept10 As Select * From Emp Where 1=2;
Create Table Dept20 As Select * From Emp Where 1=2;

Create Table FullPay
(Empno    Number(4)  Not Null,
Sal_Comm Number(7,2)
);

Insert All When Deptno = 10
      And Job   ^= 'PRESIDENT'  Then Into Dept10
     When Deptno = 20
      And Job    = 'CLERK'      Then Into Dept20
     When Comm is not null      Then Into FullPay values(Empno, Sal+Comm)
Select *
From Emp;

Set Echo On
Select * from Dept10;
Select * from Dept20;
Select * from FullPay;
Set Echo Off

Dump (Expr, Return_Fmt, Start, Length)

Column Street_Address Format A25
Column Dump           Format A30
Column Substr         Format A10

Select Street_Address
       -----------------------------------------------
     , Substr(Street_Address,3,4) As Substr
       -----------------------------------------------
     , Dump(Street_Address   /* Field or Expression */
                        , 3  /* ASCII Format        */
                        , 3  /* Start in Position 3 */
                        , 4  /* Return 4 characters */
            ) As Dump
       -----------------------------------------------
  From HR.Locations
 Where RowNum < 2;

Dump returns:	Return Formats:	Common Types:
Typ=12 (Data Type) Len=7: (Full Length of Column) 120,108 (Internal Representation)	3 ASCII (default) 8 Octal 10 Decimal 16 Hexadecimal 17 Single character	Typ=1 VARCHAR2 Typ=96 CHAR Typ=2 NUMBER Typ=12 DATE

STREET_ADDRESS            SUBSTR     DUMP
------------------------- ---------- ------------------------------
1297 Via Cola di Rie      97 V       Typ=1 Len=20: 57,55,32,86

See Character Sets for a complete list of codes generated from DUMP.

Character Set

The query below generated the data shown in the table using the DUMP statement. SQL*Plus dumped in a straight line as normal. I used Google Docs to format it into a table.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

. Char Ascii Decimal Octal Hex Char Ascii Decimal Octal Hex Char Ascii Decimal Octal Hex Char Ascii Decimal Octal Hex . space 32 40 20 0 48 60 30 A 65 101 41 a 97 141 61 . ! 33 41 21 1 49 61 31 B 66 102 42 b 98 142 62 . " 34 42 22 2 50 62 32 C 67 103 43 c 99 143 63 . $ 36 44 24 3 51 63 33 D 68 104 44 d 100 144 64 . % 37 45 25 4 52 64 34 E 69 105 45 e 101 145 65 . & 38 46 26 5 53 65 35 F 70 106 46 f 102 146 66 . ' 39 47 27 6 54 66 36 G 71 107 47 g 103 147 67 . ( 40 50 28 7 55 67 37 H 72 110 48 h 104 150 68 . ) 41 51 29 8 56 70 38 I 73 111 49 i 105 151 69 . * 42 52 2a 9 57 71 39 J 74 112 4a j 106 152 6a . + 43 53 2b K 75 113 4b k 107 153 6b . , 44 54 2c [ 91 133 5b L 76 114 4c l 108 154 6c . - 45 55 2d \ 92 134 5c M 77 115 4d m 109 155 6d . . 46 56 2e ] 93 135 5d N 78 116 4e n 110 156 6e . / 47 57 2f ^ 94 136 5e O 79 117 4f o 111 157 6f . _ 95 137 5f P 80 120 50 p 112 160 70 . : 58 72 3a ` 96 140 60 Q 81 121 51 q 113 161 71 . ; 59 73 3b R 82 122 52 r 114 162 72 . < 60 74 3c { 123 173 7b S 83 123 53 s 115 163 73

. = 61 75 3d | 124 174 7c T 84 124 54 t 116 164 74 . > 62 76 3e } 125 175 7d U 85 125 55 u 117 165 75 . ? 63 77 3f V 86 126 56 v 118 166 76 . @ 64 100 40 W 87 127 57 w 119 167 77 . X 88 130 58 x 120 170 78 . Y 89 131 59 y 121 171 79 . Z 90 132 5a z 122 172 7a

rem +--------------------------------------------------------------------------+
rem | Script ID:     Dump.sql
rem | Purpose:       Show how dump works and dump all characters in all sets.
rem |               
rem | Developer:     Lynn Tobias
rem | Script Date:   8/26/2008
rem | Oracle Ver:    10g
rem +--------------------------------------------------------------------------+

Set Serveroutput On
Declare
  V_Outputstr Varchar2(50)              ;

Begin
 Dbms_Output.Put_Line(Rpad('Char',5)|| Rpad('Ascii',6)|| 'Octal Decimal Hex');

 For V_Ctr In 1..94 Loop

Select Lpad(Substr(Dump(Substr(Charx,V_Ctr,1),17),Instr(Dump(Substr(Charx,V_Ctr,1),17),': ',11)+2),4) ||
       Lpad(Substr(Dump(Substr(Charx,V_Ctr,1), 3),Instr(Dump(Substr(Charx,V_Ctr,1), 3),': ',11)+2),5) ||
       Lpad(Substr(Dump(Substr(Charx,V_Ctr,1), 8),Instr(Dump(Substr(Charx,V_Ctr,1), 8),': ',11)+2),6) ||
       Lpad(Substr(Dump(Substr(Charx,V_Ctr,1),10),Instr(Dump(Substr(Charx,V_Ctr,1),10),': ',11)+2),8) ||
       Lpad(Substr(Dump(Substr(Charx,V_Ctr,1),16),Instr(Dump(Substr(Charx,V_Ctr,1),16),': ',11)+2),4) 
  Into V_Outputstr
  From (  ------------- This is the list of characters we want to evaluate -------------------------
        Select 
        ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~' 
        As Charx 
        From Dual
        );

  Dbms_Output.Put_Line(V_Outputstr);
  End Loop;
End;
/
-- How the lines starting with LPad work:
-- 1            Substr(Charx,V_Ctr,1)               get one character at a time from the string based on the counter
-- 2       Dump(Substr(Charx,V_Ctr,1),10)           dump to get this string: 'Typ=1 Len=1: 97'
-- 3 Instr(Dump(Substr(Charx,V_Ctr,1),10),':',11)   find the position of the ':' in the string
-- 4 Instr(Dump(Substr(Charx,V_Ctr,1),10),':',11)+2 Add 2 to get the beginning position of the character (i.e., 97)
-- 5 Substr (Search Item 2 ,Starting at position calculated in Item 4)                    
-- 6 LPad it so different length characters line up.

Data Storage Size

1024¹ ...... kb ...... kilobyte
1024² ...... Mb ...... megabyte
1024³ ...... Gb ...... gigabyte
1024⁴ ...... Tb ...... terabyte
1024⁵ ...... Pb ...... petabyte
1024⁶ ...... Eb ...... exabyte
1024⁷ ...... Zb ...... zettabyte
1024⁸ ...... Yb ...... yottabyte

Tablespace

Clear Screen
Ttitle Off
Spool utblspc.txt
Prompt Table: User_Tablespaces

Column Block_SizeK       Format 999 Heading Block|Size|(K)
Column Initial_ExtentK   Format 9999 Heading Initial|Extent|(K)
Column Next_ExtentK      Format 9999 Heading Next|Extent|(K)
Column Max_ExtentsM      Format 9999 Heading Max|Extent|(M)
Column Min_ExtlenK       Format 9999 Heading Min|Extlen|(K)
Column Status            Format A6

Break On Contents Dup Skip 1

Select Tablespace_Name,
   Block_Size/1024        As Block_Sizek,
   Initial_Extent/1024    As Initial_Extentk,
   Next_Extent/1024       As Next_Extentk,
   Min_Extents,
   Max_Extents/1024/1024  As Max_Extentsm,
   Pct_Increase,
   Min_Extlen/1024        As Min_Extlenk,
   Status, Contents, Logging, Force_Logging, Extent_Management, Allocation_Type,
   Segment_Space_Management,Def_Tab_Compression, Retention, Bigfile
From User_Tablespaces
Where Tablespace_Name ^= 'EXAMPLE'
Order By Contents;

Prompt
Prompt ===========================================================================================================================================================
Prompt
Prompt Table: User_Users

Select Username,
   Default_Tablespace,
   Temporary_Tablespace
From User_Users;

Prompt
Prompt ===========================================================================================================================================================
Prompt
Prompt Table: User_Ts_Quotas  ('-1' Is Unlimited)

Column BytesK Format 9999 Heading Bytes|(K)
Column BytesM Format 99.9 Heading Bytes|(M)

Select Tablespace_Name,
   Bytes/1024      As Bytesk,
   Bytes/1024/1024 As Bytesm,
   Max_Bytes, Blocks, Max_Blocks, Dropped
From User_Ts_Quotas;

Prompt
Prompt ===========================================================================================================================================================
Prompt
Prompt Table: Dba_Data_Files

Column Status                               Format A9
Column Bytesm       Heading Bytes|(M)       Format 9999.9
Column Blocksk      Heading Blocks|(K)      Format 999.9
Column Maxbytesg    Heading Max|Bytes|(G)   Format 99.9
Column Maxblocksm   Heading Max|Blocks|(M)  Format 99.9
Column User_Bytesm  Heading User|Bytes|(M)  Format 999.9
Column User_Blocksk Heading User|Blocks|(K) Format 999.9

Select File_Name, File_Id, Tablespace_Name,
   Bytes/1024/1024                 As Bytesm,
   Blocks/1024                     As Blocksk,
   Status, Relative_Fno,
   Autoextensible,
   Round(Maxbytes/1024/1024/1024)  As Maxbytesg ,
   Maxblocks/1024/1024             As Maxblocksm,
   Increment_By,
   User_Bytes/1024/1024            As User_Bytesm,
   User_Blocks/1024                As User_Blocksk,
   Online_Status   
From Dba_Data_Files
Where File_Name Not Like '%EXAMPLE%'
Order By File_Id;

Prompt
Prompt ===========================================================================================================================================================
Prompt
Prompt Table: User_Free_Space

Column Bytes/Blocks  Format 9999    Heading Bytes/|Blocks
Column Bytesk        Format 999,999 Heading Bytes|(K)
Break On File_Id Skip 1 Dup

Select Tablespace_Name, File_Id, Block_Id,
   Bytes/1024    As Bytesk,
   Blocks, Relative_Fno , Bytes/Blocks
From User_Free_Space
Where Tablespace_Name ^= 'EXAMPLE'
Order By File_Id, Bytes Desc;

Prompt
Prompt ===========================================================================================================================================================
Prompt
Prompt Table: Recyclebin

Column Sum(Space) Heading Total|Blocks

Select Sum(Space)
From Recyclebin
Where Ts_Name = 'USERS';
Spool off
Table: User_Tablespaces

       Block Initial   Next            Max   Pct    Min                            Force Extent Allo-   Segment    Def
Tablespace  Size  Extent Extent     Min Extent Incr- Extlen                            Log-  Manag  cation  Space      Tab                     Big-
Name         (K)     (K)    (K) Extents    (M)  ease    (K) Status Contents  Logging   ging  -ement Type    Management Compression Retention   file
---------- ----- ------- ------ ------- ------ ----- ------ ------ --------- --------- ----- ------ ------- ---------- ----------- ----------- ----
SYSTEM         8      64 .            1   2048 .         64 ONLINE PERMANENT LOGGING   NO    LOCAL  SYSTEM  MANUAL     DISABLED    NOT APPLY   NO
SYSAUX         8      64 .            1   2048 .         64 ONLINE PERMANENT LOGGING   NO    LOCAL  SYSTEM  AUTO       DISABLED    NOT APPLY   NO
USERS          8      64 .            1   2048 .         64 ONLINE PERMANENT LOGGING   NO    LOCAL  SYSTEM  AUTO       DISABLED    NOT APPLY   NO

TEMP           8    1024   1024       1 .       0      1024 ONLINE TEMPORARY NOLOGGING NO    LOCAL  UNIFORM MANUAL     DISABLED    NOT APPLY   NO

UNDOTBS1       8      64 .            1   2048 .         64 ONLINE UNDO      LOGGING   NO    LOCAL  SYSTEM  MANUAL     DISABLED    NOGUARANTEE NO
===========================================================================================================================================================
Table: User_Users

         Default      Temporary
Username     Tablespace   Tablespace
------------ ------------ ------------
SCOTT        USERS        TEMP
===========================================================================================================================================================
Table: User_Ts_Quotas  ('-1' Is Unlimited)

Tablespace Bytes Bytes      Max              Max
Name         (K)   (M)    Bytes  Blocks   Blocks Dropped
---------- ----- ----- -------- ------- -------- -------
USERS       5888   5.8        0     736        0 NO
===========================================================================================================================================================
Table: Dba_Data_Files
                                                                                           Rela- Auto-    Max    Max             User   User
File                                                  File Tablespace   Bytes Blocks            tive exten- Bytes Blocks Increment  Bytes Blocks Online
Name                                                    Id Name           (M)    (K) Status      Fno sible    (G)    (M)        By    (M)    (K) Status
----------------------------------------------------- ---- ---------- ------- ------ --------- ----- ------ ----- ------ --------- ------ ------ -------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10\SYSTEM01.DBF     1 SYSTEM       490.0   61.3 AVAILABLE     1 YES     32.0    4.0     1,280  489.9   61.2 SYSTEM
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10\UNDOTBS01.DBF    2 UNDOTBS1      40.0    5.0 AVAILABLE     2 YES     32.0    4.0       640   39.9    5.0 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10\SYSAUX01.DBF     3 SYSAUX       350.0   43.8 AVAILABLE     3 YES     32.0    4.0     1,280  349.9   43.7 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10\USERS01.DBF      4 USERS          8.8    1.1 AVAILABLE     4 YES     32.0    4.0       160    8.7    1.1 ONLINE
===========================================================================================================================================================
Table: User_Free_Space

                                      Rela-
Tablespace File    Block    Bytes          tive Bytes/
Name         Id       Id      (K)  Blocks   Fno Blocks
---------- ---- -------- -------- ------- ----- ------
SYSTEM        1   62,241    3,840     480     1   8192

UNDOTBS1      2    2,313   22,464   2,808     2   8192
UNDOTBS1      2      201      192      24     2   8192
UNDOTBS1      2      521       64       8     2   8192

SYSAUX        3   43,593    9,664   1,208     3   8192
SYSAUX        3   42,873      128      16     3   8192
SYSAUX        3   38,745       64       8     3   8192

USERS         4    1,113       64       8     4   8192
USERS         4    1,009       64       8     4   8192
USERS         4      769       64       8     4   8192
===========================================================================================================================================================
Table: Recyclebin

 Total
Blocks
----------
   144

Data Storage

Database Blocks are allocated to extents as specified in data storage clauses.
Each object (table, index, clulster) is allocated a single segment which is comprised of one or more extents.
Segments are stored in tablespaces as determined by the DBA when the object is created.
All of the tablespaces taken together comprise the database.

To Run a SQL Query From a DOS Batch File

This is one way. I haven't investigated doing the whole thing in one script. Also need to determine how to send the password into this rather than have it as part of the script.

The DOS batch file calls sqlplus and has the user/password, and the SQL script to be executed. Afte the SQL script has exited, this will clear the screen and type out the report from the spooled file:

@Echo Off
sqlplus scott/tiger @C:\emptest.sql
cls
type c:\emptest.txt

---

The SQL Script looks normal, but must have 'EXIT' at the end:

Set Linesize 200 Pagesize 50 Feedback Off  Trimspool On
Ttitle Left 'Employee Data Query Run From DOS Batch File' Skip 2

Spool C:\Emptest.Txt
Select Empno, Ename, Deptno
  From Emp
 Where DeptNo = 20;
Spool Off

Exit

Database Information

rem +--------------------------------------------------------------------------+
rem | Script ID:     DBInfo.sql
rem |
rem | Purpose:       Displays General Information About The Database.
rem |            
rem | Developer:     Hung Wu
rem | Oracle Ver:    10g
rem |
rem | Table(s) Used: v$Controlfile  v$Database  v$Datafile
rem |                v$Instance     v$Logfile   v$Sga       v$Version
rem |            
rem | Output:        n/a
rem |
rem | Revisions:     Dvl Date     Ver Comment/Change
rem |                --- -------- --- -----------------------------------------+
rem |                lct 08/16/08 1.1 Added this top block, prompts, Mb calculation,
rem |                                 column definitions, formatting, spool,
rem +--------------------------------------------------------------------------+
Clear Screen
Set Pagesize 1000 Linesize 1000 Feedback Off Heading on Newpage 1
Ttitle Off

Column DBID                                        Format 9999999999
Column Value                                       Format 999,999,999 Trunc
Column Separator1        Heading ' '               Format A9
Column Blocked                                     Format A7
Column VERSION           Heading  Version          Format A10
Column STATUS            Heading  Status           Format A7
Column THREAD#           Heading  Thread#          Format 99
Column ARCHIVER          Heading  Archiver         Format A7
Column LOG_SWITCH_WAIT   Heading  Log|Switch|Wait  Format A6
Column LOGINS            Heading  Logins           Format A7
Column DATABASE_STATUS   Heading  Database|Status  Format A8
Column INSTANCE_Name     Heading  Instance|Name    Format A8
Column ACTIVE_STATE      Heading  Active|State     Format A6
Column BLOCKED           Heading  Blo-|cked        Format A4
Column PARALLEL          Heading  Para-|llel       Format A5
Column INSTANCE_ROLE     Heading  Instance|Role    Format A8 Word_Wrapped
Column HOST_NAME         Heading  Host|Name        Format A6 Word_Wrapped
Column Controlfile_Created                         Format A11
Column Controlfile_Time                            Format A11
Column Name                                        Format A10

Spool DBInfo.Txt
Prompt =================================================================================================================================
Prompt Table: v$Database

Select Created, Database_Role, Dataguard_Broker, Dbid, Db_Unique_Name,
    Flashback_On, Force_Logging, Guard_Status, Log_Mode, Name, Open_Mode
From v$Database;
Select Platform_Id, Platform_Name, Protection_Level, Protection_Mode, Switchover_Status,
    Version_Time,' ' As Separator1, Current_Scn, Standby_Became_Primary_Scn
From v$Database;
Select Fs_Failover_Current_Target, Fs_Failover_Observer_Host, Fs_Failover_Observer_Present,
    Fs_Failover_Status, Fs_Failover_Threshold,' ' As Separator1, ' ' As Separator1,
    Supplemental_Log_Data_All, Supplemental_Log_Data_Fk, Supplemental_Log_Data_Min,
    Supplemental_Log_Data_Pk, Supplemental_Log_Data_Ui
From v$Database;
Select Open_Resetlogs, Prior_Resetlogs_Time, Resetlogs_Time, ' ' As Separator1, ' '
    As Separator1, ' '   As Separator1, Controlfile_Change#, Controlfile_Created,
    Controlfile_Sequence#, Controlfile_Time, Controlfile_Type
From v$Database;
Select Archivelog_Change#, Archivelog_Compression, Archive_Change#, Remote_Archive,' '
    As Separator1, ' ' As Separator1, Activation#, Checkpoint_Change#, Last_Open_Incarnation#,
    Recovery_Target_Incarnation#, Switchover#
From v$Database;

Prompt =================================================================================================================================
Prompt Table: v$Instance

Select * from v$Instance;

Prompt =================================================================================================================================
Prompt Table: v$Version

Select * From  v$Version;

Prompt =================================================================================================================================
Prompt Table: v$SGA

Column Name Format A20
Column Kb   Format 9,999,999
Column Mb   Format    99,999
Column Gb   Format       999
Compute Sum    Label 'Total'    Of Value Kb Mb Gb On Report
Break on Report

Select Name, Value,
    Trunc(Value/128)         as Kb,
    Trunc(Value/128/128)     as Mb,
    Trunc(Value/128/128/128) as Gb
From v$SGA;

Prompt =================================================================================================================================
Prompt Table: v$ControlFile

Column Name Format A53

Select *
From v$ControlFile
Order By Name;

Prompt =================================================================================================================================
Prompt Table: v$DataFile

Column DirFromName Heading 'Directory (from Name)' Format A40
Column Name Format A13

Select Substr(Name, Instr(Name, '\', -1, 1)+1) Name,
    Substr(Name, 1, Instr(Name, '\', -1, 1)) DirFromName,
    File#, Status, Ts#, Rfile#, Enabled, Aux_Name,
    Creation_Change#, Creation_Time, Last_Change#, Last_Time
From V$Datafile
Order By Name;

Select Substr(Name, Instr(Name, '\', -1, 1)+1) Name,
    Plugged_In, Bytes, Create_Bytes,
    Blocks, Block_Size, Block1_Offset,
    First_Nonlogged_Scn, First_Nonlogged_Time,
    Checkpoint_Change#, Checkpoint_Time,
    Unrecoverable_Change#, Unrecoverable_Time
From V$Datafile
Order By Name;

Select Substr(Name, Instr(Name, '\', -1, 1)+1) Name,
    Offline_Change#, Online_Change#, Online_Time
From V$Datafile
Order By Name;

Prompt =================================================================================================================================
Prompt Table: v$LogFile
Column Member Format A50

Select *   From v$LogFile;

Spool Off
Prompt
Prompt Your data has been spooled to DBInfo.Txt
Prompt

Count on Case

Break on Report
Compute Sum Of Salesman Clerk Manager Analyst Total On Report
Select Mgr,
       Count(Case When JOB = 'SALESMAN' Then 1 End)  As SALESMAN ,
       Count(Case When JOB = 'CLERK'    Then 1 End)  As CLERK    ,
       Count(Case When JOB = 'MANAGER'  Then 1 End)  As MANAGER  ,
       Count(Case When JOB = 'ANALYST'  Then 1 End)  As ANALYST  ,
       Count(                                1    )  As Total  
  From Emp
 Where Job <> 'PRESIDENT'
 Group By Mgr;

Create a New Profile Script

The first script (NewProfile.SQL) will call ProfRsrc.SQL, if resource_limit is 'True', otherwise, it will just prompt the user for settings for passwords.

rem +------------------------------------------------------------------+
rem | Script ID:    NewProfile.sql
rem |
rem | Purpose:      Prompt a newbie creating a profile with what the 
rem |               parameters mean and what their choices are.  
rem |             
rem | Developer:    Lynn Tobias
rem | Script Date:  August 01, 2008
rem | Oracle Ver:   10g
rem |
rem | Input File(s): none
rem |
rem | Table(s) Used: DBA_Profiles, v$Parameters
rem |              
rem | Called by:     n/a
rem | Calls:         Resources.SQL - created and called. It runs 
rem |                                ProfRsrc.SQL if value = True and 
rem |                                posts a message if false.
rem |                Create_Profile.Sql - actual profile code generated
rem |                
rem | Variables:     xValue (from v$parameters) if 'False' no resources
rem |                Profile_Name - Name typed in
rem |                Password Parameters:         Password_Verify_Function
rem |                      Failed_Login_Attempts, Password_Grace_Time, 
rem |                      Password_Life_Time,    Password_Lock_Time, 
rem |                      Password_Reuse_Max,    Password_Reuse_Time
rem |
rem | Revisions:     Prog Date     Version Comment/Change
rem |                ---- -------- ------- ----------------------------+
rem |             
rem +------------------------------------------------------------------+

Clear Screen 
Prompt +-------------------------------------------------------------------------+
Prompt |                 C r e a t i n g   a   P r o f i l e                     | 
Prompt +-------------------------------------------------------------------------+
Prompt | Notes: Anyone not assigned a profile is subject to the DEFAULT profile. | 
Prompt |        If a profile omits some resources, the DEFAULT profile is used.  |
Prompt +-------------------------------------------------------------------------+
Prompt

rem *1**************************************************************************
rem  Show the settings on the default profile 
rem ****************************************************************************

Prompt The current settings on the Default profile are:

Break on Resource_Type Skip 1
Set NewPage 1 Heading On Feedback Off Pagesize 60

Column Limit          Format A15 Heading Limit
Column Profile        Format A7  Heading Profile
Column Resource_Name  Format A30 Heading Resource|Name
Column Resource_Type  Format A10 Heading Resource|Type

Select Profile, Resource_Type, Resource_Name, Limit
  From Dba_Profiles
 Where Profile = 'DEFAULT'
 Order By Resource_Type, Resource_Name;

rem *2**************************************************************************
rem  List profile names already taken and ask for a new one.
rem ****************************************************************************

Prompt
Column Profile Format A30 Heading 'Current Profiles:'

Select Distinct Profile
  From Dba_Profiles
 Order by Profile;

Prompt
Accept Profile_Name Prompt 'Please choose a new name: '

      rem *2.1******************************************************************
      rem  Make sure the typed name is not on the list. 
      rem **********************************************************************

Set Heading Off Feedback Off Verify Off

Select Case When Ctr > 0 
            Then '*->> *** THIS PROFILE HAS ALREADY BEEN USED AND WILL BE DROPPED.****'
            Else ' '
        End
  From (
         Select Count(*) Ctr
           From DBA_Profiles
          Where Profile = Upper('&Profile_Name')
       );

rem *3**************************************************************************
rem  Write the Drop and the first line of the Create Profile.
rem ****************************************************************************

Set Term Off
Spool Create_Profile.Sql Replace

Select 'DROP PROFILE '   || '&Profile_Name' || ';'
  From Dual;

Select 'CREATE PROFILE ' || '&Profile_Name' || ' LIMIT'
  From Dual;

Spool Off
Set Term On

rem *4**************************************************************************
rem  Check the resource limit parameter to see if they can be set
rem ( ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH; )
rem ****************************************************************************

Column Value New_Value Xvalue
Set Term off

Spool Resources.SQL Replace

Select Case When Value = 'TRUE' 
            Then '@ProfRsrc'
            Else 'Prompt *->> RESOURCE_LIMIT=FALSE. ALTER SYSTEM to use.'
        End
  From V$Parameter 
 Where Name = 'resource_limit';

Spool Off
Set Term On

Prompt
@Resources
Prompt

rem *5**************************************************************************
rem  Get the password parameters
rem ****************************************************************************

Prompt
Prompt |               P A S S W O R D   P A R A M E T E R S
Prompt +-------------------------------------------------------------------------+
Prompt |      Please either supply an answer to the following parameters         |
Prompt |                  or press Enter to choose Default:                      |
Prompt +-------------------------------------------------------------------------+
Prompt |Note: These can take any one of these as an answer:                      |
Prompt |      - integer                                                          |
Prompt |      - expression (any form except scalar subquery expression)          |
Prompt |      - UNLIMITED                                                        |
Prompt |      - DEFAULT   (press Enter)                                          |
Prompt |                                                                         |
Prompt | Time:  Day=1       Hours=n/24       Minute=n/1440       Seconds=n/86400 |
Prompt +-------------------------------------------------------------------------+
Prompt
Accept FAILED_LOGIN_ATTEMPTS Default Default Prompt -
'FAILED_LOGIN_ATTEMPTS (# of failed logins to the acct before the acct is locked): '
Prompt
Accept PASSWORD_GRACE_TIME   Default Default Prompt -
'PASSWORD_GRACE_TIME (# of days between warning and login expiration)            : '
Prompt
Accept PASSWORD_LIFE_TIME    Default Default Prompt -
'PASSWORD_LIFE_TIME (# of days a password can be used)                           : '
Prompt
Accept PASSWORD_LOCK_TIME    Default Default Prompt-
'PASSWORD_LOCK_TIME (# of days acct locked after the # of repeated failed logins): '
Prompt
Prompt +-------------------------------------------------------------------------+
Prompt |         For PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX to work,         |
Prompt |                   they must both be specified.                          |
Prompt +-------------------------------------------------------------------------+
Prompt | If PASSWORD_REUSE_TIME = 10 and PASSWORD_REUSE_MAX = 2,                 | 
Prompt |    then Password can be reused after 10 days if changed 2 times.        |
Prompt |                                                                         |
Prompt | If either is a number and the other UNLIMITED,                          |
Prompt |    then the password can never be reused.                               |
Prompt +-------------------------------------------------------------------------+
Prompt
Accept PASSWORD_REUSE_MAX    Default Default Prompt -
'PASSWORD_REUSE_MAX    (# of changes required before the current one can be reused): '
Prompt
Accept PASSWORD_REUSE_TIME   Default Default Prompt -
'PASSWORD_REUSE_TIME   (# of days before a password can be reused)                 : '
Prompt
Prompt +-------------------------------------------------------------------------+
Prompt | PASSWORD_VERIFY_FUNCTION                                                |
Prompt +-------------------------------------------------------------------------+
Prompt | This allows a PL/SQL password verification script to be used.           |
Prompt | Oracle provides a default script.  Use this or 3rd-party code, or make  |
Prompt | your own.  'NULL' is no verification done.                              |
Prompt |                                                                         |
Prompt | Answer: Function | NULL | DEFAULT                                       |
Prompt +-------------------------------------------------------------------------+
Prompt
Accept PASSWORD_VERIFY_FUNCTION Default Default Prompt 'PASSWORD_VERIFY_FUNCTION: '
Prompt 

rem *6**************************************************************************
rem  Create the profile statement
rem ****************************************************************************

Set Heading Off NewPage None Linesize 100 Term Off

Spool Create_Profile.Sql Append

Select 'FAILED_LOGIN_ATTEMPTS        ' || '&FAILED_LOGIN_ATTEMPTS'    From Dual;
Select 'PASSWORD_GRACE_TIME          ' || '&PASSWORD_GRACE_TIME'      From Dual;
Select 'PASSWORD_LIFE_TIME           ' || '&PASSWORD_LIFE_TIME'       From Dual;
Select 'PASSWORD_LOCK_TIME           ' || '&PASSWORD_LOCK_TIME'       From Dual;
Select 'PASSWORD_REUSE_MAX           ' || '&PASSWORD_REUSE_MAX'       From Dual;
Select 'PASSWORD_REUSE_TIME          ' || '&PASSWORD_REUSE_TIME'      From Dual;
Select 'PASSWORD_VERIFY_FUNCTION     ' || '&PASSWORD_VERIFY_FUNCTION' From Dual;
Select ';'                                                            From Dual;
Spool Off

rem *7**************************************************************************
rem  Execute the statement created
rem ****************************************************************************

Set Echo On Feedback On Term On
@Create_Profile
Set Echo Off Heading On Feedback Off NewPage 1

rem *8**************************************************************************
rem  Show them the new profile from DBA_Profiles
rem ****************************************************************************

Column Profile        Format A7  Heading Profile

Select Profile, Resource_Type, Resource_Name, Limit
  From Dba_Profiles
 Where Profile = Upper('&Profile_Name')
 Order By Resource_Type, Resource_Name;

---

rem +------------------------------------------------------------------+
rem | Script ID:    ProfRsrc.sql
rem |
rem | Purpose:      Prompt a newbie creating a profile with what the 
rem |               parameters mean for resources and what their choices are.  
rem |             
rem | Developer:    Lynn Tobias
rem | Script Date:  August 01, 2008
rem | Oracle Ver:   10g
rem |
rem | Input File(s): none
rem |
rem | Table(s) Used: Dual
rem |              
rem | Called by:     Resources.SQL (generated by NewProfile.Sql)
rem | Calls:         n/a
rem |
rem | Variables:     Resource Parameters: SESSIONS_PER_USER, CPU_PER_SESSION, 
rem |                         CPU_PER_CALL, CONNECT_TIME, IDLE_TIME, 
rem |                         LOGICAL_READS_PER_SESSION, LOGICAL_READS_PER_CALL,
rem |                         COMPOSITE_LIMIT, PRIVATE_SGA
rem |                         
rem | Output:        Create_Profile.Sql 
rem |
rem | Revisions:     Prog Date     Version Comment/Change
rem |                ---- -------- ------- ----------------------------+
rem |             
rem +------------------------------------------------------------------+

Prompt
Prompt |               R E S O U R C E   P A R A M E T E R S
Prompt +-------------------------------------------------------------------------+
Prompt |      Please either supply an answer to the following parameters         |
Prompt |                  or press Enter to choose Default:                      |
Prompt +-------------------------------------------------------------------------+
Prompt | Note: These can take any one of these as an answer:                     |
Prompt |       - integer                                                         |
Prompt |       - UNLIMITED                                                       |
Prompt |       - DEFAULT   (press Enter)                                         |   
Prompt |                                                                         |
Prompt |  * Used in COMPOSITE_LIMIT                                              |   
Prompt |                                                                         |
Prompt | If CONNECT_TIME or IDLE_TIME exceeded, the current transaction rolls    |
Prompt | back and the session is ended. The next call returns an error.          |
Prompt |                                                                         |
Prompt | If other resources exceeded, the operation is aborted, the current      |
Prompt | statement is rolled back, and an error posted. Commit or rollback the   |
Prompt | current transaction, and then the session ends.                         |
Prompt |                                                                         |
Prompt | If single call limit exceeded, the operation is aborted, current state- |
Prompt | ment rolled back, an error returned. Current transaction is left intact |
Prompt +-------------------------------------------------------------------------+
Prompt

Accept SESSIONS_PER_USER         Default Default Prompt -        
'SESSIONS_PER_USER (# of concurrent sessions)                                       : '
Prompt
Accept CPU_PER_SESSION           Default Default Prompt -       
'CPU_PER_SESSION* (CPU time limit in 100th of seconds)                              : '
Prompt
Accept CPU_PER_CALL              Default Default Prompt -       
'CPU_PER_CALL (CPU time limit for a parse, execute, or fetch in 100th of seconds)   : ' 
Prompt
Accept CONNECT_TIME              Default Default Prompt -        
'CONNECT_TIME* (total session elapsed time limit in minutes)                        : '
Prompt
Accept IDLE_TIME                 Default Default Prompt -        
'IDLE_TIME (the permitted continuous inactive time during a session in minutes)     : '
/*       Does include long-running queries and other operations  */
Prompt
Accept LOGICAL_READS_PER_SESSION Default Default Prompt -
'LOGICAL_READS_PER_SESSION*  (# of data blocks read from memory and disk/session)   : '
Prompt
Accept LOGICAL_READS_PER_CALL    Default Default Prompt -
'LOGICAL_READS_PER_CALL (# of data blocks read to process a parse/execute/fetch)    : '
Prompt
Accept COMPOSITE_LIMIT           Default Default Prompt -
'COMPOSITE_LIMIT (Cpu_Per_Session+Connect_Time+Logical_Reads_Per_Session+Private_SGA: '
Prompt
Prompt +-------------------------------------------------------------------------+
Prompt | Note: PRIVATE_SGA can take any one of these as an answer:               |
Prompt |       - integer K|M|G|T|P|E                                             |
Prompt |       - UNLIMITED                                                       |
Prompt |       - DEFAULT   (press Enter)                                         |   
Prompt | (This only applies for shared server architecture.                      |
Prompt +-------------------------------------------------------------------------+
Accept PRIVATE_SGA               Default Default Prompt -
'PRIVATE_SGA* (space a session can allocate in the shared pool of the SGA           : ' 

Set Heading Off NewPage None Linesize 100 Term Off
Spool Create_Profile.Sql Append

Select 'SESSIONS_PER_USER            ' || '&SESSIONS_PER_USER'          From Dual;
Select 'CPU_PER_SESSION              ' || '&CPU_PER_SESSION'            From Dual;
Select 'CPU_PER_CALL                 ' || '&CPU_PER_CALL'               From Dual;
Select 'CONNECT_TIME                 ' || '&CONNECT_TIME'               From Dual;
Select 'IDLE_TIME                    ' || '&IDLE_TIME'                  From Dual;
Select 'LOGICAL_READS_PER_SESSION    ' || '&LOGICAL_READS_PER_SESSION'  From Dual;
Select 'LOGICAL_READS_PER_CALL       ' || '&LOGICAL_READS_PER_CALL'     From Dual;
Select 'COMPOSITE_LIMIT              ' || '&COMPOSITE_LIMIT'            From Dual;
Select 'PRIVATE_SGA                  ' || '&PRIVATE_SGA'                From Dual;

Spool Off

Set Term On