Toad for Oracle - Issues Solutions Tricks and Tips

Toad for Oracle

I am using Toad for Oracle for long time for my projects. Here, I have listed out few tricks and tips and issues/solution which I faced during my work. I happy to share this post to help someone :)


1.    Is there a way to disable the popup for Statement Processing window?

The below solution for

In case it is happening at the moment and want to cancel, the solution is just press Alt+F4 button.
I faced this problem many times and finally I closed the entire toad window, it causes I wanted to lost other queries from other tabs.
Just I found this Alt+F4 solution.

How to disable  "Statement processing" dialog in future?

Yes, there is an option to disable this popup window.
    1.    Go to View -> Options -> Oracle -> Transactions
    2.    Click on "Execute queries in threads [Creates separate session]"
You have to do it before login into any database to activate this option.

Remember, if any query running background, it will be indicated by double arrow on the tab.

If you love to see the window back, just un-check the above option.


2.    How to display the query execution time?

    1.    Go to View -> Options -> SQL Editor
    2.    Click on "Persist display of execution time"
   
   
3.    How to resolve "Object xxx not found" when press Ctrl+Mouse click?
 
Solution:
    1.    Ctrl+A
    2.    Ctrl+X
    3.    Ctrl+V

I will keep updating this post.

PLSQL -Interview QA - 2012 - Part4

How to find dependencies referenced Inside PL/SQL Store Procedure

The Oracle data dictionary tracks the object types referenced in PL/SQL with the dba_dependencies view. To track the dependency among packages and tables, try this dictionary query:

select
  referenced_owner,
  referenced_name,
  referenced_type
from
  DBA_DEPENDENCIES
where
  name= 'YOUR_PROCEDURE or PACKAGE NAME' and   owner = 'SCOTT'
order by
  referenced_owner, referenced_name, referenced_type;
 

Finding PL/SQL compilation errors

PL/SQL does not always tell you about compilation errors.

SQL>SHOW ERRORS
No errors.

But how to see the error?

You have to give the full command as follows:

SQL>SHOW ERROR PROCEDURE


Show Errors syntax: 

Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE |
   PACKAGE BODY | TRIGGER | VIEW
   | TYPE | TYPE BODY | DIMENSION
   | JAVA SOURCE | JAVA CLASS } [schema.]name] 
 
   
Finding Record count of all tables?
 
   PROMPT
     PROMPT ROWCOUNT for Table &Table_Name.

     SET FEEDBACK OFF
     SET SERVEROUTPUT ON
     DECLARE N NU MBER ;
     V VARCHAR2(100) ;
     BEGIN
     V := 'SELECT COUNT(*) FROM ' || UPPER('&Table_Name.') ;
     EXECUTE IMMEDIATE V INTO N ;
     DBMS_OUTPUT.PUT_LINE (N);
     END;
     /

SQL>SELECT COUNT(*) FROM EMP;
14 Records.   
SQL>SELECT COUNT(*) FROM DEPT;
4 Records.   
...
...

How do you find when an object was created , last modified date?

SELECT OWNER, OBJECT_NAME, CREATED, LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'enter_object_name_here';
   
   
Sending E-Mail in PL/SQL

Oracle allows us to send email from PL/SQL. You can create a procedure to send mail as follows:

CREATE OR REPLACE PROCEDURE SEND_MAIL (
    msg_from     VARCHAR2 := 'sender@domain.com'
  , msg_to       VARCHAR
  , msg_subject  VARCHAR2 := 'E-Mail message from your database'
  , msg_text     VARCHAR2 := ''
)
IS
  c   UTL_TCP.CONNECTION;
  rc  INTEGER;
BEGIN
  c  := UTL_TCP.OPEN_CONNECTION('localhost', 25);       -- open the SMTP port 25 on local machine
  rc := UTL_TCP.WRITE_LINE(c, 'HELO localhost');
  rc := UTL_TCP.WRITE_LINE(c, 'MAIL FROM: '||msg_from);
  rc := UTL_TCP.WRITE_LINE(c, 'RCPT TO: '||msg_to);
  rc := UTL_TCP.WRITE_LINE(c, 'DATA');                  -- Start message body
  rc := UTL_TCP.WRITE_LINE(c, 'Subject: '||msg_subject);
  rc := UTL_TCP.WRITE_LINE(c, '');
  rc := UTL_TCP.WRITE_LINE(c, msg_text);
  rc := UTL_TCP.WRITE_LINE(c, '.');                     -- End of message body
  rc := UTL_TCP.WRITE_LINE(c, 'QUIT');
  UTL_TCP.CLOSE_CONNECTION(c);                          -- Close the connection
EXCEPTION
  WHEN others THEN
    RAISE_APPLICATION_ERROR(-20000, 'Unable to send e-mail message from PL/SQL routine.');
END;

SQL>exec send_mail('sender@domain.com','recipient@domain.com','Test message from first PLSQL','your message here ');

PL/SQL -Q&A - 2011 - Part 3

What is difference between Materialized View and Materialized Log?

Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse or in multi-master replication environment.

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases.

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.


Materialized View Log - When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.


When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must re-execute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.

To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.


Difference between Bigfile & Smallfile?

Big and small file tablespaces in the Oracle 10g database
It is the Oracle Database 10g feature. A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size and on the other hand, a smallfile tablespace can contain many data files.
The size of a bigfile can reach to 128TB depending on the Oracle block size.

An Oracle database can contain both bigfile and smallfile tablespaces.
You can change the default tablespace type to BIGFILE or SMALLFILE.


How to sorting (Order By) LOB column data?
DBMS_LOB.SUBSTR is used to sort the LOB column


Can you give an example for the above?
Yes.

SQL> create table tab1(col1 number, col2 clob);
Table created.


SQL> insert into tab1 values (1,'this is a test record fro clob column');
1 row created.


SQL> insert into tab1 values (2, 'alangottai');
1 row created.


SQL>  insert into tab1 values (3,'mannargudi');
1 row created.


SQL> select * from tab1 order by col2;
select * from tab1 order by col2
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes


SQL> select col1, dbms_lob.substr(col2,50,1) from tab1 order by 2;
         col1 DBMS_LOB.SUBSTR(col2,50,1)
---------- ----------------------------------------------
         2 alangottai
         3 mannargudi
         1 this is a test record fro clob column
 

What are the uses of database trigger?

• Automatically generate derived column values
• Enforce complex security authorizations
• Prevent invalid transactions
• Enforce referential integrity across nodes in a distributed database
• Enforce complex business rules
• Provide transparent event logging
• Provide auditing
• Maintain synchronous table replicates
• Gather statistics on table access
• Modify table data when DML statements are issued against views

Difference between Row Level Trigger and Statement Trigger?

Row Level Trigger:
A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.

Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. For example, Figure 22-2 illustrates a row trigger that uses the values of each row affected by the triggering statement.

Statement Triggers
A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:

Make a complex security check on the current time or working hours, weekday, or user
Generate a single audit record.

PL/SQL -Q&A2

What is Raise_application_error ?

Raise_application_error is a procedure of package DBMS_STANDARD which allows to
issue an user_defined error messages from stored sub-program or database trigger.


What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occured.
SQLERRM returns the relevant error message of the SQLCODE.



Where the Pre_defined_exceptions are stored ?

In the standard package.
Procedures, Functions & Packages ;



What is difference between a PROCEDURE & FUNCTION ?

A FUNCTION is always returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not
Return at all.



What are advantages of Stored Procedures?

Extensibility, Modularity, Reusability, Maintainability and one time compilation.



What are the modes of parameters that can be passed to a procedure ?

IN,OUT,IN-OUT parameters.

What are the two parts of a Pakage?
Pakage Specification and Pakage Body.



Give the structure of the procedure ?

PROCEDURE name (parameter list.....)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;

Give the structure of the function ?
FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;



Explain how procedures and functions are called in a PL/SQL block ?

Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');
26. What is Overloading of procedures ?
The Same procedure name is repeated with parameters of different datatypes and
parameters in different positions, varying number of parameters is called overloading of
procedures.

e.g. DBMS_OUTPUT put_line



What is a package ? What are the advantages of packages ?

Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Applicaton Design, Information.
Hiding,. reusability and Better Performance.



What are two parts of package ?

The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.

Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.


What is difference between a Cursor declared in a procedure and Cursor
declared in a package specification ?

A cursor declared in a package specification is global and can be accessed by other
procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by
other procedures.
30. Name the tables where characteristics of Package, procedure and functions are stored ?
User_objects, User_Source and User_error.


What is an Exception ? What are types of Exception ?

Exception is the error handling part of PL/SQL block. The types are Predefined and
user_defined. Some of Predefined execptions are.

CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR<>INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOU RCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.

PL/SQL -Q&A1

What is PL/SQL ?

PL/SQL is a procedural language that has both interactive SQL and procedural
programming language constructs such as iteration, conditional branching.


What is the basic structure of PL/SQL ?

PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks
can be used in PL/SQL.

Declarative part, Executable part and Execption part.

Declaration
;
Begin
Exception
End;


What are the datatypes a available in PL/SQL ?

Some scalar data types such as
NUMBER,VARCHAR2,DATE,CHAR,LONG,BOOLEAN.
Some composite data types such as RECORD & TABLE.


 What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?

% TYPE provides the data type of a variable or a database column to that variable.

% ROWTYPE provides the record type that represents a entire row of a table or view or
columns selected in the cursor.

The advantages:
i. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable
changes accordingly.


What is difference between % ROWTYPE and TYPE RECORD ?

% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables.

E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type );
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.


What is PL/SQL table ?

Objects of type TABLE are called "PL/SQL tables", which are modelled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.

What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed.
Cursors are required to process rows individually for queries returning multiple rows.

Explain the two type of Cursors ?

There are two types of cursors, Implict Cursor and Explicit Cursor.
PL/SQL uses Implict Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.

What are the PL/SQL Statements used in cursor processing ?

DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

What are the cursor attributes used in PL/SQL ?

%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows featched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are featched.
% NOT FOUND - to check whether cursor has featched any row. True if no rows are featched.
These attributes are proceded with SQL for Implict Cursors and with Cursor name for Explict Cursors.

What is a cursor for loop ?

Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches
rows of values from active set into fields in the record and closes when all the records have
been processed.

eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;


Explain the usage of WHERE CURRENT OF clause in cursors ?

WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest
row fetched from a cursor.

What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an
oracle error. To get an error message of a specific oracle error.

e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

Useful Scripts Collection Part 1

Do you have your own Blog or Website? Try PuppyURL - Free and Automated Directory Submission.


SCRIPT COLLECTION - How-to find global name of your database?
ora9i$maran> select * from global_name;

GLOBAL_NAME
-------------------------------------------------------
tifa.prod.com.sg

find Primary key constraints for a given table name?
ora9i$maran> Select
2 c.CONSTRAINT_NAME as PCN , c.TABLE_NAME PTN, c.COLUMN_NAME PCOL
3 from
4 USER_CONS_COLUMNS C ,user_constraints T
5 where
6 t. CONSTRAINT_NAME= c.CONSTRAINT_NAME AND
7 t.TABLE_NAME =c.TABLE_NAME AND
8 T.CONSTRAINT_TYPE='P' AND
9 c.table_name = '&TNAM'
10 /
Enter value for tnam: RIGHTS
PCN ; PTN ; PCOL
----------------------------------------------------------------------
SYS_C001420 RIGHTS &n bsp; RIGHTS_ID

find available indexes for a given table name?
ora9i$maran> select table_name||' - '|| COLUMN_NAME||' - '|| INDEX_NAME
2 from user_ind_columns where table_name like UPPER('%&TN%')
3 /
TABLE_NAME||'-'||COLUMN_NAME||'-'||INDEX_NAME
------------------------------------------------- -------
ACCT_CODE_MAS$ - ACCT_ID - ACCT_ID$_PK
ACCT_TRANS_MAS$ - ACCT_TRANS_ID - ACCT_TRANS_ID$_PK

find Primary key and Unique key for a given table?
SQL> SELECT B.TABLE_NAME||' - '||B.COLUMN_NAME||' -'||B.CONSTRAINT_NAME||' '|| DECODE(A.CONSTRAINT_TYPE, 'P', ' Primary Key ','U', ' Unique')
3 FROM
4 USER_CONSTRAINTS A, USER_CONS_COLUMNS B
5 WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND
6 A.TABLE_NAME = B.TABLE_NAME AND
7 A.CONSTRAINT_TYPE in ('U','P') and
8 A.TABLE_NAME LIKE upper('%&tn')
9 /
Enter value for tn: ROLE
old 8: A.TABLE_NAME LIKE upper('%&tn')
new 8: A.TABLE_NAME LIKE upper('%ROLE')
B.TABLE_NAME||'-'||B.COLUMN_NAME||'-'||B.CONSTRAINT_NAME||''||DECODE(A.CONSTRAIN
-------------- ------------------------------------------------------------------
ROLE_MAS$ - ROLE_ID – ROLL_MAS_PK Primary Key
ROLE_MAS$ - DESCRIPTION - ROLL_MAS_DESC_U Unique

find the object created date?
ora9iAS$maran@TIFA> select OBJECT_NAME||' - '||OBJECT_TYPE||' - '||CREATED from
2 user_objects where OBJECT_NAME like upper('%&objname%')
3 /
Enter value for objname: COUNTER_MAS$
old 2: user_objects where OBJECT_NAME like upper('%&objname%')
new 2: user_objects where OBJECT_NAME like upper('%COUNTER%')
OBJECT_NAME||'-'||OBJECT_TYPE||'-'||CREATED
--------------------------------------------------------------------------------
COUNTER_MAS$ - TABLE - 12-APR-07
COUNTER__MAS$_BCK - TABLE - 12-APR-07

find all schemas and its created date?
ora9iAS$maran@TIFA> select username,CREATED from all_users order by 2,1;
USERNAME CREATED
------------------------------ ---------
SYS 23-NOV-04
SYSTEM 23-NOV-04
TIFA 23-NOV-04
find reference tables of a given primary table?

ora9iAS$maran@TIFA> Select c.TABLE_NAME FTN
2 from
3 USER_CONS_COLUMNS C ,user_constraints T
4 where
5 t. CONSTRAINT_NAME= c.CONSTRAINT_NAME AND
6 t.TABLE_NAME = c.TABLE_NAME AND
7 T.CONSTRAINT_TYPE='R' AND
8 t.R_CONSTRAINT_NAME = (SELECT c.CONSTRAINT_NAME as PCNfrom
9 USER_CONS_COLUMNS C ,user_constraints T
10 where
11 t. CONSTRAINT_NAME= c.CONSTRAINT_NAME AND
12 t.TABLE_NAME =c.TABLE_NAME AND
13 T.CONSTRAINT_TYPE='P' AND
14 c.table_name = '&TNAM')
15 /
Enter value for tnam: COUNTER_MAS$'
old 14: c.table_name = '&TNAM')
new 14: c.table_name = 'COUNTER_MAS$')
FTN
------------------------------
DAILYTRANSACTION_DET$
LOGIN_LOG_DET$

find server ip address?
ora9iAS$maran@TIFA> SELECT UTL_INADDR.get_host_address from dual;
GET_HOST_ADDRESS
------------------------------------------------------------------------------ --
192.168.1.200

find last number of a given sequence?
ora9iAS$maran@TIFA> select SEQUENCE_NAME, LAST_NUMBER from user_sequences where upper(SEQUENCE_NAME) like upper('%&sn%');
Enter value for sn: BANK
old 2: where upper(SEQUENCE_NAME) like upper('%&sn%')
new 2: where upper(SEQUENCE_NAME) like upper('%BANK%')
SEQUENCE_NAME LAST_NUMBER
------------------------------ -----------
BANK_ID_SEQ 1

SQLLOADER:
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. SQL*Loader supports various load formats, selective loading, and multi-table loads.
Can I load more than one input file at the same time?
Yes.
LOAD DATA
INFILE 'C:empcsv1.csv'
INFILE 'C:empcsv2.csv'
INFILE 'C:empcsv3.csv'
TRUNCATE INTO TABLE EMP
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO,
ENAME,
SAL
)

You need to DBA privilege to run the following queries:

find out the objects created in SYSTEM tablespace other than SYSTEM, SYS users?
ora9i$maran> SELECT TABLE_NAME, OWNER from dba_tables
2 where TABLESPACE_NAME= 'SYSTEM' and
3 OWNER NOT IN ('SYSTEM','SYS');
TABLE_NAME OWNER
------------------------------------------------------------
COUNTER_MAS$ TIFA
RIGHTS_DET TIFA
2 rows selected.
ora9i$maran>

find size of the database?
ora9i$maran> select sum(bytes)/1024/1024 "Total DB size in Meg" from sys.v_$datafile;
Total DB size in Meg
--------------------
2543.4375

find segment size greater then 1 MB?
ora9i$maran> select OWNER||' - '||SEGMENT_NAME||' - '||(BYTES/1024)/1024||' MB'
2 from dba_segments where (BYTES/1024)/1024 >=1 AND
3 OWNER like UPPER('%&OWNER%')
4 /
Enter value for owner: UCB
OWNER||'-'||SEGMENT_NAME||'-'||(BYTES/1024)/1024||'MB'
---------------------------------------- ------------------------------------------
UCB - TERM1_DB - 6.515625 MB
UCB - TERM1_XL - 6.4609375 MB

find all schema size?
ora9i$maran> select owner , sum(bytes/1024/1024) MB
2 from dba_segments
3 group by owner order by 2
4 /
OWNER MB
----------------------------------------
KENT .0625
SYSTEM 3.34375
VIP 57.96875
CVB 263.25
ER_CVB 344.460938
SYS 391.492188

find users those who have DBA privilege?
ora9i$maran> select grantee "DBA Privilege"
2 from dba_role_privs
3 where granted_role='DBA';
DBA Privilege
------------------------------
SYS
SYSTEM

move objects from current tablespace to another
REM create a file name with movetsall.sql using this content
SET FEEDBACK OFF;
SET HEADING OFF;
SET ECHO OFF;
SET PAGESIZE 50000;
SET LINESIZE 100;
CLEAR SCREEN;
column movets new_value movets noprint
select 'c:'||'Move_Tablespace_Script_'||USER||'_'||to_char(sysdate,'DDMMYYYY')||'.SQL' movets from DUAL;
spool && movets
select decode( segment_type, 'TABLE',
segment_name, table_name ) order_col1,
decode( segment_type, 'TABLE', 1, 2 ) order_col2,
'alter ' || LOWER(segment_type) || ' ' || segment_name ||chr(10) ||
decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
' tablespace &Taget_Tablespace_Name ' ||chr(10) ||
' storage '||chr(10) ||
' ('||chr(10) ||
' initial ' || initial_extent || ' next ' ||next_extent ||chr(10) ||
' minextents ' || min_extents || ' maxextents ' ||max_extents ||chr(10) ||
' pctincrease ' || pct_increase || ' freelists ' ||
freelists ||chr(10) ||
' );'
from user_segments,
(select table_name, index_name from user_indexes )
where segment_type in ( 'TABLE', 'INDEX' )
and segment_name = index_name (+)
order by 1, 2
REM edit 'c:Move_Tablespace_Script__.SQL’, modify as needed and run it.