Ask Anantha

I googled for this and got this from some website. Do not remember the website from where I got this query. Thanks for the original author . Pasting the query for my personal records: SELECT owner,        table_name,        trunc(SUM(bytes) / 1024 / 1024) meg FROM   (SELECT segment_name table_name,                owner,                bytes         FROM   dba_segments         WHERE  segment_type = 'TABLE'         UNION ALL         SELECT i.table_name,                i.owner,                s.bytes         FROM   dba_indexes  i,                dba_segments s         WHERE  s.segment_name ...

I have a data in CSV format (comma separated values) like follows: first,second,third,fourth,fifth  I want the output as follows: column_value first second third fourth fifth The easiest way to achieve is to use a CONNECT BY clause in DUAL table and to populate the results. Here is the query: SELECT substr(str, instr(str, ',', 1, LEVEL) + 1, instr(str, ',', 1, LEVEL + 1) - instr(str, ',', 1, LEVEL) - 1) column_value FROM   ( SELECT ',' || '&mystring' || ',' str  FROM  dual) CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) - 1; Pass value   first,second,third,fourth,fifth to mystring.

Question:  How to get the last password changed time for a oracle user? Answer: The SYS view user$ consists of a column PTIME which tells you what was the last time the password was changed for the user. Try this query: SELECT name,        ctime,        ptime FROM   sys.user$ WHERE   name = ' USER-NAME '; Note: Replace USER-NAME with the user name which you want to know the information. CTIME Indicates - Creation Time PTIME Indicates - Password Change Time Here's the table DESC ription: Name         Type                Nullable Default Comments  ------------ ------------------- -------- ------- --------  USER#        NUMBER                                         NAME         VARCHAR2(30 BYTE)   ...

This question I have frequently faced while developing packages/procedures in PL/SQL in Oracle.  Question: How will I identify who has locked a procedure or a package. I need to identify this because it should not hang after the compilation is issued? Answer: You may see the details from v$sql  with the search text (Your package name). If the query fetches results then join it with v$session to get the session information. select *  from v$sql, v$session where sql_address=address and upper(sql_text) like '%PACKAGE_NAME%'; Hope this simple tip saves you some time.

Tip Courtesy: Oracle Tips by Burleson Consulting (dba-oracle) Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques. This post shows some examples of using SQL to delete duplicate table rows using an SQL sub query to identify duplicate rows, manually specifying the join columns: DELETE FROM    table_name A WHERE   a.rowid >    ANY (      SELECT         B.rowid      FROM         table_name B      WHERE         A.col1 = B.col1      AND         A.col2 = B.col2         ); For a script that does not uses ROWID concept, read this post. Deleting /Listing duplicate records without ROWID

There is no oracle defined function for getting size of a table. After all if it is easy with one simple query who will require a function. Isn't it? Anyway you can choose to save this query as a function for easy retrieval. select segment_name table_name, sum(bytes)/(1024*1024) table_size_meg from user_extents where segment_type='TABLE' and segment_name = '&table_name' group by segment_name; Read more on what all to remember while getting the size of a table. Click here Create your own function for the purpose: CREATE OR REPLACE FUNCTION get_table_size (t_table_name VARCHAR2)RETURN NUMBER IS l_size NUMBER; BEGIN SELECT sum(bytes)/(1024*1024) INTO l_size FROM user_extents WHERE segment_type='TABLE' AND segment_name = t_table_name; RETURN l_size; EXCEPTION WHEN OTHERS THEN RETURN NULL; END; / Example: SELECT get_table_size('EMP') Table_Size from dual ; Result: Table_Size 0.0625

I was hunting for an article when my friend came up and asked me this question. How to list/delete duplicate records without using ROWID? I never asked why he do not want to use ROWID, because I was caught in some thinking. I done some googling, and derived the method, but first the ROWID method First how to delete records with ROWID, and why? ROWID is the unique record identifier within Oracle, and it is easy to get the minimum of the ROWID for the duplicating criteria and delete the rest of them. We will take the example of EMP table for simplicity. Assume that there is no primary key in the Employee_Id column due to some data migration requirements. Once the data migration is over I need to check the existense of duplicate records. This is how traditionally we used to achieve this: Suppose the data after migration stands like this: Employee_Id Employee_Name 10          Scott 11          Tige...

Starting with Release 7.1 of Oracle, users have had access to a feature called an inline view. An inline view is a view within a query. Using this feature, you can easily accomplish your task. Example: Show percentage of salaries for each department Every row in the report must have access to the total sum of sal. You can simply divide sum (sal) by that total, and you'll have a number that represents the percentage of the total. column percentage format 99.9 select deptno, sum(sal),sum(sal)/tot_sal*100 "PERCENTAGE" from emp, (select sum(sal) tot_sal from emp) group by deptno, tot_sal; With Oracle8i Release 2 (8.1.6 and higher), you can calculate percentages by using the new analytic functions as well. The query using an analytic function might look like this: column percentage format 99.9 select deptno, sum(sal), (ratio_to_report(sum(sal)) over())*100 "PERCENTAGE" from emp group by deptno; The query produces the same answer—but it does so more efficiently, becau...

To find out which queries are running in database, login as SYS and issue the following query:   SELECT a.USERNAME, a.STATUS, b.sql_text FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.SQL_ADDRESS= b.ADDRESS;   V$SESSION view lists session information for each current session.   To see field-wise description for V$SESSION follow the link below: V$SESSION V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution. To see field-wise description for V$SQLAREA follow the link below: V$SQLAREA

Test Your PL/SQL Knowledge This puzzler has come from Steven Feuerstein for the month of January 2008. So I thought to reproduce the puzzler with its answer: Answer the following multiple choice question to see how well you understand the nuances of PL/SQL: Which of the following do not help you execute multiple PL/SQL programs simultaneously? Oracle Advanced Queuing DBMS_JOB DBMS_SQL Pipelined Functions Now for the answer scroll down: . . . . . . . . . . . . . . . . . Answer: (c) DBMS_SQL . Both Advanced Queuing and DBMS_JOB provide mechanisms for communicating with other sessions, thereby allowing you to "kick off" multiple PL/SQL programs at the same time. Pipelined functions are a special type of table functions: functions that can be called in the FROM clause of a query. If you include the PARALLEL_ENABLE clause in the header of a pipelined function, then the Parallel Query engine will be abl...

This query has become the mostly asked question in any Oracle technical Interview. Following is an excerpt tip which comes from Ramcharan Karthic , software engineer in Bangalore , India .   Note: This tip was published in the January/February 2003 issue of Oracle Magazine .   This Select query will help you select the Nth Max or Min value from any table.   For example, consider a table TAB1 in which you want to find the Nth Max or Min from the column, COL1.   First, the query for Max:   SELECT * FROM TAB1 a WHERE &N = (SELECT count (DISTINCT (b.col1)) FROM TAB1 b WHERE a.col1<=b.col1)   Next, the query for Min:   SELECT * FROM TAB1 a WHERE &N = (SELECT count (DISTINCT (b.col1)) FROM TAB1 b WHERE a.col1>=b.col1)   If N=1 will return first max or first min. N=2 will return second max or min.

The following query if run will fetch Free space, Used space and Total space of each tablespace available.   SELECT Total.name "Tablespace Name", Free_space , ( total_space-Free_space ) Used_space , total_space FROM   ( select tablespace_name , sum(bytes/1024/1024) Free_Space   from sys.dba_free_space   group by tablespace_name   ) Free,   ( select b.name , sum(bytes/1024/1024) TOTAL_SPACE   from sys.v_$datafile a, sys.v_$tablespace B   where a.ts # = b.ts #   group by b.name   ) Total WHERE Free.Tablespace_name = Total.name;   This tip comes from Lazydba

The pl/ sql package utl_file allows me to create, read and write flat files at the O/S level on the server. Also the dbms_lob package allows me to read files from the server and load them into the database. But how do I delete an O/S file from within pl/ sql after I have finished with it. One 'near- soultion ' is to use the utl_file package to re-open the file for writing (without the append option), and then close the file without writing to it. This recovers most of the disk space, but still leaves the file on the system as an empty O/S file. Another approach is to write a short piece of Java, which can then be called from PL/SQL. Java currently offers far more flexibility than PL/SQL when dealing with O/S files, for example you could use Java to invoke and load a directory listing from PL/SQL so that you know what files exist for deletion. (See further reading). A pure simple PL/SQL solution, however, appears to exist in the dbms_backup_restore package. This...

In 10g there is a new procedure hidden away in the undocumented DBMS_BACKUP_RESTORE package. It's called searchfiles , which is a bit of a giveaway and appears to have been introduced for the new backup features in 10g, as RMAN now needs to know about files in the recovery destination. Calling this procedure populates an in memory table called x$krbmsft , which is one of those magic x$ tables, the only column which is of relevance to us is fname_krbmsft which is the fully qualified path and file name. This x$ table acts in a similar fashion to a global temporary table in that its contents can only be seen from the calling session. So two sessions can call searchfiles and each can only see the results of their call (which is extremely useful). The code sample below will only really run as sys , due to the select from x$krbmsft , it's just intended as a demo. The first two parameters in the call to searchfiles are IN OUT so must be defined as variables, even though the secon...

The connection between constraints and the indexes which are used to check these constraints for the current user can be described by this query: select --+ rule o.owner index_owner, o.object_name index_name, n.name constraint_name from sys.cdef$ c, dba_objects o, sys.con$ n where c.enabled = o.object_id and c.con# = n.con# and n.owner# = uid; If you leave away the condition and n.owner# = uid you get all the constraints. You may further limit this query to your constraint name by adding the condition and n.name = 'your_constraint_name' . Why can indexes and constraints be so different? In particular, you may use for example an index on columns (c, a, b) to enable a unique constraint on columns (a, b, c). Remember a constraint is a logical structure whereas an index is a physical one. So a unique or a primary constraint just describe the uniqueness. If (c, a, b) is unique then all other permutations are...

Here is the query to find all dates and the days in a current year: select mydate,to_char(mydate,'Day') from( select (level-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from dual connect by level Note: You must be running 10g to get the desired output. For a non 10g version see below the query: select mydate,to_char(mydate,'Day') from( select (rownum-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from all_objects where rownum The above query assumes that all_objects returns at least 365 records.

* Explain Plan is a statement that lets you to have execution plan for any SQL statement without actually executing it. You will be able to examine the execution plan by querying the plan table. * A Plan table holds execution plans generated by Execute plan statement. Typical name is plan_table but any name can be given for a plan table. * utlxplan.sql file in $ORACLE_HOME/rdbms/admin contains script to create plan table. * Privileges required for Explain Plan: -> INSERT privilege for Explain Plan -> EXECUTE privilege for statement execution -> SELECT privilege on underlying table/view Syntax: EXPLAIN PLAN [SET STATEMENT_ID = {string in single quotes} ] [INTO {plan table name} ] FOR {SQL statement}; Output extraction: select id, parent_id, LPAD('',LEVEL-1)|| operation ||' '||options operation, object_name from plan_table where statement_id = '&statement_id' start with id=0 and statement_id = '&statement_id' connect by prior ...

Use this query to get the record count of all tables in a schema. select table_name, to_number( extractvalue( xmltype( dbms_xmlgen.getxml('select count(*) c from '||table_name) ),'/ROWSET/ROW/C')) count from user_tables order by 1 The output is like table_name count ----------- -------- DEPT 4 EMP 14 Courtesy: http://laurentschneider.com/wordpress/2007/04/ how-do-i-store-the-counts-of-all-tables.html

Use this query to convert any length string in comma separated format to rows: SELECT SUBSTR('AA,BBB,C,D,E,F,G,H', INSTR(CHR(44)||'AA,BBB,C,D,E,F,G,H'|| CHR(44),',',1,LEVEL), INSTR('AA,BBB,C,D,E,F,G,H'||CHR(44),',',1,LEVEL)- INSTR(CHR(44)||'AA,BBB,C,D,E,F,G,H'|| CHR(44),',',1,LEVEL)) FROM DUAL CONNECT BY LEVEL LENGTH(REPLACE('AA,BBB,C,D,E,F,G,H',','))+1

Say for example we have a string 'A,B,C,D,E,F'. We would like to have it printed in separate lines say A B C D E F Like so. Now we can use a simple SQL statement to convert the same. SELECT SUBSTR('A,B,C,D,E,F', INSTR('A,B,C,D,E,F',',',1, LEVEL)-1,1) FROM DUAL CONNECT BY LEVEL REPLACE('A,B,C,D,E,F',',')) To elaborate the technique, we have used the following logic: 1. Used CONNECT BY statement to find out how many rows are necessary 2. INSTR to find out the place of each row 3. SUBSTR to cut the string between the comma Well what was the assumptions before using this query. The string between comma are having length of one. Any number of strings can be given in this fashion.