Exact Space Occupied By A Table With LOB Column
Oct 18, 2013
I need to check the exact amount of space used (in bytes or MB) by a table which is having a BLOB column.I tried the following query but it is not giving the proper usage.
select segment_name , sum(bytes)from dba_extentswhere segment_type='TABLE'and segment_name in ('TEST_CLOB','TEST_BLOB','TEST_CLOB_ADV','TEST_BLOB_ADV') group by segment_name; I even tried the following stored procedure create or replace procedure sp_get_table_size (p_table_name varchar2)as l_segment_name varchar2(30); l_segment_size_blocks number; l_segment_size_bytes number; l_used_blocks number; l_used_bytes number; l_expired_blocks number; l_expired_bytes number; l_unexpired_blocks number; l_unexpired_bytes number; begin select
[code].......
But it is giving the error
Error starting at line 298 in command:exec sp_get_table_size ('TEST_CLOB_ADV')Error report:ORA-03213: Invalid Lob Segment Name for DBMS_SPACE packageORA-06512: at "SYS.DBMS_SPACE", line 210ORA-06512: at "SYS.SP_GET_TABLE_SIZE", line 20ORA-06512: at line 103213. 00000 - "Invalid Lob Segment Name for DBMS_SPACE package"*Cause: The Lob Segment specified in the DBMS_SPACE operation does not exist.
*Action: Fix the Segment Specification Although the LOB section is specified in create table syntax.
View 4 Replies
ADVERTISEMENT
Nov 3, 2010
I am trying to find the space occupied on disk by the tablespaces of the database that contain tables, some (and not all) of whose columns are encrypted. My query is like this:
select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB
from dba_data_files a, dba_tables b,
(select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c
where
a.tablespace_name = b.tablespace_name and
b.owner = c.owner and
b.table_name = c.table_name
order by a.tablespace_name;
The output of the query is as shown in the attached file:
TABLESPACE_NAMEFILE_NAMEFILE_SIZE_IN_GB
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0044.DBF29.296875
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0045.DBF29.296875
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0051.DBF29.296875
DMS_DATAN:ORACLEORADATASPOPRODDMS_DATA_0012.DBF19.53125
[code]...
Since the output (under the heading Total Size of the tablespace) is probably the sum of all the datafiles returned by the query and is obviously incorrect, I have not given the rest of it. I also tried the following:
select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB,
sum (bytes/(1024*1024*1024))over (partition by a.tablespace_name order by file_name) "Total Size of the tablespace"
from dba_data_files a, dba_tables b,
(select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c
where
a.tablespace_name = b.tablespace_name and
b.owner = c.owner and
b.table_name = c.table_name
order by a.tablespace_name ;
[code]...
Here, the fig. under the heading "Total Size of the tablespace" are probably the sum of all the records returned by the query if distinct is not used i.e all the data file sizes returned by the query.
tune my query and get the desired results? I think this can be achieved by group by with rollup, cube, order by and grouping functions, but am not sure how to proceed. I know that I can get the results by using Enterprise Mgr. Console in 2 mins., but would still like to get the results with the queries.
View 11 Replies
View Related
Aug 16, 2010
I want to writa a query to fetch the INCOME TAX RATE from the given table, my calculated gross salary amount is 594000
FromAMT ToAMT Rate
------- ------ ----
300001 350000 0.75
350001 400000 1.50
400001 450000 2.5
450001 550000 3.5
5500001 650000 4.50
and so on...
View 3 Replies
View Related
Oct 3, 2012
Just now sysaux resized to 600m from 250m >>
Sysaux Tablespace is running low. WE SET AWR RETENTION TIME=60 DAYS. WE ARE NOT INTEREST TO EXTEND SYSAUX TABLESPACE SIZE.
Usually we take AWR weekly once. Some times we did ADDM report and ASH.
CODEsql>select TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024), AUTOEXTENSIBLE, MAXBYTES/(1024*1024) from dba_data_files where tablespace_name = 'SYSAUX';
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024) AUT MAXBYTES/(1024*1024)
SYSAUX /u01/app/oracle/oradata/test/sysaux01.dbf 600 YES 32767.9844
CODEsql> @SCRIPT.SQ
TABLESPACE TOTAL_SPACE(MB) USED_SPACE(MB) FREE_SPACE(MB) % Used % Free
SYSAUX 600 248 352 41.33 58.67
1. What's the best SOLUTION ?
2. Can i shrink sysaux tablespace ?
3. I think , The size for all occupants in sysaux tablespace is less than 200 MB => how to find actual content of sysaux tablespace ?
4. What could be the reason for growth? Is there any way to free the space from sysaux table space?
View 9 Replies
View Related
Apr 9, 2008
How to validate a sum of some records in a table to be exact value?
I want to guarantee that sum(val_column)=100 at database level. Check constraints out of question. Trigger before update statement disable any update to that column when incrementing one row and decrement another... (unless with for all...)
View 3 Replies
View Related
Sep 1, 2010
There was one constraint define on the column of table that the value of column should be in range of 100 to 200. Some one has deleted the constraint. Is it possible to get the exact name of constraint from any system table.
View 7 Replies
View Related
Nov 24, 2012
I must admit my "google skills" have failed me and it is possible that I might be missing something obvious here but allow me to explain.
If I have a table that is populated/accessed by a typical OLTP application such that over time the data in the table may become "fragmented".
I know that is a very controversial term I used but that is not the question here. A simple way to describe the state of the table might be that the table has always been populated with lots of small INSERTs, modified with lots of small UPDATEs and data has been deleted in small DELETEs.
This has meant that the data is neither closely stored in data blocks nor is in any particular order (so there are some empty blocks under HWM).
Now my question is how do I create exact copy of this table and its indexes AS THEY EXIST AT THE MOMENT that includes
1) its data
2) its constraints/indexes etc.
3) its storage parameters
4) data stored in EXACTLY same manner as in original table.
To avoid complications, the table in question is just a normal heap table without any partitioning involved. While
CREATE TABLE AS SELECT (or CREATE TABLE followed by INSERT) will take care of points (1) to (3) above, it will not achieve point (4) above.
View 11 Replies
View Related
Feb 25, 2013
i have a ref cursor and i have used 'open cursor for' statement:
CREATE OR REPLACE PACKAGE aepuser.pkg_test
AS
TYPE cur1 IS REF CURSOR;
PROCEDURE get_empdetails (p_empno NUMBER, io_cur OUT cur1);
END;
[code]...
then i want to know that- will oracle automatically deallocate the memory occupied by records in cursor area?if yes, then when it will be free , in case of 'open cursor for' ?
View 7 Replies
View Related
Feb 28, 2010
I have one issue while loading the value through sql*loader the last column data is SG1 and when its loaded , it is length of this columns is showing 4 char. Unable to understand, how to find this extra space. Though used TRIM but does not work.
View 8 Replies
View Related
Jul 1, 2013
I want to replace double space with single space and also remove junk characters from the data. How can I do that?
CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
View 8 Replies
View Related
Jun 11, 2013
I am trying to Shrink Table space using the following SQl. As we are dropping large datasets. Later i am trying to shrink the table space
Alter Tablespace table_name Shrink Space Keep 20M;
Is this the best way to do in oracle 11G
View 1 Replies
View Related
May 4, 2011
I got the error ORA-01653: unable to extend table <OWNER.TABLE_NAME> by <BYTE> in tablespace <TABLESPACE> in my production database. But I could see 4GB of free space available in the tablespace. But this was resolved after increasing the Tablespace size by 1 more GB. So I wanted to know how I can I reclaim the 4GB space ?
While searching in internet I got few tips like there will be a fragmentation in the tablespace, the free space available in the tablespace may not be a continuous block. To avoid this we need to reorganize the tables using ALTER TABLE <TABLE_NAME> MOVE <TABLESPACE>; command.
But in my tablespace there are huge number of tables exists I cannot do reorganization of all the tables. So I need to know how to identify particularly what are the tables has more fragmentation? so that I can go for reorganizing those tables only.
My Database version in 9.2.0.7
Tablespaces are Locally Managed
View 1 Replies
View Related
Apr 17, 2012
I have a tablespace of size 512 GB. On the basis of tables and indexes created on it, the space consumed should be 319GB but when I am retrieving the free space size , I am getting only 124GB of free space. That means around 70GB of space is missing.
View 1 Replies
View Related
Sep 3, 2012
We are using a GTT table to store the summarize data and display it on same screen(10g 10.2.0.5). Now we are facing temporary tablespace space issue very frequently as our client do not enable the auto extend on for temp table space. We have analyzed the AWR and came to know that there are 900000 inserts per hour on an average. Client DBA Claims that there are sessions(1or 2) which inserts the data in temporary table continuously for 2-3 days.
According to him one session is running from 28th Aug and problem comes on 2nd Sep and after killing the problematic session the application will work fine. Generally this problems come on weekend. I have discussed with our dev team and as per them there is no session leakage issue.
following is the insert statement:
INSERT INTO DT_CA_STNDALN_DETAILS_TMP (ORG_ID,BA_PRODTYPE_ID,MAX
TENOR,GROSSLIMIT,GROSS_UTILISATION,HAS_MDR,HAS_CLUSTER,SIGN_IN
D,GROSS_AVAILABILITY,COLLATERAL,NET_UTILISATION,DT_CA_STNDALN_DE
TAILS_TMP_VER,DM_LSTUPDDT,NET_AVAILABILITY) VALUES (:1,:2,:3,:4,
:5,:6,:7,:8,:9,:10,:11,:12,sysdate,:13) ;
View 9 Replies
View Related
Jul 20, 2012
I need insert white space into table which a VARCHAR2 column. I would like to validate the below expression against the data in the column S.*
I guess this pattern validates for non-whitespace characters. explain what this pattern does?
View 1 Replies
View Related
Jun 11, 2009
I want to alter a very large table.
ALTER TABLE MYTABLE ADD
(
ENTRY_TSTMP DATE DEFAULT SYSDATE NOT NULL
)
My table is very large and I am getting an error saying I am out of undo space.
The dba says the undo space is as big as the table.
View 1 Replies
View Related
Oct 15, 2011
a newbie dba here..
select TABLESPACE_NAME,
sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent
from dba_free_space
group by TABLESPACE_NAME
/
result output is attached .txt file.
the SYSTEM & USERS table space shows only <10 mb free space. Is it a bad sign? What I should do ?
View 7 Replies
View Related
Oct 18, 2012
alter index test_idx1 shrink space;
I've heard that this statement causes a table lock but cant find any information on this.if it is so, is it a write lock or also a read lock of the table?
View 5 Replies
View Related
Feb 14, 2013
I am using Oracle 10.2.0.3. Since yesterday i am seeing a session with sid 1160 using undo tablespace but not able to find how much it is using .I need to know which session and from which module and how much is the Undo being used by those sessions. I have tried searching but all the queries provide me with some different results each time.
Also i need the same information for REDO being generated .
View 20 Replies
View Related
Dec 21, 2010
I want to create system table space's extent management dictionary with the syntax:
CODEcreate database
logfile
group 1 ('/u01/app/oradata/anand/redo1a.log') size 100M,
group 2 ('/u01/app/oradata/anand/redo2a.log') size 100M,
group 3 ('/u01/app/oradata/anand/redo3a.log') size 100M
datafile '/u01/app/oradata/anand/system.dbf' size 400M extent management dictionary
sysaux datafile '/u01/app/oradata/anand/sysaux.dbf' size 300M
default temporary tablespace temp tempfile '/u01/app/oradata/anand/temp.dbf' size 50M
but it is giving error
ERROR at line 6:
ORA-25141: invalid EXTENT MANAGEMENT clause
how can I make system tablespace's extent management dictionary?
View 3 Replies
View Related
Sep 16, 2012
while trying to refresh an materialized view.. oracle throws cannot extend temp table space error.. while starting to refresh mivew temp table space is empty but once refresh started temp tablespace is growing and throws cannot extend temp tablespace error,,,size of temp tablesapce is 200GB..when i monter the session it does an sort event of an table(ammt_pol_ag_comm).. only 4% of this sort event is completing after that it throws error bu occupying the entire 200 GB tabespace.. MView script below..
CREATE materialized VIEW ammv_agent_pol_persis_emas
NoLogging
Parallel 10
Build Immediate
Refresh on demand
With Primary Key
AS
[code]...
View 13 Replies
View Related
Nov 21, 2011
I have table which has 240 columns.
Here is the list of column data type.
VARCHAR2(50)
TIMESTAMP(6)
VARCHAR2(25)
VARCHAR2(25)
NUMBER(15,2)
VARCHAR2(50)
NUMBER(1)
[code]....
The table has 64 partition.. When i count the table(select count(*) from table), i see close to 22 million records.
SQL> select (num_rows*avg_row_len)/1024/1024/1024 GB,num_rows,avg_row_len
2 from dba_tables
3 where table_name='TRX_TAB';
GB NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------
74.9393936 21871585 3679
SQL>
It is supposed to take 75 GB. But this table is consuming 135 GB now. It is occupying 8 GB per day.
View 4 Replies
View Related
Jun 9, 2011
How to check for the increment of a space of the tablespace based on the particular table. (i.e.) Say a scenario, if am trying to load the data for a particular table, for first I loaded some 10000 records and then again loading 50000 records ,so based on the icrement of the reocrds the tablespace size also increases gradually . so for this scenario how to monitor the increment of the space.
View 13 Replies
View Related
Jun 27, 2013
It is not possible to run SHRINK SPACE against a table with a function based index. This is documented, and I've tested on the current release. I've reverse engineered it a bit, and the issue is in fact that you cannot SHRINK SPACE if there is an index on a virtual column:SQL> SQL> create table t1(c1 number, c2 as (c1*2)) segment creation immediate;
Table created.
SQL> alter table t1 enable row movement;
Table altered.
SQL> alter table t1 shrink space;
Table altered.
SQL> create index i1 on t1(c2);
Index created.
SQL> alter table t1 shrink space;
alter table t1 shrink space
ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object.
View 2 Replies
View Related
Oct 21, 2011
How to calculate exact age for example my date of birth is 10-04-1972 and today current date is 21-10-2011 so i want to calculate age how many years, how many months and how many days.
View 27 Replies
View Related
Nov 23, 2011
I created a table of Number(20,4) column. I inserted an amount value 999999999999999.5555 but this value is rounded off to 1000000000000000.0000 automatically in Oracle. How to avoid this? I tried for less number of digits and I am getting the exact value. Is there any way to get the exact value without changing the datatype?
View 1 Replies
View Related
Dec 11, 2012
When I pass the input as 'micky', then i should get the count of records as 4. I tried to use REGEXP_LIKE, but could not get the result.
Test Case:
DROP TABLE test1;
CREATE TABLE test1( pattern_series VARCHAR2(30));
INSERT INTO test1 VALUES ('qa_micky1');
INSERT INTO test1 VALUES ('qa_micky2');
[code].....
-- I should get the output as 4 not 8
View 5 Replies
View Related
Jan 7, 2013
How can I find out that exact count of '~'?
SELECT NVL(LENGTH('~~~~~~~~~~~~~~~~~')-LENGTH(REPLACE('~~~~~~~~~~~~~~~~~','~','')),0) result
FROM dual;
View 11 Replies
View Related
Mar 18, 2013
I am using Release 10.2.0.1.0 version of oracle. I am getting a special character, but i suspect it as any other language character.
Its appearing in my 'TOAD editor/Sql prompt editor' as 'A?'. but when i am selecting it from the table using below query, its giving 'No rows Returned'.
select id from tab1 where id like 'A%';
How can i be able to see the exact character or which editor will enable me to see the character?
I think sql developer GUI might be able to show the same but i dont have sql developer with me.
View 1 Replies
View Related
Jul 16, 2010
1)How can i know that in a table when i modified a row or deleted a row and which row i inserted when i want to know the particular time
can it is possiable if possiable then tell me how.
2)Is there any difference between 9i merge and 10g merge command ?
View 4 Replies
View Related
