Server Administration :: Get Script That Create Partition By Auto?
Oct 27, 2011Is there a script that it create partitions by auto? who can share it?
View 1 Replies
ADVERTISEMENT
Server Administration :: Create Subpartition For Existing List Partition
Dec 14, 2012We have a table which is already partitioned by list and now we would like to add a subpartition.
Create table Item_mst
(
ccn varchar2(10),
Flag varchar2(1),
Item varchar2(10),
status varchar2(1)
)
[Code]..
Now I could like to alter the above table for adding a new subpartition on each partition on status.
ALTER TABLE Item_mst
MODIFY PARTITION Item_mst_Test
ADD SUBPARTITION Item_mst_Test_A VALUES ('A');
Above alter gives ORA-14253: table is not partitioned by composite range method.
However dropping and recreating the table with subpartitions is working fine.
But Dropping and recreating the table in production is very cumbersome as it has huge data and many indices.
Server Administration :: When Did A Datafile Auto-extend
Dec 21, 2010Is there a way to find out when a datafile for an undo tablespace with autoextend enabled actually extended? I've done a few tests, and nothing is written to the alert log or any trace file that I've found. I can't find any V$ or DBA view that will give me the history of a file's size.
View 5 Replies View RelatedServer Administration :: Segment Space Management Is Auto?
Oct 18, 2010the syntax to make a tablespace's segment space mnagement auto which is segement management is *manual* and exent management is *local autoallocate*.
View 2 Replies View RelatedServer Administration :: Objects Invalid Due To Auto-compilation
Jun 27, 2012One of my friends is facing a peculiar problem where objects are getting "Invalid" during execution I suspect it is happening as they are changing system date during their testing (time travel) which can create conflicted last_ddl_time on objects having dependencies
Consider a scenario
[1] system date is 10-06-2012 there are total 10 objects which has status as 'valid'
[2] the system date is changed to 10-07-2012 Now out of 10 Only 5 objects are compiled During execution ORA-04065,ORA-06508, ORA-06512 are observed
[3] the system date is brought back to 10-06-2012 Again during execution ORA-04065,ORA-06508, ORA-06512 are observed
suppose in step 2 objects are compiled whereas there synonyms are compiled in step 1, only thus last_ddl_time for objects will be later to that of its' synonym...
Does database validate last_ddl_time for objects having dependency during execution and then auto-compiles or invalidates the objects?
Server Administration :: Monitoring Auto Allocate Tablespaces
Nov 7, 2011Currently all my tablesapces are "UNIFORM" and I am looking into going to "AUTOALLOCATE" so space will not be some much of an issue.
I am using the following code and here is the output
sqlplus -s / <<-EOT
set pages 50
set line 100
set verify off
set feed off
set trimspool on
[code]........
Have a script that can emulate my above output and also deal with tablespaces that can autotallocate?
Server Administration :: Tablespace Auto-Extend OFF On Production System
Jun 6, 2012I want to OFF tablspace AUTOEXTEND on a prodution system, we have many RAC databses and that will be done on all stations. i have got a document from net which was written on 29-Jun-2007 and it says that if need to OFF the AUTOEXTEND of a TABLESPACE so you need to ist make it off on the underlying datafiles of that tablespace so this doc is for Oracle 8.1.7.2.0
View 2 Replies View RelatedSQL & PL/SQL :: Create Normal Partition Range On Date And Sub Partition List On Batch ID
Mar 17, 2011I 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 RelatedSQL & PL/SQL :: Apply Redefinition And Create Range Partition And Hash Sub-partition?
Apr 3, 2013At present we have a non partitioned table.
Can we apply redefinition and create range partition and hash sub partition on it?
Server Administration :: Cannot Use Function In Partition-key
Aug 18, 2011It cannot use function in partition-key, right?
create table tb_hxl_user_rh
(
statedate number(8),
provcode number not null,
usernumber varchar2(13) not Null
)
partition by range (statedate)
(
partition HXL_USER_20110516 values less than
(to_number(to_char(TO_DATE('2011-05-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),'YYYYMMDD')))
);
ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE
Server Administration :: Creating Tablespace In Raw Partition?
Aug 17, 2012i want to create tablespace in raw partion on windows.I have added a hard disk and added extended partition to it. Then created 4 logical partitions of 256 MB with diskpart.exe.I have assigned a drive letter to one of them as X:
However, I am having trouble in creating tablespace on it. I referred to [URL]... I get following error,
C:UsersAdministrator>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 17 15:32:17 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: SYSTEM
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing opti
SQL> create tablespace exampletb datafile '\.X:accounting_1' size 100;
create tablespace exampletb datafile '\.X:accounting_1' size 100
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
SQL> create tablespace exampletb datafile '\.X:accounting_1' size 502;
create tablespace exampletb datafile '\.X:accounting_1' size 502
[code]...
Server Administration :: Default Partition In Table?
Oct 17, 2011How can i check whether a partition tbale have default partition?
View 9 Replies View RelatedServer Administration :: Difference Between Partition Compress?
Nov 11, 2010I am using compress partition for a large table.During the process I have used the below command for compress---
1. ALTER TABLE <TABLE_NAME> MODIFY PARTITION P1 COMPRESS;
However I found there are different command as below:-
2. ALTER TABLE <TABLE_NAME> MOVE PARTITION P1 COMPRESS;
What is the basic difference between these two as the compression is happening for both command.
Server Administration :: Move Partition To Cache
Feb 28, 2012How to move a partition of a table to db_keep_cache ?
View 8 Replies View RelatedServer Administration :: How To Check A Partition Have Been Compressed
Oct 20, 2011How can i check a partition whether it has been compressed? just as flowing test,i can not get the information about partition P_L1 whether been compressed.
SQL> Select
2 aa.compression,
3 aa.partition_name
4 From dba_tab_partitions aa
5 Where aa.table_name = 'TB_HXL_LIST';
COMPRESS PARTITION_NAME
-------- ------------------------------
DISABLED P_L1
DISABLED P_L2
DISABLED P_L3
DISABLED P_L4
SQL> Alter Table tb_hxl_list compress;
Table altered.
SQL> Alter Table TB_HXL_LIST
2 Move Partition P_L1 compress;
Table altered.
SQL> Select
2 aa.compression,
3 aa.partition_name
4 From dba_tab_partitions aa
5 Where aa.table_name = 'TB_HXL_LIST';
COMPRESS PARTITION_NAME
-------- ------------------------------
ENABLED P_L1
ENABLED P_L2
ENABLED P_L3
ENABLED P_L4
Server Administration :: How Partition Can Be Brought Offline
Jan 4, 2012I have a partitioned table - 128 partitions.
If I am not using data of many partitions in any way, will it affect my performance if I am firing select query that uses other/active partitions data.
How can I check when that partitioned was last accessed, also can I brought those inactive partitions offlie? If we can, what will be the advantages or disadvantages of that?
Server Administration :: Error While Exchanging Partition?
Mar 15, 2012I am not able to exchange partition by following below steps:
drop table manu_tst_hashed_tbl_org;
drop table manu_tst_hashed_tbl_bkp;
drop table manu_tst_smpl_tbl;
CREATE TABLE manu_tst_hashed_tbl_org
[code]...
and getting the below erro:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
14097. 00000 - "column type or size mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause: The corresponding columns in the tables specified in the ALTER TABLE EXCHANGE PARTITION are of different type or size
*Action: Ensure that the two tables have the same number of columns with the same type and size.
Server Administration :: Disable Partition Feature
Feb 2, 2012I am using Oracle 10g enterprise edition .
Currently, Partition is enabled in my database.I want to disable it.
SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Partitioning';
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Partitioning
TRUE
the steps to disable the partition feature.
Server Administration :: Privileges For DROP Partition
Jun 21, 2011I 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.
Server Administration :: Exchange Partition With Update Indexes
Nov 7, 2011I exchange a partition with into a normal table with UPDATE INDEXES,but i found the index of both table are marked UNUSABLE?
SQL> Create Table tb_hxl_list_part
2 (
3 statedate Number,
4 provcode Number
5 )
6 Partition By List(provcode)
7 (
8 Partition p_1 Values(1)
9 );
Table created.
SQL> Create Unique Index idx_tb_hxl_list_part On tb_hxl_list_part(provcode) Local;
Index created.
SQL> Insert Into tb_hxl_list_part Values(20111101,1);
1 row created.
SQL> commit;
Commit complete.
SQL> Select status From dba_ind_partitions aa
2 Where aa.index_name = 'IDX_TB_HXL_LIST_PART';
STATUS
--------
USABLE
SQL> Create Table tb_hxl_list_part_bak
2 (
3 statedate Number,
4 provcode Number
5 );
Table created.
SQL> Create Unique Index idx_hxl_list_part_bak On tb_hxl_list_part_bak(provcode);
Index created.
SQL> Select status From dba_indexes bb
2 Where bb.index_name = 'IDX_HXL_LIST_PART_BAK';
STATUS
--------
VALID
SQL> Alter Table tb_hxl_list_part
2 Exchange Partition p_1
3 With Table TB_HXL_LIST_PART_bak UPDATE Indexes;
Table altered.
SQL> Select status From dba_ind_partitions aa
2 Where aa.index_name = 'IDX_TB_HXL_LIST_PART';
STATUS
--------
UNUSABLE
SQL> Select status From dba_indexes bb
2 Where bb.index_name = 'IDX_HXL_LIST_PART_BAK';
STATUS
--------
UNUSABLE
Server Administration :: Partition Exchange In Hash Partitioned Table
Mar 13, 2012While trying partition exchange feature of Oracle with 2 hash partitioned tables, I come to know that I can't directly exchange partitions between 2 partitioned tables
I have two hash partitioned tables , so to move partition data from one table to another will include-
1) Exchange from partitioned table to non-partitioned table.
2) exchange from non-partitioned table to new partitioned table.
But I am not sure in which hash partition my data will go in new partitioned table (data need to be moved has single key value on basis of which tables are partitioned),
Server Administration :: Impact On Table Of Partition Truncate / Delete?
Oct 21, 2012Any impact is there if is do the following:
ALTER TABLE MY_TABLE TRUNCATE PARTITION P1 UPDATE GLOBAL INDEXES;
will there be any lock on the table during this operation? DML operations will work without any issue or not?
Server Administration :: ORA-14099 / All Rows In Table Do Not Qualify For Specified Partition
Sep 1, 2011The table tb_hxl_hash have two partitions,when i exchange the second partition,it raise error,why?
SQL> Alter Table tb_hxl_hash
2 Exchange Partition P_HS1 With Table tb_hxl_test;
Table altered.
SQL> Alter Table tb_hxl_hash
2 Exchange Partition P_HS2 With Table tb_hxl_test;
Exchange Partition P_HS2 With Table tb_hxl_test
*
ERROR at line 2:ORA-14099: all rows in table do not qualify for specified partition
Server Administration :: Splitting Table Partition Without Making Primary Key Index Unusable?
Apr 8, 2013splitting a table partition without making its primary key index ar any other indexes unusable.
I think it is possible to do so 10g onwards.
DB Details:
Oracle RDBMS 11.2.0.3, HP-Ux B.11.31, OLTP
Create Code In A Way That AppID Is Auto Incremented
Nov 16, 2011I have a database table App, with a primary key,AppID. So I need to insert Data in App, which will auto generate AppID.
My Store Procedure goes something like this:
AppID IN NUMBER
, PersonID IN NUMBER
, ContractorID IN NUMBER
, erfNo IN VARCHAR2
, streetNo IN VARCHAR2
so I need a to create the code in a way that AppID is Auto incremented, and it is a returned value after I inserted values.
SQL & PL/SQL :: Create Trigger Not Working For Auto-increment
Jun 16, 2010Step1
CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));
Step2
CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;
Method1 Follow Step1 and Step2 and create a Trigger as below :
CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/
Method 2 Follow Step1 and Step2 and directly have an insert statement as below:
INSERT INTO test (id, name) VALUES (test_sequence.nextval , 'Jon343');
Method 1, the Trigger is not getting created, however by following Method 2, I am able to generate auto-increment number.
SQL & PL/SQL :: How To Auto Increment Number - Create Sequence And Trigger
Nov 26, 2012I am switching database from access to oracle 11g. I have create all the required tables, but I am stuck at one point. The previous person who created access database had auto increment with SG0101, SG0102,........ In oracle I know we can auto increment primary keys but only with the numbers not with characters.
So I have customerid which is a primary key and it automatically increments the number, but I have one more column with memberid where I am inserting all the ids that start with SG0101 bla bla.....
I already have 800 member ID's that start with SG, but that value doesnt automatically increment because I dont have any sequence or trigger to do that.
So how do I create a sequence and trigger that will automatically start value with SG and keeps auto incrementing?
Server Administration :: Create Tablespace For Administration
Nov 29, 2010i'm a student currently learning database administration security.
I need to create a tablespace for administration of database but i don't know what datafile settings are best suited for admin usage.
I have attached the schema that was given to me for this assignment.
Create A Sequence For Primary Key To Simply Auto-increment By Default Of 1
Mar 14, 2005I'm trying to create a sequence for a primary key to simply auto-increment by the default of 1. I have a sql script written to generate mt tables, and I'm not sure how to modify the script to include the sequence. I also just want the sequence for a specific column, ie, PK, not the PK in all tables.
Here's a snippet from my script:
create table image
(
image_id int NOT NULL,
source_id int NOT NULL,
CONSTRAINT image_id_pk PRIMARY KEY (image_id),
CONSTRAINT fk_source_id FOREIGN KEY (source_id) REFERENCES source(source_id)
);
Would I add the create sequence statement right after the create table, and if so, how do I apply the sequence to only 1 table and a single column?
Server Administration :: Create Multiple Database In Single Server
Apr 18, 2011I have database in single server. I already have ORACLE_SID=stagedb. So i want to create new sid and named as proddb.Is it possible have multiple db in single server? This server not running as RAC. It run as single only.
View 1 Replies View Related