SQL & PL/SQL :: Determine Table Size Order By

Mar 28, 2012

How to find the tables starting with smallest size and vice versa in schema level and database level?

View 4 Replies


ADVERTISEMENT

Performance Tuning :: Correct Method To Determine Table Actual Size

Aug 9, 2012

Which is the correct method to calculate actual data size in a table? becaue when I serach in google, I saw the below line.

"Oracle thumb rule says (actual space required for a table + 30 % space) will calculate the original space requirement for a table."

Method 1:

actual space = num_rows*avg_row_len

Method 2:

actual space = (Num of rows in a table) * (Avg_row_len) + ((Num of rows in a table) * (Avg_row_len)* 0.3)

View 8 Replies View Related

11.2.0.3 DB - How To Determine Fetch Size Of Application

Feb 18, 2013

The db is 11.2.0.3 on a linux machine.I would like to know the "fetch size" of an application, but I was not able to find any related meteris in v$statname.

The application configruation is invisible to me.Do I need to do some calculations based on statistic metrics from v$statname?

If so, what meteris should be considered for the assumption for "fetch size" ?

The following is from manual, but the application configuration is invisible to me.

[URL]

Setting the Fetch Size

The following methods are available in all Statement, PreparedStatement, CallableStatement, and ResultSet objects for setting and getting the fetch size:

•void setFetchSize(int rows) throws SQLException

•int getFetchSize() throws SQLException

View 4 Replies View Related

How Oracle Determine Initial Extent And Max Extent Size

Aug 10, 2012

Suppose tablespace allocation_type is system then how oracle determines the initial extent and max extent size?

View 4 Replies View Related

SQL & PL/SQL :: Result Order In 2 Table Joins Without Order By

Sep 6, 2011

I have below tables,

describe rpthead

Name Null Type
--------------------------- -------- -------------
RPTNO NOT NULL NUMBER
RPTDATE NOT NULL DATE
RPTD_BY NOT NULL VARCHAR2(25)
PRODUCT_ID NOT NULL NUMBER

describe rptbody

Name Null Type
------------- -------- -------------
RPTNO NOT NULL NUMBER
LINENO NOT NULL NUMBER
COMMENTS VARCHAR2(240)
UPD_DATE DATE

The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.

SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0 , RPTHEAD rpthead
WHERE
(
t0.RPTNO = rpthead.RPTNO
AND
t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss')
AND
rpthead.PRODUCT_ID IN ('4647')
)

I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.

View 5 Replies View Related

Server Utilities :: Estimate Size Of FlatFile Based On Table Size?

May 8, 2013

We are planning to export the table data to a file pipedelimited. How do i estimate the size of the FlatFile based on the table size? or avg rowlength

View 3 Replies View Related

Server Administration :: Command To Determine Which Table Can Benefit From Shrinking

Apr 14, 2010

I use following command to determine which table can benefit from shrinking

select * from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'))
order by reclaimable_space desc

then i give following command to get reclaimable space

alter table t1 enable row movement ;
alter table t1 shrink space cascade;
alter table t1 disable row movement ;

in table t1 427MB was shown as reclaimable space, after executing above commands, i run dbms_space procedure again to check the out come, but result was same.I understand tablespaces are by default ASSM in 11g, none of table has LONG datatype or LOB indexes or MVIEW with ON COMMIT.

View 2 Replies View Related

PL/SQL :: Joining Order Table With Asset Or CX_ACTION Table?

Apr 18, 2013

joining the Order table with the Asset table or the Order table with CX_ACTION table to Can i get reference on this .

select x.x_msisdn,
x.x_product_name,
x.x_action,
x.x_status_outcome,
a.recovered_dt,
x.x_amount,

[code].....

View 1 Replies View Related

Performance Tuning :: Order Of Steps In Execution Path And Order Of Predicates In The Plan

Mar 20, 2012

Which step in the following plan is the first step of execution

I reckon it is "TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE"

Is that correct?

In the "Predicate Information (identified by operation id):"

section the predicates - access and filter for the step "TABLE ACCESS FULL | PYMNT_DUES" are displayed first

Isn't there any relation between the order of execution steps and the order in which predicates are displayed?

Execution Plan
----------------------------------------------------------
Plan hash value: 538700484
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2364 | 15 (14)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 2364 | 15 (14)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 2364 | 14 (8)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 2313 | 13 (8)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 2281 | 12 (9)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 2255 | 11 (10)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 175 | 6 (17)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INDX_2 | 12 | 612 | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PYMNT_DUES | 43 | 5332 | 3 (0)| 00:00:01 |
| 10 | VIEW PUSHED PREDICATE | | 1 | 2080 | 5 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 154 | 5 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 103 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE | 1 | 32 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | INDX_BBS_1 | 3 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| DAILY_CHECK | 1 | 71 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | INDX_SEARCH | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | INDX_IAM_SR_NO | 1 | 26 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INDX_2 | 1 | 32 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------

View 3 Replies View Related

Server Administration :: Move Partitioned Table Between Table Spaces Of Different Block Size?

Apr 4, 2011

I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.

So far the only option I have is to export and then import back the data.

know if there is any way to move a partitioned table between table spaces of different block size?

View 14 Replies View Related

Oracle Table Sort Order

Jul 16, 2010

I know the only way to guarantee a specific sort order result when querying table is by using the order by clause. However, I have an issue where I do not have access to the code for the web user interface of a very lightly used interface (has two users). In that user interface is a drop down box that is populated by a table - one table. The drop down box is populated by the query "select [column name] from [table name]". Right now there are 400+ rows in that table total, so it's small but not having the items ordered is a pain.

I would like to alter something on the db side so the result of "select [column name] from [table name]" is an ordering by the column descending. I don't want the sort order to be the same for all queries (including joins and all) just want to control the order for that one query. It is Oracle9i.

View 1 Replies View Related

SQL & PL/SQL :: Select With Order In Self-reference Table?

Jun 25, 2012

I have the following table:

CREATE TABLE test_A(member_id NUMBER(2) PRIMARY KEY, MEMBER_name VARCHAR2(20), MEMBER_parent NUMBER(2) DEFAULT NULL);
INSERT ALL
INTO test_A VALUES (1, 'mem1', NULL)
INTO test_A VALUES (2, 'mem2', NULL)
INTO test_A VALUES (3, 'mem3', NULL)
INTO test_A VALUES (4, 'mem4', 1)
INTO test_A VALUES (5, 'mem5', 1)

[code]....

As the actual data is huge, I need to know the best (least expensive) way to select each parent and then all its direct child ordered by member_id the output should look like:

1mem1(null)
4mem41
5mem51
6mem61
2mem2(null)
7mem72
8mem82
10mem102
3mem3(null)
9mem93
11mem113

View 3 Replies View Related

How To Reorganize A Table In-order Of Primary Key

Jul 13, 2010

I would like to reorganize a table inorder of primary key but I'm not sure if I'm expecting the right thing from dbms_redefinition package.

I am working on oracle 9i 9.2.0.8

I have the following table :

SELECT * from SCOTT.TESTTABLE
ID REF
---------- ----------
1 FF
2 BB
3 CC
4 DD
8 EE
6 ZZ
7 YY
5 GG

when I use the CODEexec dbms_redefinition.start_redef_table('SCOTT', 'TESTTABLE', 'TESTTABLE2', 'id id, ref ref', dbms_redefinition.cons_use_pk);

Would the newly created scott.testtable be created in order of primary key (ID) thus a select * from scott.testtable will give me an ordered result?

When I do the test, the table before and after the redefinition is exactly the same so why use the CONS_USE_PK if it doesn't order the table by primary key?

View 5 Replies View Related

SQL & PL/SQL :: Write A Query In Order To Receive A New Table?

Apr 23, 2012

i need to write a query in order to receive a new table.

select table_name,col1, null col2
from
(SELECT b.table_name, b.col1 col1, a.CountPrimaryKeys
from
(SELECT user_constraints.table_name,
COUNT(user_cons_columns.column_name) CountPrimaryKeys

[code].......

View 21 Replies View Related

Create A Separate Sort Order Column On Table

Oct 31, 2006

I have data that i am sorting, the data is mostly numeric (format of XXX-1234). is there any way to have my query sort xxx-1000 AFTER xxx-999? right now i am thinking i will have to create a separate sort order column on my table.

View 2 Replies View Related

SQL & PL/SQL :: Finding Next Record In Cyclic Order In Table Using Sequence

Sep 4, 2010

I have a table as below:-

CREATE TABLE TEST_EMP_WIP
(
EMP VARCHAR2(20 BYTE),
WIP NUMBER,
MAX_SEQUENCE NUMBER
)

[Code]....

EMP WIP MAX_SEQUENCE RANK
002 2 2 1
003 1 4 2
005 3 5 3
010 1 4
011 1 5

I have to find the next EMP whose MAX_SQUENCE needs to be updated with a sequence. In this case the next would be '010' I fetched the EMP who falls after the max value of the column max_sequence in order of EMPCODE.

select emp_next from (
SELECT emp,max_sequence, LEAD(emp, 1, 0) OVER (ORDER BY emp) AS emp_next
from test_emp_wip where wip > 0 )
where max_sequence=(select max(max_sequence) from test_emp_wip where wip > 0)
EMP_NEXT
010
After sometime, the record would look like :-

EMP WIP MAX_SEQUENCE RANK
002 2 2 1
003 1 4 2
005 3 5 3
010 1 6 4
011 1 7 5

Now the next update should be done to the EMP '002' in cyclic manner.

tell me a way to do this. I can insert new columns if needed in table for calculation.

View 5 Replies View Related

Row Size Of Two Different Row In Table?

Sep 13, 2011

One of my user is asking to give me the size(bytes) of row 39 and size(bytes) of row 49 of a table ZETR.

as i am not aware of how to collect size(bytes) for a particular row.

View 3 Replies View Related

Trigger That Will On Updating Table Order And Changing Status To GoodsReceived

Nov 30, 2009

my simplified database schema is:

Order
OrderID
Status
OrderItems
OrderID
EAN
Amount
Store
EAN
Amount

now,I need trigger that will on updating table Order and changing status to "GoodsReceived" increase amounts in Store according to values in OrderItems.

create or replace trigger order_received_trigger
before update
on Order
for each row
begin
if (:old.status=4 and :new.status=1) then
/*
select ean, sum(amount)
from OrderItems
where OrderID=:old.OrderID
group by ean;
*/
end if;
end;

but now i dont know how to apply that select on table Store.

View 2 Replies View Related

SQL & PL/SQL :: How To Know The Block Size Of A Table

Jul 12, 2011

AS of we know that we can get the number of blocks occupied by data in a table by querying the user_tables Data Dictionary.

My questions are,

1.)how to know if i want the each block size ?(i.e each block size either it is 4kb or 8kb or 16kb..)?

2.) Is the block size is same for all blocks in a table and in Database or it varies?

3.)The block size is DB dependent or Table dependent or Machine dependent(32-bit, 64-bit and OS)?

View 5 Replies View Related

How To Increase Table Size

Oct 14, 2012

I have table and it's size is full when i'm inserting records , records are not inserting , How can i increase table size

View 8 Replies View Related

How To Limit The Table Size

Oct 9, 2012

I have a problem with one table.. First of all:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

The problem table resides in a locally managed tablespace. About 10 millions records is added in this table every day. After 36 hours all these records moved to another (partitioned) table, so the size of data in the problem table always about 75 Gb. But the size of table is reached 157 Gb today, and it still growing. The results of dbms_space.space_usage are showed below:

Size of blocks with:

0-25% free space: 4726784
25-50% free space: 17301504
50-75% free space: 24920064
75-100% free space: 102418669568
full blocks: 54761594880Thus, a lot of blocks have 75-100% free space but the table constantly growing: during last 9 days the size increased from 123 to 157 Gb.

how to stop the table growing? It there any way to limit the table size in locally managed tablespace?

View 20 Replies View Related

Server Administration :: How To Reduce Size Of TEMP DBF File Size

Apr 13, 2011

I am using oracle 8.1.5 database and my temp01.dbf file size is increased upto 19.8 GB now i want reduce its size .

View 13 Replies View Related

PL/SQL :: Table Data Size In Oracle 11g?

Jul 12, 2013

how to find the data size in particular Table?Ex:I need find out the sh.sales table how much data size is loaded

View 3 Replies View Related

Partitioned Table - What Extent Size To Be Set

Sep 28, 2012

I have a partitioned table (one partition per month). Every month there are added about 1GB data. What extent size should I set? 1GB will be ok?

What if data will be greater than 1GB, adding new 1GB extent takes probably a lot of time and clients may see delays while they're inserting in this time? (it's OLTP system)

When new extent is allocated? Exact in time of lacking space in existing extent or before? Partitions are dropped after one year so free space isn't a problem.

View 6 Replies View Related

PL/SQL :: Determine Next Due Date

Sep 12, 2012

I have a table that holds the definition of schedules, a schedule defines when a document should be submitted to a specific party. The schedule definition contains a start date, and end date, a recurrence type (is this submitted one time, or on a recurring schedule) and the frequency at which the document should be submitted. The second table provides a history of the submissions, this stores when it was due and when it was received. At the beginning of the month we pre-populate the submittal table with a list of records that will be due for the month. For instance, on September 1 we look through all of the schedules and determine which ones would have a record due at some point in 9/12 and then create a record in the submittal table.

I am having issues getting the calculated list of submittal records to work properly.

The DDL and DML will be in a follow-up post

Here is the query that I am currently using and fails to work properly.

     with schedules as (
       -- generate a list of valid permit schedules
       select s.schedule_id,s.submittal_frequency_months,s.recurrence_type,
       s.first_due_date,s.requires_approval,
       round(round(months_between(to_date('09/01/2012','mm/dd/yyyy'),s.first_due_date))/decode(s.submittal_frequency_months,0,1,s.submittal_frequency_months)) recurrence_number
 
[Code]....

     -- create a list of all potential due dates for these schedules

     select submittal_id_seq.nextval,schedule_Id,8,requires_approval,
     case
      when recurrence_type='One Time'
        then first_due_date
      when recurrence_type='Recurring' and trunc(first_due_date)=to_date('09/01/2012','mm/dd/yyyy')

[Code]....

      -- exclude those that already have a submittal record ;

Basically I found all possible records in the schedule table that could have a record due in September, then generate a result for all possible instances and then look at only those whose calculated due date is 09/01/2012. I've determined that the root problem I have right now is this line:

(select level iteration from dual connect by level <= (select max(schedules.recurrence_number) from schedules)) d

Schedule ID 469907 has a start date of 05/15/1992 and a frequency of every 2 months. I calculate what I call the recurrence number, which is the number of times the schedule has happened since its start date to now. I use that to do an add_months calculation from the start date and then eventually compare these calculted start dates with my target month (09/12). In this one records case the calculated recurrence number is 122. So when I generate the connect by level is does 122 records for every schedule, so I end up with duplicate records in the submittal table for many of the schedules. This current query could probably work if I could figure out a way to make the level be schedule ID specific, but I've failed at that thus far.

Aside from the fact that this is returning the wrong results, I am thinking there must be a better more efficient method to determine which records are due for a given month. I was thinking there is probably some cool way to use the model clause here, but I haven't got a grasp on that one yet.

If you run the following insert statement you'll see that it inserts over 2400 records:

insert into submittal (submittal_id,schedule_id,submittal_status_type_id,requires_approval,due_date,created_by,created_date,modified_by,modified_Date)
     with schedules as (
       -- generate a list of valid permit schedules
       select s.schedule_id,s.submittal_frequency_months,s.recurrence_type,
       s.first_due_date,s.requires_approval,
  
[Code]....

-- only submittals whose last due date has not passed, null last date included

       and trunc(s.first_due_date,'mm') <= to_date('09/01/2012','mm/dd/yyyy') -- only valid start dates
       --and round(round(months_between(to_date('09/01/2012','mm/dd/yyyy'),s.first_due_date))/decode(s.submittal_frequency_months,0,1,s.submittal_frequency_months)) >0
       )

     -- create a list of all potential due dates for these schedules

     select submittal_id_seq.nextval,schedule_Id,8,requires_approval,
     case
      when recurrence_type='One Time'
        then first_due_date
      when recurrence_type='Recurring' and trunc(first_due_date)=to_date('09/01/2012','mm/dd/yyyy')
  
[Code]...

You can see the problem after words:

select schedule_id,count(0)
from submittal
where trunc(due_date,'mm')=to_date('09/01/2012','mm/dd/yyyy')
and submittal_status_type_id=8
having count(0) >1
group by schedule_id;Tony

View 14 Replies View Related

Calculate Size Of Table With BLOB Column

Feb 5, 2007

when trying to calculate the occupied space for a table, I'm using DBA_SEGMENTS, which works fine as long as the table does not have a BLOB column.

As far as I can tell, the size of the BLOB data is stored with the SEGMENT_TYPE = 'LOBSEGMENT', but I cannot find a view that tells me which DBA_SEGMENT row belongs to the BLOB column in the table I'm checking.

To give you an example:

select sum(BYTES)
from DBA_SEGMENTS
where owner = user
and segment_name = 'MY_TABLE'
group by SEGMENT_NAME

returns 262144

running:

SELECT sum(length(blob_column))
FROM my_table

returns 821333

There are entries in DBA_SEGMENTS for my user with the type LOBSEGMENT, but I cannot find a way to map the correct DBA_SEGMENTS row to the table I am checking.

I'm using Oracle 10.2.0.3.0 on Redhat

View 2 Replies View Related

Estimate Next Extent Size For Very Large Table?

May 13, 2011

How to estimate next extent size for very large table? What should I take into account? Is there any formula for that?

View 4 Replies View Related

How To Reduce Size When Table Is Null - Sqlplus

May 17, 2012

how can I reduce the size of ------------- when table is null. I m in sqlplus I typeSelect A,B,C,D,F,G,H from SOMEHERE where B='GOAT1';

if A is 10 char long
B is 50
c is 10
d is 30
e is 10
f is 50

if any of those don't have data it still outputs ----------------------------- (50) for B and tht covers the whole screenhow can I make is to show less if it null

View 2 Replies View Related

Infrastructure DBA To Move Table With Size 126GB?

Jul 16, 2010

I have requested the Infrastructure DBA to move a table with size 126GB(as shown in the stats/size tab in TOAD) from one tablespace to another.This is free the huge space in the first tablespace which i wanted to use for creating another table.

But when the table is moved to another tablespace, surprisingly for me, i saw that the sizeof the table has come down to 8 GB from 126 GB.Point to be noted is that everyday there are physical deletes happening on the table.

View 6 Replies View Related

Server Administration :: How To Shrink Table Size

Jan 4, 2012

I have a table: desc STG_XML

Name Null Type
------------------------------ -------- ------------------------
ENTITY_ID NOT NULL VARCHAR2(100 CHAR)
ENTITY_TYPE_ID NOT NULL NUMBER
SOURCE_ID NOT NULL VARCHAR2(512 CHAR)
XML_SCHEMA_ID NOT NULL NUMBER
JOB_ID NOT NULL NUMBER
FINGERPRINT NOT NULL VARCHAR2(100 CHAR)
ENTITY_XML_DATA CLOB()
ARCHIVED NUMBER(1)
CREATION_DATE TIMESTAMP(6)
MODIFICATION_DATE TIMESTAMP(6)
ARCHIVING_DATE TIMESTAMP(6)
CREATED_BY VARCHAR2(50 CHAR)
MODIFIED_BY VARCHAR2(50 CHAR)

The problem is that the data of the table are 40GB while on the DB the table holds 400GB! How can I shrink and reuse that space except from drop/recreate and drop/import?

The table has no initial data, so that I can play with the INITIAL parameter. Data are inserted, updated and deleted all the time. I have run DBMS_ADVISOR which recommended to SHRINK table. I have performed the shrink :

alter table STG_XML shrink space COMPACT;

but I haven't gained any space.

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved