SQL & PL/SQL :: Rows From Each Table In DB
May 5, 2011Scenario like this , I have a dB which contain 100 tables, i want to find out each table contain how many rows excluding
system tables ..
how can i write query for this.
ADVERTISEMENT
SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?
Jun 12, 2012primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
[code]...
SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins
Sep 24, 2010We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.
View 11 Replies View RelatedDelete 4 Millions Of Rows From A Table Having 25 Millions Of Rows?
Dec 14, 2007I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.
View 4 Replies View RelatedPL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows
Aug 22, 2012what are the collections available in Oracle Plsql, what are concepts of collection.
How to Transpose a Table from rows to columns or columns into rows.
DDL and DML concepts.
What is the concepts of statistics in Oracle Plsql.
Purging Table Which Is Having 98 M Rows
Dec 3, 2012I want to purge a table which is having more then 98M rows...here are the details...
Purge Process I followed
---------------------------------------------
Step 1. Created backup table from Main table with required data only
create table abc_98M_purge as
select * from abc_98M where trunc(tran_date)>='01-jul-2011'
-> table created with 5325411 rows
Step 2. truncate table abc_98M
Step 3. inserted all 5325411 rows back to abc_98M from abc_98M_purge using below procedure
DECLARE
TYPE ARRROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
tbrows ARRROWID;
row PLS_INTEGER;
cursor cur_insert is select rowid from abc_98M_purge order by rowid;
BEGIN
open cur_insert;
loop
[code]....
SQL & PL/SQL :: How To Delete Rows From Table
Oct 19, 2011I am using the below proc to delete some records
1)select client_id,count(*) from TCLIENT_NOTIFICATION_PACK where client_id=1620560178 group by client_id order by 2 desc;
client_id count(*)
----------- ---------
16205601785128
2)select client_id, count(*) from TCLIENT_NOTIFICATION_PACK
where client_id=1620560178
group by client_id
having count(*) > 40
order by 2 desc
client_id count(*)
----------- ---------
16205601785128
3) select client_id,clnt_notification_pack_tid
-- bulk collect into v_client_id,v_notif_tid
from (select clnt_notification_pack_tid,
client_id,
clnt_notification_pack_typ_tid,
crte_dt,
[code]....
4) Iam using the below proc to delete the rows from table, except the 4 rows returned above
declare
v_clnt_notification_pack_tid TCLIENT_NOTIFICATION_PACK.CLNT_NOTIFICATION_PACK_TID%type;
tYPE t_client_id is table of TCLIENT_NOTIFICATION_PACK.client_id%type;
tYPE t_notif_tid is table of TCLIENT_NOTIFICATION_PACK.clnt_notification_pack_tid%type;
v_client_id t_client_id;
v_notif_tid t_notif_tid;
[code]....
5) After running this procedure, i shud see 5124 records, but i see zero records.
SQL & PL/SQL :: How To Display All The Rows In A Table
Sep 8, 2010I am bit confused to display all the rows in a column using stored procedure like
In sqlserver:
create procedure test
as
begin
select * from table;
end
in sqlserver it is working perfectly.
In oracle:
create or replace procedure test
is
begin
select * from table;
end;
But in oracle i am getting an error like select * into something like that.I understand the error. i.e. i need to move the column values into variables.if it a single row then there will be no problem.i want to display all the rows in the table at one time.i can not use cursors.
SQL & PL/SQL :: Rows Of A Table Updated
Jul 17, 2012write a query find which rows of a table is updated on 2 days before?
(OR)
In table there r so many rows write a query which two rows r updated last two day before?
SQL & PL/SQL :: Delete Even Rows Of A Table
Oct 25, 2006How to delete even rows of a table or rather alternate rows of a table.
View 31 Replies View RelatedSQL & PL/SQL :: How To Delete 1% Of Rows From A Table
Jun 13, 2011I have a table, in that i have "n" no of rows from that i want to delete 1% of rows from that table
View 26 Replies View RelatedPL/SQL :: Reduce Rows In A Big Table
Dec 19, 2012I have one very big table , says employee table, I need to keep less rows to do this
1. create table employee_backup as select * from employee
2. truncate table employee
3. insert into employee select * from employee_backup where rownum < 1000
is #3 sript is OK? if I only want 1000 rows ?
PL/SQL :: Fetch 1 Lac Rows From Table
Dec 15, 2012OS : widows 2003
Oracle 9i release 2
I need to Fetch 1 lakh rows from table (all rows) in fastest way.
How to do it?
How To Select 10 Rows From Mission Table
Sep 2, 2008i want the 10 most recent mission date from mission table . i have tried using rownum but could not get it. TOP does not work
select * from
(select length(code_name) leng from missions
where length(code_name) >7
order by mission_date)
where rownum = 10
;
this does not work ::: no rows are selected and i do rownum = 1 then gives me one record and when i do rownum>=1 gives me all records
Convert Rows To Columns In A Table?
Apr 24, 2012How do I convert rows in oracle table to columns by writing a query?
Here is my table.
EID QNo Qual YOP
101 1 B.Tech 2004
101 2 M.Tech 2006
102 1 B.Tech 2003
102 2 M.Tech 2005
102 3 MBA 2007
Now I want the above table in the following format......
EID Qual1 YOP1 Qual2 Yop2 Qual3 Yop3
101 B. Tech 2004 M.Tech 2006
102 B. Tech 2003 M.Tech 2005 MBA 2007
I have maximum of 8 Qualifications, how to achieve this in oracle.
Import And Export Table With Around 5 Lac Of Rows
Sep 18, 2013I have oracle database 11.2.0.3 OS Hp-ux I have one table with around 5 lac of rows I want to copy this table to another oracle database 11.2.0.3 with different OS windows server 2008 R2.
View 3 Replies View RelatedDisplaying Rows Into Columns In A Table
Oct 20, 2012I have table having below records.
empno ename deptno
101 a 10
102 b 20
103 c 10
104 d 20
105 e 30
Normal Output
deptno count(*)
-----------------
10 2
20 2
30 1
I want to display like this(rows into columns)
--------------------------------------------------------
Required Output
-------------
10 20 30
2 2 1
SQL & PL/SQL :: Joining Subsequent Rows In A Table
Mar 29, 2010I create a second "DATE_PAIRS" table, and join a few subqueries. It works with conditions (ID=1), but scanning the whole table (2+ million records) is abysmal.
Table:
ID Date Price
---------------------
1 2010/1/1 100
1 2010/1/2 125
1 2010/1/5 115
2 2010/1/1 115
2 2010/1/2 105
2 2010/1/5 125
I'd like to return
ID Date1 Date2 Price1 Price2
--------------------------------
1 2010/1/1 2010/1/2 100 125
1 2010/1/2 2010/1/5 125 115
etc ..
SQL & PL/SQL :: How To Remove Duplicate Rows From Table
May 8, 2013how to remove duplicate rows from table?
View 6 Replies View RelatedSQL & PL/SQL :: Select Updated Rows From One Table
Jul 13, 2013There are two tables like I posted below.I want a SQL query which selects all the rows from TABLE A which are not present in TABLE B. Also the select statement should pick all those rows which has updated value of COL_A2 in TABLE A.
TABLE A
COL_A1COL_A2
AAAMOBILE
BBBTABLET
CCCDESKTOP
DDDUNKNOWN
TABLE B
COL_B1COL_B2
AAAMOBILE
BBBUNKNOWN
CCCDESKTOP
The select statement should return following rows from TABLE A
COL_A1COL_A2
BBBTABLET
DDDUNKNOWN
SQL & PL/SQL :: How To Retrieve Duplicate Rows From A Table
Jun 14, 2010How to retrieve duplicate rows from a table, suppose i don't know any column names. Without hard-coding column names I need to find.
View 5 Replies View RelatedSQL & PL/SQL :: Select Rows From Table With Same Particular Field?
Aug 20, 2012select the rows from a table with the same particular field in PL/SQL. Actually I don't want to write two loops one inserted into another.
View 7 Replies View RelatedSQL & PL/SQL :: Procedure To Display All The Rows From A Table?
Mar 25, 2013how to create a procedure to display all the rows in a table.
I wrote the below PL/SQL but im hitting as below.
DECLARE
howmany NUMBER;
some_full_name login_user.full_name%TYPE;
some_login_id login_user.login_id%TYPE;
some_users login_user%ROWTYPE;
[code]....
error :
Error report:
ORA-06550: line 3, column 19:
PLS-00225: subprogram or cursor 'LOGIN_USER' reference is out of scope
ORA-06550: line 3, column 19:
PL/SQL: Item ignored
ORA-06550: line 4, column 18:
[code]....
SQL & PL/SQL :: Count Distinct Rows In Table
Feb 21, 2013I think this is easier than I think but all I want to do is count the distinct rows in a table.
WITH data AS
(SELECT 1 id1, 1 id2, 111 val1
FROM dual
UNION ALL
SELECT 1, 2, 222
FROM dual
UNION ALL
[Code]..
seems to get what I want
ID1VAL1CNT1
111114
212224
321114
422224
but can the count be done in a stand alone query such as??
SELECT DISTINCT id1,
val1,
COUNT(*) over() cnt1,
COUNT(*) over(PARTITION BY id1) cnt2
FROM data
ORDER BY id1, val1
NOT what I want
ID1VAL1CNT1CNT2
1111152
2122252
3211153
4222253
SQL & PL/SQL :: Unable To Retrieve Rows From A Table
Mar 7, 2012I have created a table called ct_temp and it's structure is:
create table ct_temp(id char(2),srs number(3),amt number);
Result : Table Created
I now insert the following rows into the table:
id srs amt
1 62 30000
2 65 50000
3 65 70000
4 65 80000
5 62 16000
6 65 10000
7 65 100000
8 65 10
Commit
Then I issue the following query to retrieve data based on a specific criteria (Actually I have condensed the table and data because in Production, I have large number of rows; so for simplicity I am using 8 rows with one table)
criteria : I then want to retrieve the following:
for srs = 62, all values that is greater than 10,000
Answer: select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000
Works like a charm and retrives the results.
Now comes the big issue
I want to retrieve the values for srs = 62 which is greater than 10,000 and at the same time I also want to retrieve the values for srs = 65 which is less than srs = 62.
Typically I wrote the query as:
select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000
and decode(srs,65,ab.amt)<decode(srs,62,ab.amt)
I should atleast get one row for srs = 65 which is id # 8 but it displays blank rows or "no rows returned".
I am actually preparing a ad-hoc report for the business analyst and stuck at this step due to which I am unable to proceed any further. I have used DECODE function because of the requirement specified by Business Analyst.
I have tried the following in the office:
using EXISTS operator = no luck
using INLINE VIEW = no luck
Is there any way around?
SQL & PL/SQL :: Selected Number Of Rows From A Table
Aug 3, 2011Can we select table's rows of 5 to 15, like
select * from emp
where rowid <=5;
It is through an error when i used the below method.
select * from emp
where rowid >=5 and rowid <=15;
SQL & PL/SQL :: Rows In Table With Sequence Number
May 17, 2012I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50. The table may not have all the Sequence number. For example following is the data in my table..
Quote:NAME SEQUENCE
------------------------
Jonh| 5
Amy| 1
Suresh| 3
I need a SQL to get the data like
Quote:NAME SEQUENCE
------------------------
Amy| 1
| 2
Suresh| 3
| 4
Jonh | 5
|6
|7
..
..
..
|50
Basically I want to have rows for the sequence numbers which are not present i my table. I thought of using the following query to generate a sequence and then join, but does not work.
select rownum from dual connect by rownum <=50
I tried something like
with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num
SQL & PL/SQL :: Exclude Rows While Inserting Into A Table
Jul 12, 2011I have an insert statement like below.
insert into emp (select empno,ename,sal);
Here I need to exclude the rows having sal<0 from the SELECT query and insert those into some other table simultaneously.
SQL & PL/SQL :: How To Count Rows Of Partition Table
Apr 9, 2013I have tried to execute this query
select partition_name, num_rows from dba_tab_partitions where table_name='Partisi_Book_Issue';
it display 0 num_rows. But i have made non partitioned table to partitioned table with the same number of rows. how to display number of record in each partition in any table?
SQL & PL/SQL :: How Exactly Oracle Updates Rows Of A Table
May 17, 2011Check the below simple sql stmts:
create table tab_tab(col1 number, col2 number);
insert into tab_tab values(1, 1);
insert into tab_tab values(2, 2);
insert into tab_tab values(3, null);
[code]......
My question is why the table content didn't look like:
col1 col2
----------
1 2
2 2
3 2
4 3
5 4
I thought that when oracle will update 1st, 2nd & 3rd rows then the sub query will get a count equal to 2, but the time when it goes to update the 4th & 5th row the sub query should get back a count equal to 3 & 4 resp. BUT that is not what exactly is happening!!