Thread: Geeks - Tip of the Day

Oracle Built-In packages - I

Dbms_Metadata.Get_Ddl
It is used to retrieve complete database object definitions (metadata) from dictionary. By specifying the type of object, for example, tables, indexes, or procedures optional selection criteria, such as owner etc.

Code:

 
EX:- set heading off; 
     set pages 999; 
     set long 5000; 
     
     select dbms_metadata.get_ddl('table','emp','cmsjan') 
     from dual; 
 
     Here table is object type 
          emp is object name 
          cmsjan is schema name.

To get whole schema table definitions

Code:

 
    set heading off 
    Set pagesize 0 
    set long 90000 
    set feedback off 
    spool cmsjanschema.sql 
 
    select dbms_metadata.get_ddl('table',x.table_name) 
    from user_tables x; 

    spool off; 
    set feedback on; 
    set heading on;

Oracle Built-in Packages - II

DBMS_UTILITY provides various utility procedures and functions.

In this package Get_time function provides elapsed time in seconds interval. Finds out the current time in 100th's of a second.

Code:

Example
   DECLARE
   time_before BINARY_INTEGER;
   time_after  BINARY_INTEGER;
   
   BEGIN
   time_before := DBMS_UTILITY.GET_TIME;
   CMSRPT_MONTHWISETARGET_INS('200607','200708','Sugar Cane');     
   time_after := DBMS_UTILITY.GET_TIME;
   
   DBMS_OUTPUT.PUT_LINE (time_after - time_before);
   
   END;

OUTPUT
SQL> @krk;
623

i.e. it takes 6.23 seconds time to execute the procedure.

Oracle Built-In Packages - IV

Using DBMS_RANDOM package functions and procedures we can generate random numbers , strings and dates.

The value function in dbms_random is used to generate random numbers. This function comes in two specifications

1. DBMS_RANDOM.VALUE( )
2. DBMS_RANDOM.VALUE( low IN NUMBER, high IN NUMBER)

Value function without arguments generates a random number greater than or equal to 0 and less than 1, with 38 digits of precision.

Code:

Ex:- SQL> SELECT dbms_random.value() FROM dual;
 
          DBMS_RANDOM.VALUE()
          -------------------
             .480358

VALUE function with two arguments generates a number in between low values and high value. The high value is not included as a possible value.

Code:

Ex:- The following function generates a random number in between 0 and 99

     SQL> SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) 
          FROM DUAL;
          
          TRUNC(DBMS_RANDOM.VALUE(0,100))
          -------------------------------
               92

Oracle Built-In Packages- V

The string function in dbms_random package generates random text string.

Syntax:- dbms_random.string(opt, len);

Here len determines length of the string

Opt Specifies What the returning string looks like. It can take any one of the following codes.
'u' or 'U' - returning string in uppercase alpha characters
'l' or 'L' - returning string in lowercase alpha characters
'a' or 'A' - returning string in mixed case alpha characters
'x' or 'X' - returning string in uppercase alpha-numeric characters
'p' or 'P' - returning string in any printable characters.

Code:

Ex:- 
 SQL> SELECT dbms_random.string('u', 10) StringEx 
      FROM dual;

      StringEx
      --------------------
      YYHFTNZAUU

We can also generate date random values using to_char function with Julian date format.For example, to generate random dates during the year 2007, first determine the date integer for January 1, 2007

Code:

SQL> SELECT TO_CHAR(TO_DATE('01/01/07','mm/dd/yy'),'J')
     FROM DUAL;

TO_CHAR
-------
2454102

Now we can use the above date integer 2454102 to generate random numbers using DBMS_RANDOM.VALUE with a low_value of 2452641 and a high_value of 2452641+364, and convert it to a date.

Code:

SQL> SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2454102,2454102+364)),'J') 
  2  FROM DUAL;

TO_DATE(T
---------
20-JUL-07

DDL Statements – Auto Commit

Auto commit or implicit commit will takes place before and after every DDL statement. DDL will always commit, even if it is unsuccessful (Run time errors). But it will not commit a transaction in case of syntax errors or semantic errors..

Example

Code:

SQL> CREATE TABLE TEST_DATA (X NUMBER);
Table created.

SQL> INSERT INTO TEST_DATA VALUES(1);
1 row created.

SQL> INSERT INTO TEST_DATA VALUES(2);
1 row created.


SQL> CREATE TABLE TEST_NEW (XX NUMBEER);
CREATE TABLE TEST_NEW (XX NUMBEER)
                          *
ERROR at line 1:
ORA-00902: invalid datatype
-- It is runtime  error. Auto commit will takes place.

SQL> ROLLBACK;
Rollback complete.

SQL> SELECT * FROM TEST_DATA;

        X
---------
        1
        2

Example for syntax error

Code:

SQL> TRUNCATE TABLE TEST_DATA;
Table truncated.

SQL> INSERT INTO TEST_DATA VALUES(1);
1 row created.
SQL> INSERT INTO TEST_DATA VALUES(2);
1 row created.

SQL> CREATE TABLETEST_NEW (XX NUMBER);
CREATE TABLETEST_NEW (XX NUMBER)
       *
ERROR at line 1:
ORA-00901: invalid CREATE command

-- It is syntax error. It will not commit transaction.
SQL> ROLLBACK;
Rollback complete.

SQL> SELECT * FROM TEST_DATA;
no rows selected

What is a datablock?

ORACLE database’s data is stored in data blocks.
One data block corresponds to a specific number of bytes of physical database space on disk.
A data block size is specified for each ORACLE database when the database is created.
The standard block size is specified by the initialization parameter DB_BLOCK_SIZE and is specified in INIT.ORA file .
ORACLE database’s data block can be of size 2K, 4K, 8k, 16K or 32K.
The default block size for oracle 10g is 8K.
A small block size is useful if you’re working with small rows and you’re doing a lot of index lookups.
Larger block sizes are useful in report applications when you’re doing large table scans.

A privilege is the right to execute a particular type of SQL statement or to access a database object owned by another user.

Oracle privileges are of two types

1. System privileges :- Enable user to perform particular task in the database

2.Object privileges:- Enable user to access or manipulate particular object in the database

Use grant command to add a privilege to user and revoke command to delete a privilege from user.

There are no cascading effects when a system privilege is revoked. But revoking object privilege has cascading effect.

i.e. If user X grant a system privilege with admin option to user y
User y grant a system privilege to user z.
User x revoke the system privilege from user y
still user z has the privilege that user y granted him

Where as in case of object privileges
If x user revoke the privilege from user y then the user z also losses the privilges granted by y.

You can’t grant object privileges on some schema objects, such as clusters, indexes, triggers, and database links. You control the use of these types of objects with a system privilege instead.

UNLIMITED TABLESPACE cannot be granted to a role.

There is no CREATE INDEX privilege. CREATE TABLE includes the CREATE INDEX and the ANALYZE commands

A control file records the physical structure of the database.
It contains
Name and location of datafiles and redo log files
Data base name
Time stamp of database creation.

Use the following query to find the size of control file
select sum(record_size) from v$controlfile_record_section

Query to find locked objects

select oracle_username, os_user_name,locked_mode,object_name,object_type
from v$locked_object a, dba_objects b
where a.object_id=b.object_id

To Disable Archive Log Mode

1. Shut Down the database

2. Comment Archive log destinations in parameter file
# LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
# LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc2”

3. Startup Mount

4. ALTER DATABASE NOARCHIVELOG;

5. Shutdown the database and take full offline backup.

How to export data based on a query?

EXP file=f1.dmp log=f1.log tables=y query=\"WHERE DEPTNO=10\"