Partition Drop Not Increasing Free Space In Dba_free_spaces?
Jan 16, 2013
I am Using Oracle 11.2.0.3. I had a script that droped partitions for a table which should have dropped nearly 30 gb of data. We have data and index tablespace.
Query of dba_free_space showed no change on data tablespace. However, index tablespace showed increase in free space.
select sum(bytes / (1024 * 1024 * 1024)) "Size (GB)" from dba_free_space where tablespace_name ='&tbs_name'
I am using following command to drop partition
ALTER TABLE table_name
DROP PARTITION "partition_name"
UPDATE GLOBAL INDEXES;
What should be done to increase free space shown for tablespace after partition drop?
View 2 Replies
ADVERTISEMENT
Oct 29, 2012
try to evaluate it as IMDB cache.I am facing this error repeatedly. I have increased perm size from 6.25 GB to 10 GB.After inserting about 460.000 rows I get the error again. Is it possible that 460.000 rows need 3.75 GB space?
In Oracle database these rows occupy about 200 MB space.
View 2 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
Feb 26, 2013
for some reason,i want to know which data block contains free space,or which table/index contains free space.
View 6 Replies
View Related
Nov 5, 2013
is it possible to get information about free space on drive/path for example with some DBMS_*, UTL_* package or with stored function based on Java
(e.g. CREATE OR REPLACE FUNCTION GetFreeSpace(driveLetter IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'DiskSpace.getFreeBytes(java.lang.String) return java.lang.String') but for JDK 1.3
View 8 Replies
View Related
Jul 23, 2011
How do we find the free space in tablespaces in a standby database
View 4 Replies
View Related
Jul 9, 2012
I've installed GI 11.2.0.3 on AIX 7.1.And today I noticed strange ASM's behavior:
-bash-3.2$ getconf DISK_SIZE /dev/rhdisk2
*2285768*
-bash-3.2$ asmcmd
ASMCMD> lsdsk -k
Total_MB Free_MB OS_MB Name Failgroup Failgroup_Type Library Label UDID Product Redund Path
*188616* 68204 *188616* DATA_0000 DATA_0000 REGULAR System UNKNOWN /dev/rhdisk2
I guessed that was because of LUN size (it was exceed 2 TB)After that I dinamically shrinked LUN size on our external storage, rebooted and perfomed cfgmgr command on both nodes. But I still have no enough free space.
-bash-3.2$ getconf DISK_SIZE /dev/rhdisk2
*1992294*
-bash-3.2$ asmcmd
ASMCMD> lsdsk -k
Total_MB Free_MB OS_MB Name Failgroup Failgroup_Type Library Label UDID Product Redund Path
*188616* 68204 *1992294* DATA_0000 DATA_0000 REGULAR System UNKNOWN /dev/rhdisk2
View 2 Replies
View Related
Oct 11, 2013
Some trace files were deleted with rm command. Obviously they were active and now the free space does not free up (over 9GB). How can this be fixed without database interruption?
View 4 Replies
View Related
May 5, 2010
Im a oracle pl/sql developer but I didnt learn oracle as a language. With my sql skills I started working on Oracle.In my project every table has an associated MLOG$ table for it.
For eg.
CREATE TABLE MLOG$_TEST
(
ID VARCHAR2(64 BYTE),
SNAPTIME$$ DATE,
DMLTYPE$$ VARCHAR2(1 BYTE),
OLD_NEW$$ VARCHAR2(1 BYTE),
CHANGE_VECTOR$$ RAW(255)
)
So is MLOG$_TEST oracle's internal table.
Whenever an insert/update/delete happens it is recorded in MLOG$ tables as I/U/D for the priamry key.So if I do a bulk delete of records in tables an entry is made into MLOG$ tables. So deleting old records in a database doesn't free much space.If this is oracle internal table is it advisable to delete from MLOG$ tables too.
My oracle database is mounted on the Linux server.If I delete bulk records in tables from which path I can see how much space is freed.
View 9 Replies
View Related
Mar 3, 2011
I have partition based table one the basis of year month. And we have 8 local indexes on this table. Every month we have to create a new partition and load data into this partition and the volume of the data is around 14million and the load process is taking long time due to indexes. Is it possible to drop the indexes from particular partition?
View 8 Replies
View Related
Jun 29, 2011
I need to drop one of the schema with 700 tables. There are other schemas in same database and tablespaces are shared across.
How can I reclaim the disk space after dropping the particular schema?
View 4 Replies
View Related
Apr 19, 2012
we have our production database running on 11.2.0.2. I have a user table which has partitions. i would like to drop the partition and seeing weird issue:
The command to drop the partition i have is:
alter table SA.accounts drop partition dec_20111203 update global indexes;
Say after 10 minutes, the session is terminated and we are seeing the following: ORA-00028. We don't have a process that kills the session so we are trying to find the root cause why the session is being killed. what might be going on? also as an alternative - i was looking to do the following and want to know - if this works or not:
ALTER TABLE sa.accounts TRUNCATE PARTITION dec_20111203;
Then try to do the drop partition again.
View 7 Replies
View Related
Jun 21, 2011
I have one user CD_APP. I have one partition table CD.T_FCDR_DT. User has got ALTER/INSERT/UPDATE/DELETE/SELECT privileges on the table..
Now when I try to drop a partition, I get error as below:
-------------------------------------
SQL> show user
USER is "CD_APP"
SQL> ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES;
ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-01031: insufficient privileges
--------------------------------------
Do I have to grant some other privileges for this user.
View 12 Replies
View Related
May 28, 2010
i have table with range partition and list sub-partition..can i add one more list sub-partition if it is not possible , i have to drop first sub-partition.
View 6 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
Oct 3, 2013
How to increase ASM DATA free size? I tried deleting expired backups and old archive logs which are backed up more than one time. Still no success ..
View 1 Replies
View Related
Nov 8, 2011
Due to an ORA-08177: can't serialize access for this transaction in our Java application using serializable transactions and after running some tests, we decided to increase INITRANS from 3 to 5 for our tables.
what would be the disadvantages from this measure? Space? I have done some tests with large tables with INITRANS 3 and 5, populated with large amounts of data and the space occupied is the same in bytes and blocks. Performance? Something else?
View 9 Replies
View Related
Mar 17, 2011
I Know we can create dynamic partitions on table in oracle 11g. Is it possible to create normal partition and sub partition both dynamically.I have to create Normal partition range on date and sub partition list on Batch ID (varchar).
View 3 Replies
View Related
Jun 21, 2012
I had created a Primary key and wanted to compress as per my senior instructions.Below are my results the size increased after compression.
select compression from dba_indexes where index_name = 'TEST_IDX';
Compression
----------
DISABLED
select sum(blocks) no_of_blocks, (sum(blocks)*8192)/(1024*1024)size_MB
[code]....
We ran a compression on the primary key index TEST_IDX
ALTER INDEX SCOTT.TEST_IDX REBUILD INITRANS 15 TABLESPACE DATA_01 COMPRESS;
ANALYZE INDEX SCOTT.TEST_IDX VALIDATE STRUCTURE;
Now when i ran the below select statement:
select compression from dba_indexes where index_name = 'TEST_IDX';
Compression
----------
ENABLED
select sum(blocks) no_of_blocks, (sum(blocks)*8192)/(1024*1024)size_MB
[code]....
As you can see after compression the blocks and size has been increased, but i ran for many tables and other indexes, we observed the blocks and size was reduced by 50-70%, i am not sure why this happened to the index compression.
View 3 Replies
View Related
Jul 30, 2013
1.2.0.2 on RHL.. 3 Log Groups with 1 member each. db_recovery_file_dest string /oracle/oraarch For the purpose of increasing log file size, if i use ALTER DATABASE ADD LOGFILE GROUP 1 SIZE 300M; but it creates Log Group with 2 member. one is at /oracle/oraarch location and other at /oracle/oradata (db_create_file_dest).
We are using ORACLE MANAGED FILE SYSEM . I want only 1 member at /oracle/oraarch (to keep the previous setting intact ...just increasing the size from 100 to 300M). If I manually give the path where to create the logfile member, I get this error:
ALTER DATABASE ADD LOGFILE GROUP 1 '/oracle/oraarch/DB/onlinelog/' SIZE 300M;
ALTER DATABASE ADD LOGFILE GROUP 1 '/oracle/oraarch/DB/onlinelog/' SIZE 300M
*
ERROR at line 1:
ORA-00301: error in adding log file '/oracle/oraarch/DB/onlinelog/' - file cannot be created
ORA-27038: created file already exists
Additional information: 1
View 8 Replies
View Related
Jun 26, 2010
I have a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk
when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)
View 3 Replies
View Related
Apr 3, 2013
At present we have a non partitioned table.
Can we apply redefinition and create range partition and hash sub partition on it?
View 2 Replies
View Related
Mar 7, 2010
I want to increase the size of the tablespace but when i login as sysdba or admin user i can just see the 21 tables in the dba_tablespaces or user_tablespaces. I want to see the tablespaces related to the application.
View 8 Replies
View Related
Jan 10, 2012
After the reboots each system was using around 30 GB of memory... now that it's been up for a week memory is up to 98GB used on each system. None of the systems are swapping.
Mem: 98999084k total, 97937116k used, 1061968k free, 774900k buffers
Swap: 16779884k total, 0k used, 16779884k free, 89510312k cached
View 2 Replies
View Related
Nov 30, 2012
Considering the below factors, I am planning to increase the buffer cache value from 256Mb to 512Mb.
1. Buffer cache hit ratio value is around 35% even in the normal period.
2. free buffer requested value is below during peak & normal hours below.
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
free buffer requested 54,694,995 15,226.9 2,523.7
free buffer requested 23,412,674 6,501.7 2,585.9
3. most of the top 5 physical reads & logical reads queries are well tuned and some of queries are doing FTS on small tables (table count min 1500 max 35000). SO indexing option is not required for these queires. But these queries getting executed frequently.
SQL> show sga
Total System Global Area 2148534928 bytes
Fixed Size 731792 bytes
Variable Size 1879048192 bytes
Database Buffers 268435456 bytes
Redo Buffers 319488 bytes
5.top 5 waitevents during db slow performance & high cpu utilization (>80%) issue.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free 1,848,898 153,793 52.00
buffer busy waits 395,280 87,201 29.49
db file scattered read 3,488,648 34,199 11.56
enqueue 4,052 10,897 3.68
CPU time 5,567 1.88
6. Top 5 waitvents during normal activities and CPU utilization is around 40%.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 1,860 45.32
db file scattered read 1,133,669 985 23.99
imm op 776 605 14.73
sbtinfo2 208 139 3.40
sbtbackup 2 123 3.00
View 11 Replies
View Related
Mar 6, 2012
we are inserting a data in table by selecting data from another table.This is part of a very big procedure and the code is like
Loop
SELECTMAX(biz_trn_no) + 1
INTOv_biz_tran_no
FROMnew_business_transactions
WHEREnb_srno = nb_sr_no;
[code]....
Is there any way I can get this functionality without using a sequence? I assume corelated query would be slow and analytic.
View 6 Replies
View Related
Apr 14, 2012
I have a table with numbers like this in a column
sr no. value
1 17
2 0
3 0
4 38
5 32
6 24
7 12
8 51
9 42
10 1
11 1
12 0
13 0
14 1
15 1
.
.
.
so on....
I want to find out consecutive non-increasing sequences of value (2nd column) order by sr. no (first column) in ascending order.
For example, in the 2nd column, 17 is followed by 0 and 0 and then 38 so it means 3 consecutive values (i.e starting from 17 are 0 and 0) are non-increasing and they are ranked by '1' in my desired in third column as shown below. similarly, the 2nd non-increasing sequence starts with 38,32,24 and 12 and this is ranked as '2' in the third column. same is the case with rank '3' for the third non increasing sequence. so bascially i want the third column with "ranks" starting and ending as per above logic. i tried using LEAD function but doesn't get what I want. I need the shortest possible query to get that third column since i have other columns in the original table in a multiple group by query.
my desired output is:
sr no. value Rank
1 17 1
2 0 1
3 0 1
4 38 2
5 32 2
6 24 2
7 12 2
8 51 3
9 42 3
10 1 3
11 1 3
12 0 3
13 0 3
14 1 4
15 1 4
.
.
.
View 3 Replies
View Related
Dec 5, 2012
My listener.trc file has grown to a size of 56G and is contineously growing with the following message:
9:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:670] naeshow: [05-DEC-2012
[code]...
View 1 Replies
View Related
Feb 27, 2008
The size of redolog member is 12m . Can I increase the size of that member dynamically, without adding a new member to that group and dropping the old one.
View 8 Replies
View Related
Jul 6, 2012
I noticed that listener file in diag/trace is rapidly increasing. I am using 11g Rel 1 on window 2008 server Its 1 GB in couple of days. Is there any way if I can turn it off ?
View 5 Replies
View Related
