Oracle 10g - How To Manage Partition Sizes Dynamically
Jun 6, 2012
We are using oracle 10g. with our code, Currently Oracle partitions are size the same way, each partition is using 10MB for data and 12MB for indexes (with the 6 default indexes); even of very few records are written in the partition.
We create partitions in advance as a part of nightly job with 10 minutes duration.Can some intelligence can be added where based on statistics we can decide the size of partition dynamically? Lot of space is getting wasted because of this reason.
I have created an Interval Partition Table as show below, Is their any way, i can drop the partition dynamically when i truncate the table as oracle creates them with system generated name? Instead Alter table drop partition !
Insert Script : ------------------------------------------------------------------------------------------------------------------------------------------------ INSERT INTO TBL_EMP_SALES VALUES (1001,'A',50,SYSDATE); INSERT INTO TBL_EMP_SALES VALUES (1002,'B',100,SYSDATE+1); INSERT INTO TBL_EMP_SALES VALUES (1003,'C',80,SYSDATE+2);
[Code]....
Partitions Created : ------------------------------------------------------------------------------------------------------------------------------------------------- select segment_name,partition_name,bytes from user_segments where segment_name = 'TBL_EMP_SALES'; segment_name partition_name bytes
what people have set for the SGA and PGA sizes for their larger usage, larger data, databases? I've been seeing one of our warehouses grow both in terms of tables (number and sizes) and users groups querying the database. We're at 96g sga and 10g pga, but was thinking in terms of 1/2 tB machine to pin some larger tables. I know we'll have SSD soon, but am seeing enourmous numbers of reports using windowing and analytic functions and in line sets being created. How big in general do you have your larger systems set to?
I did some google searches about large number of extents and ASSM. I see bits and pieces on the web. This is something I need to look at while testing an application. Not looking to go into 'why' I would use smaller extents, I just want to make sure I have what I need to look for during testing..Issues with massive numbers of extents:
1. DBA_EXTENTS query is really slow. 2. issues truncating tables (due to having to read lots of extents) 3. issues splitting maxvalue partitions and with dropping partitions. 4. if I stay away from ASSM, would this reduce these issues? Are there any other performance issues or other issues I need to know about to check when I do tests?
Any issues with query or insert wait time? The tables that would get smaller events would have thousands of partitions/sub-partitions . Most of these sub-partitions will be rather smaller.I just want to test for a variety of different cases. The 'why' will come out during testing.
Before I begin, I want to clarify that I am newbie in the administration of data warehouse.I need to know how to calculate the sizes of the archive and redo on data warehouse DB, in order to make an initial sizing of the BD on disks level.
is there a way to set the default sizes for the canvas, object navigator and properties window in forms designer so that they don't maximise when opening them. i tried to set them in the caupref and cagpref files to no avail.
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.
I am using Oracle XE with SQL Developer to teach a course on databases.
The students complete a number of small projects. How can a student separate the projects so as to be able to open in SQL Developer only the tables pertaining to a specific project? Ideally, the student would be able to assign the same name to two tables that belong to different projects.
I know that this can be done by naming conventions and filtering, but this is awkward.
A Java program would call a sequence of 5 PLSQL procedures, and they all need to participates the Tranasction defined in Java.
What do I do for the transactions inside PLSQL to ensure all 5 pariticpate in the Java transaction? I suppose I just do not specify any COMMIT in the PLSQL, but that probably would not work as PLSQL would COMMIT by default after exiting the proc?
We just purchased NAS (Raid5) unit to manage our data storage. I am planning to create a virtual partition on this nas device and use one partition for oracle data storage and another virtual partition will be used by other data (files and may be sqlserver data files....etc..)
We will have oracle installed on seperate oracle server. Can we use RMAN to manage incremental backups in this environment? May main worry point is that our data storage device will have many different type of datas and will we be able to tell RMAN to make backups only from certain virtual drives?
I wiuld like to suppress oracle messages conditionally. I am running oracle view creation scripts from command line, After successful view creation, it is showing me "view created" message. To suppress this, I have created following
set serveroutput on format wrapped; SET TERMOUT OFF @@test1.sql SET TERMOUT ON
[Code]....
but still it is giving me "PL/SQL procedure successfully created" message along with "'View test831 created successfully". I need to suppress "PL/SQL procedure successfully created".
I have successfully installed the Oracle 10g standerd version with a sample database on Windows server 2008 standard server. Then I installed the client so that I could use the Enterprise manage console that comes with it. Once installed, I was able to successfully configured the Net service. Now when I use either Enterprise manage console or SQL plus to login to the service that I created, once I enter the user id and password and click ok, system automatically closes both applications. I am not sure what is missing. I installed the same client on a windows XP pc and the enterprise manage console is working perfect.
I have a diskgroup with normal redundancy level (2 two failure groups) working fine. I desire add a new failgroup to diskgroup (changing the redundancy level to high).
Which ASM process will manage the copy of data to new failgroup? Are there some way to control or tuning this process?
1. How many oracle mechanism can link with oracle send mail mechanism ?? 2. Any samples which the oracle send mail mechanism to run with some oracle alert mechanism.
I upgraded my APEX instance from 4.1 to 4.2 and now I can't go to (INTERNAL) Manage Instance->Instance Settings I see only ORA-28817: PL/SQL function returned an error.
I have to manage an active passive cluster on SLES10 SP4. Oracle 10.2.0.4. All oracle binaries are on a DRBD Device on mountpoint /oradata. This mountpoint includes the binaries and all datafiles.
I havent installed these systems. At one stage on node died and got reinstalled by a system administrator. After a switch from the running node to the standby node, the cluster is not starting.
Jan 3 14:36:01 a logger: Cluster Ready Services waiting on dependencies. Diagnostics in /tmp/crsctl.6114. Jan 3 14:36:14 a logger: Waiting for Oracle CSS service to be available before starting Jan 3 14:36:14 a logger: ASM instance +ASM. Wait 2.
After issuing $ORACLE_HOME/bin/localconfig add the cluster is starting, the asm instance is coming up, and also the database instance is starting.
Shortly after that the db instance dies. Alert log is showing something like recursive sql error. Looks like writing to the datafiles is not working. In asmcmd the diskgroups are showing DISMOUNTED (show MOUNTED before)
so what to do ? a) Why is it necessary to issue "localconfig add" to make the cluster starting ? b) What could be a reason for not being able to write to the database ? c) How to install an active/passive cluster ? c1) Install OS on both nodes c2) Install oracle binaries c2.1) on both nodes in the local files system and do the mapping to the drbd afterwards? c2.2) only on one node and just switch the mountpoint ?
I'm developing, or trying, on Apex 4.2...The issue is about themes and how to manage the positions of diferents Display Points of a theme.
Everybody know that any theme have a map where its position all differents parts of this map (Body1, Body2, Body3, position01, position02 and so). So when you choice any theme, it come with all this Display points defined. You can see this map rendering any region page and in the item Display Point you can see this map clicking on the light.
There is anyway to can manage this map and change position or behavior of those points?. For example , I'm interested that under the tabs, to have at the same height, 3 diferentes position, one at left, one at center and other at right.
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).
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)
I would like to change the printing settings in my workspace Instance settings. But, I can't find the "Manage Instance" option mentioned in the tutorials.
I have a question related to partitions and dividing into subpartitions on the existing table.Situation is as follows:
1. we have an inventory table with a list partition on one column sales_desk_id. 2. This table contains millions of records. Due to concurrency and due to high amount of data inserts, now there is a need to make sub partitions based on sale_date.
Question: is there any way to make the subpartitions without dropping the tables?
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production
Can I add range sub partition to a hash partition table. Example like this.
CREATE TABLE test ( test_id VARCHAR2(10 ) , test_TYPE VARCHAR2(5) , CREATE_DATE date ) partition by hash (test_id, test_type) Partitions 3 SUBPARTITION BY RANGE (CREATE_DATE);
When Tried, I am getting syntax error as invalid option.
