SQL & PL/SQL :: How To Find Size Of View Or Table In Database

Jul 10, 2010

How can we find the size of a view or synonym or table in a database. What is the code.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: View Sample Data From Very Table Which Is Large In Size?

Apr 26, 2010

I have a query on , how to view the sample data from a very table which is large in size ( more than 10 million ).

I just need to see some sample data from a large table ( to see what kind of data which is application related ).

My question is :

Select *
from Sample_table
where rownum < 10

is this a Good way to view the sample data ?

I have understanidng that the rownum will be assigined to the rows once all the rows are reteived.

So what is the best way to view ?..I am not sure of any condition to put in the intial time of querying.

View 5 Replies View Related

Find Tablespace Size (used And Free) For Partitioned Table?

Aug 9, 2013

I need to find the tablespace size (used and free) for a partitioned table

View 3 Replies View Related

XML DB :: Find Size Of Table With XML Type Column Store As Binary XML

Dec 21, 2012

I have a table with structure as:

CREATE TABLE XML_TABLE_1
(
ID NUMBER NOT NULL,
SOURCE VARCHAR2(255 CHAR) NOT NULL,
XML_TEXT SYS.XMLTYPE,
CREATION_DATE TIMESTAMP(6) NOT NULL
[code].....

- So HOW do I find the total size occupied by this table. Does BINARY storage work as LOB storage. i.e. I need to consider USER_LOBS as well for this. OR foll. will work

select segment_name as tablename, sum(bytes/ (1024 * 1024 * 1024 )) as tablesize_in_GB
From dba_segments
where segment_name = 'XML_TABLE_1'
and OWNER = 'SCHEMANAME'
group by segment_name ;

- Also if I am copying it to another table of same structure as:

Insert /*+ append */ into XML_TABLE_2 Select * from XML_TABLE_1.

Then how much space in ROllbackSegment do I need. Is it equal to the size of the table XML_TABLE_1?

View 2 Replies View Related

Enterprise Manager :: Querying Grid Control Tables To Find Size And Name Of Database?

Sep 25, 2012

query to get the name and size of all the databases associated to grid..

I need to find this to do capacity planning in our environment.

View 6 Replies View Related

Server Administration :: How To Find The Base Table Of A View

Aug 15, 2011

How to find the base table of a view,such as a view:

create or replace view vw_test as select * from tb_test;

Is there dic view to get the base table of view?

View 3 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

SQL & PL/SQL :: Regular Table Or Materialized View - Clone Table Data In Another Database

Jul 19, 2010

There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.

Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.

View 19 Replies View Related

SQL & PL/SQL :: How To Find OS Block Size

Oct 25, 2002

I find posted and written in many places that the DB block size should be a multiple of the OS block size. I can't find any information, however, on how to find what the OS block size is for an OS. How to find the OS block size for Windows and UNIX systems (Solaris, Linux, and HP-UX)?

View 4 Replies View Related

SQL & PL/SQL :: Find Out Column Value In All Table In Whole Database

May 19, 2010

I need the column name and table name which is having the particular value in the whole database.

E.g. :We have 'Scott' value in emp table emp_name column. and we have lot of tables. here we need emp_name and emp as emp table is having scott value in emP_name column.

View 14 Replies View Related

Table / View V$database Not Found In PL/SQL Function

Feb 18, 2011

I'm trying to create a function that simply returns the current database name (e.g: select db_unique_name FROM v$database ) from a function but when I compile it comes up with :

Error(9,5): PL/SQL: SQL Statement ignored
Error(9,44): PL/SQL: ORA-00942: table or view does not exist

I am compiling and running this as the SYSTEM user and I do think that I need to set privledges/roles, etc to allow this (since I have read that using synonyms in functions/procedures requires permissions...but I cannot seem to find anything that tells me exactly what role/priveledge I need to grant/allow to let this happen.

View 5 Replies View Related

Server Administration :: How To Find The Size Of Individual Folder In ASM 10g

Mar 16, 2012

Would like to know, how to find the size of individual folder in ASM 10g !

Also I am not able to find the PATH/name of the raw devices in ASM 10g !

View 3 Replies View Related

How To Check / Find Size Of Current ROLLBACK Segment

Apr 8, 2013

How to check/find the size of current ROLLBACK segment in oracle 10g ?

View 20 Replies View Related

Security :: Find Out Locked Table And Session From Database

May 20, 2013

I used to find out locked table and session from database....query with SYS user but i want to hand over the user session kill role to location level IT person so i have created one user in database named as rab and i have given "select any table,alter session and Grant dba to that rab user,but with that user they will drop and delete any table also

col owner format a12
col object_name format a25
col ORACLE_USERNAME format a15
col OS_USER_NAME format a15
col OBJECT_TYPE format a15
set lines 140
[code]....

View 3 Replies View Related

View To Find SQL Plan Changes

Jul 4, 2012

The view that can be used to find the changes the plans (hash_value,plan_hash_value for a particular sql statement).

I have a particular sql statement for which the execution plan changes but, unfortunately i cannot find regarding which view can be used to find the details regarding this. V$SQL_PLAN_MONITOR is not working as well.    

View 4 Replies View Related

SQL & PL/SQL :: How To Find Number Of Rows Of View

Oct 22, 2010

is there a way to find out how many rows a View has? Something similar to NUM_ROWS with regular tables perhaps? Or do I have to use Count(*)?

View 4 Replies View Related

SQL & PL/SQL :: View To Find DB Link Type

Feb 21, 2012

Is there any view to find out the Types (Public or Private) of the DB LINK already created?

View 6 Replies View Related

SQL & PL/SQL :: Find And Trace Custom View / Procedure?

May 24, 2013

I know that this query had been executed again the db, but the person is no longer here. Last run was about 3~4 months ago.

SELECT Subject_ID,
TO_CHAR (completed_date, 'mm/dd/yyyy'), status
FROM ADMINDBG_USER.adbt_master
...

[ADMINDBG_USER.adbt_master] is not a part of [dba_tables]. [ADMINDBG_USER.adbt_master] must be a view/procedure/a sort.
I have no luck to find what view/procedure/syntax is populating the data onto [ADMINDBG_USER.adbt_master].

View 7 Replies View Related

Security :: How To Find Out Date View Status Changed To Invalid

Mar 10, 2011

We are trying to audit a view. We are currently seeing that view in production instance and trying to find out when exactly it got invalidated.

View 4 Replies View Related

PL/SQL :: Oracle Dictionary View 2 / Find Queries Run And Its Execution Time

Feb 6, 2013

Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.

View 2 Replies View Related

SQL & PL/SQL :: Materialized View - Table Or View Does Not Exist

May 2, 2012

I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.

EXEC DBMS_MVIEW.REFRESH('pro_mview','C');

But I am getting the below error.

*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1

I am able to fetch the data from that materialized view(pro_mview).

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

Size Of Database

Aug 24, 2009

i have few questions to ask.

1)How to find out the total size of my database.

2)what is dblink and how to find out the same.

View 5 Replies View Related

Database Size Required For SOA?

Jun 19, 2012

I will be installing SOA Suite with OSB in Exalogic for testing. It is not for the production environment and very simple application will be used for testing. Database admin asked me what size database would be required but I did not find clear answer in the documentation anywhere.

View 0 Replies View Related

Reduce The Size Of Database

May 9, 2013

We have Oracle 10G database over Unix platform, Customer want to reduce the size of database as much as possible and the ami of customer to move the storage area of this database to other one. so we resize some datafiles and get lots of free space at mount point but while checking the utilzation of table is showing some what different as other. Below O/P:

                                                                                                                       %
                                                                                    %                         MaxPoss    Max
Tablespace Name                         KBytes            Used            Free   Used         Largest          Kbytes   Used
------------------------------ --------------- --------------- --------------- ------ --------------- --------------- ------
*a DATA                             45,875,200       8,740,992      37,134,208   19.1       1,728,512     100,663,248   45.6
*a HIGH_S_DATA                      21,504,000       1,331,520      20,172,480    6.2       3,048,704               0     .0
*a HIGH_S_IND                       15,360,000         853,568      14,506,432    5.6       1,661,504               0     .0

[Code]....

above all o/p is different, no able to understand it. is there any way to reset the HWM at Datafile level and how we reset the HWM of those tables having Materlized view?

View 18 Replies View Related

Database Block And Cache Size

Oct 5, 2010

In Oracle 9i

I know that db_block_size defines the standard/default block size for my datafiles.

I know db_cache_size defines the size of the my default database buffer of the size of my standard/default block size.

db_block_size = 8192 (8k)
db_cache_size = 200Mo of 8k block

Is there any needs for the other buffer cache

db_16k_cache_size
db_4k_cache_size
db_32k_cache_size

and so on?

If I have NO datafiles other than of the default block size, would I need to define a size for those other buffer pool? Is there any process that would benifit of these pools?

View 3 Replies View Related

Security :: Database Field Size

Nov 28, 2012

We have Employee table, there is a field name Employee_no. field size is 6.can we restrict some one not to increase or decrease the field size of Employee_no. even User has admin role.

is there any way to restrict admin user that he should not allow to enter the value of field more or less than 6 characters through Toad or SQL Plus 8.0.
------------------------------------------------------
for example

field: employee_no
feild_size: 6
field_type: Varchar2

When we enter Employee no A000001(7 digit) then database not allowed to update because its field size is 6 characters we want to restrict admin user in Toad or SQL Plus 8.0 because he is Toad and SQL Plus user.

View 6 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

Server Administration :: Size Of The Oracle Database 11g?

Apr 21, 2010

how can we find the size of the oracle database 11g.

View 2 Replies View Related

Server Administration :: Increase Size Of Database To 50 GB

Jan 31, 2013

I have Oracle 11gR2 running on windows xp machine. Windows xp has total size of 150 GB and free space of 95 GB.

I checked the size of the database that I created. It showed the total size of the database as 2 GB and used space as 2 GB. If I want to increase the total size of the database to 50 GB, what should i do? Now which is the disk space size? Windows or Oracle?

View 4 Replies View Related







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