SQL & PL/SQL :: Dropping Unused Columns In A Partition?

Jan 21, 2003

I have been trying to drop an unused column in a partitioned table, and the number of records stored in this unused column was very high. I kept on running into errors as follows:

ORA-01562: failed to extend rollback segment number 10

ORA-01650: unable to extend rollback segment R09 by 256 in tablespace RBS

I tried to "SET TRANSACTION USE ROLLBACK SEGMENT <name>" with a larger rollback segment, but it still did not work. Can I drop the "unused column" from each partition instead?

How to apply that? Or, what are my options besides increasing the size of the rollback segment?

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Dropping Sub-partition / Range-partition And List Sub-partition

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

Server Administration :: Unused Columns

Jul 13, 2013

If you mark a column unused, is there any way to project it? I know the docs say you can't, but as the data is still there I would have thought it should be possible. I can see the column in dba_tab_cols, but the obvious ways of making it usable don't work:

orcl> select column_name,hidden_column from user_tab_cols where table_name='DEPT';

COLUMN_NAME HID
------------------------------ ---
LOC NO
DNAME NO
DEPTNO NO

orcl> alter table dept set unused column loc;

Table altered.

orcl> select column_name,hidden_column from user_tab_cols where table_name='DEPT';

COLUMN_NAME HID
------------------------------ ---
SYS_C00003_13071316:19:02$ YES
DNAME NO
DEPTNO NO

orcl> select "SYS_C00003_13071316:19:02$" from dept;
select "SYS_C00003_13071316:19:02$" from dept
*
ERROR at line 1:
ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier

orcl> alter table dept rename column "SYS_C00003_13071316:19:02$"
2 to loc;
alter table dept rename column "SYS_C00003_13071316:19:02$"
*
ERROR at line 1:ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier

orcl> alter table dept modify "SYS_C00003_13071316:19:02$"
2 visible;
alter table dept modify "SYS_C00003_13071316:19:02$"
*
ERROR at line 1: ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier

View 2 Replies View Related

Server Administration :: How To Find Unused Columns

Nov 9, 2011

is there a way to find out which unused columns in oracle?

View 8 Replies View Related

PL/SQL :: Dropping A Partition?

Jul 7, 2012

Do i have to create indexes again if i drop a partition on a table?

View 6 Replies View Related

PL/SQL :: Dropping Partition Dynamically In Interval Partitioning

Aug 22, 2012

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 !

Create Table Script :
------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE TBL_EMP_SALES
(
EMP_ID NUMBER(38),
EMPNAME VARCHAR2(9),
EMP_SALES_AMOUNT NUMBER(5),
EMP_SALES_DATE DATE,

[Code]....

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

TBL_EMP_SALES     P0     8388608
TBL_EMP_SALES     SYS_P602     8388608
TBL_EMP_SALES     SYS_P603     8388608

[Code]....

View 5 Replies View Related

Partition The Same Table Using Different Columns

Mar 5, 2013

I’m facing an issue in my current project where we have to run batch jobs and interfaces on the same master tables. These tables are huge (millions) and we get very poor response time.

We thought of partitioning the tables, but the problem is our batch jobs queries are based on dates (some run on monthly data, some runs on yearly data..) but interfaces uses primary keys.

I’m not sure on how to partition the tables in this situation, is there any way I can partition the tables in such a way that both batches and interfaces will get benefit out of it?

Our database infrastructure has a 3 Node RAC (each with 8 CPUs) and oracle 10g R2. We have almost 60gig of RAM allocated to oracle.

PS: We are not thinking about Mviews now because client wants to explore the partition option first.

View 1 Replies View Related

Sub Partition By List On Multiple Columns

May 16, 2011

Can i partition by list on multiple columns? i am trying the following code, and it is returning an error. Is there a way round this?

CREATE TABLE
(...)
PARTITION BY LIST (col1)
SUBPARTITION BY LIST (col2)
SUBPARTITION TEMPLATE (
SUBPARTITION DETAIL VALUES ('DETAIL')
SUBPARTITION ROLLUP VALUES ('ROLLUP'))
(
PARTITION RT VALUES('RT')
)

View 1 Replies View Related

Hash Partition On Primary Key Columns?

Jul 18, 2012

I have a existing non partition table with more than 100 million records,planning to re design using Hash partition.This table doesn't has any range column to do range partitioning.

Table has 40 columns with a Primary Key on two columns (guest_sales_Id ,Version Flag). guest_sales_Id is unique for entire table but with anopther column version Flag declared as Primary key.(Version Falg will have only two distinct values in entire table)

If i do hash partition,do i need to declare on two columns which are declared ad Primary key ?If i use only guest_sales_id to declare hash prtition any issues ?

View 2 Replies View Related

Finding Unused Index

Nov 3, 2008

I have read and used the AWR script (mentioned in the page Finding unused index for finding unused customised (Z) indexes in our SAP system using oracle 10.2.0.2 as the SAP database.

But this returns no rows. Is there any precondition? I want to know how much / many times the indexes are used...We are smelling that lot of unused index are there in the database.

View 8 Replies View Related

SQL & PL/SQL :: Unused Variables In A Procedure

Jul 30, 2010

A way to identify variables declared in a procedure that are not being used? I had thought plsql_warnings might do the trick but it does not.

I have inherited a lot of code that was ill conceived and unfortunately have started to notice a trend.

View 8 Replies View Related

SQL & PL/SQL :: Index Unused State

May 7, 2013

I made an Index Unused while doing some update by using sql developer right click 'Make unusealbe'. Now I need make it as useable.

marking as Usable. I have checked in all_indexes state is show as 'UNUSABLE'.

View 2 Replies View Related

Release Tablespace Unused Space

Nov 18, 2011

I have a problem...

I created a tablespace called my_ts:

CODECREATE TABLESPACE my_ts DATAFILE 'C:\Oracle\oradata\db\my_ts.dbf' SIZE 5M  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
ALTER DATABASE DATAFILE  'C:\Oracle\oradata\db\my_ts.dbf' AUTOEXTEND ON;

Its was sucessfully created and my_ts.dbf file has 5MB

charging with data...

CODEcreate table big_table tablespace my_ts as select * from dba_objects;
select * from big_table;
begin
  for i in 1..10 loop
    insert into big_table select * from dba_objects;
  end loop;
end;

Now the my_ts.dbf file has 90MB

Now I want drop this table:
CODEdrop table big_table purge;

And my tablespace file still has 90MB.

I already tried to restart the database but doesn't works...

how can I reduce the size to the original (5MB)?

View 3 Replies View Related

PL/SQL :: List Of Unused Tables And Procedures

Aug 28, 2012

I am in the task of clean up of tables. I need to find the list unused tables and procedures. Is there any way where i can find when was the last time the table queried?

Give sql query to find the list of unused tables and procedures.

View 6 Replies View Related

PL/SQL :: Unable To Drop Unused Column?

Nov 14, 2013

SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER                          TABLE_NAME                          COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER            F_TRADING_COLLATERAL_SR                1 SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER                          TABLE_NAME                          COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER            F_TRADING_COLLATERAL_SR                1 SQL> alter table F_TRADING_COLLATERAL_SR drop unused columns; Table altered. SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER                          TABLE_NAME                          COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER            F_TRADING_COLLATERAL_SR                1 SQL>

View 6 Replies View Related

Compressed Table - Drop Unused Column

Feb 9, 2012

what happens if you mark a column unused in a compressed table and then alter table drop unused columns? We had a customer do this and Oracle threw a -3113 (end of communication) error. They did a system restore before contacting us and blew away any evidence in alert logs/trace files. They did this on a 400GB compressed table.

My question is, when you drop an unused column off a compressed table, does it uncompress? Where does this uncompression occur? In the instances default tablespace? In the tablespace configured for the table?

Basically, we are wondering whether the error was due to poor error-handling of the system running out of space during decompression and trying to see if we can reproduce it. This was on an 11.1.0.7 system.

View 7 Replies View Related

SQL & PL/SQL :: Create Normal Partition Range On Date And Sub Partition List On Batch ID

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

Performance Tuning :: Select Partition Table With Non-partition Key Condition?

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

SQL & PL/SQL :: Apply Redefinition And Create Range Partition And Hash Sub-partition?

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

SQL & PL/SQL :: Add Range Sub-Partition To Hash Partition Table

Jan 2, 2013

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.

View 8 Replies View Related

SQL & PL/SQL :: Tables Activity - Inserted Partition Key Does Not Map To Any Partition

Sep 22, 2011

I have two tables Activity and Activity1.

Activity Structure
ACTIVITY_TYPE CHAR (1) NOT NULL,
ACTIVITY_DATE DATE DEFAULT sysdate NOT NULL,
ACTIVITY_ON VARCHAR2 (30) NOT NULL,
REFERENCE_NO VARCHAR2 (19),
CHILD_REFERENCE_NO VARCHAR2 (19),
USER_ID VARCHAR2 (30) DEFAULT user NOT NULL,
TERMINAL VARCHAR2 (30) DEFAULT userenv ('TERMINAL') NOT NULL )

Activity1 Structure Which I have Done Partitioning When I insert data from Activity to Activity1 it gives that error ORA-14400: inserted partition key does not map to any partition what I am doing wrong

CREATE TABLE ACTIVITY1(
ACTIVITY_TYPE CHAR (1) NOT NULL,
ACTIVITY_DATE DATE DEFAULT sysdate NOT NULL,
ACTIVITY_ON VARCHAR2 (30) NOT NULL,
REFERENCE_NO VARCHAR2 (19),

[code]....

Insert Statement

insert into ACTIVITY1(ACTIVITY_TYPE,
ACTIVITY_DATE,
ACTIVITY_ON,
REFERENCE_NO,
CHILD_REFERENCE_NO,

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Adding A New Partition To Existing Partition Table

Jun 14, 2011

i want to create a new partition for version 2

existing table is as below

create table test
(
name varchar2(100),
version NUMBER(12)
)

[Code]....

View 15 Replies View Related

SQL & PL/SQL :: Can Drop Partition Based Indexes From A Particular Partition

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

SQL & PL/SQL :: Partitioned Table - Inserted Partition Key Does Not Map To Any Partition

Jul 10, 2012

I have two tables in which one is partitioned table with the following details.

CREATE TABLE "SCOTT"."TBL_MITTAL"
("ACCOUNT_NAME" VARCHAR2(50 BYTE),
"BILL_NO" VARCHAR2(50 BYTE),
"BILL_DATE" VARCHAR2(50 BYTE),
"CLI" VARCHAR2(50 BYTE),
"ANI" VARCHAR2(50 BYTE),

[code].....

When I am trying to insert record from tbl_mittal into tbl_temp table. I am facing "ORA-14400: inserted partition key does not map to any partition" error

SQL> insert into tbl_temp select * from tbl_mittal;
insert into tbl_temp select * from tbl_mittal
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

AS tbl_mittal is having hugh number of records so I am providing only few rows from tbl_mittal table as test data.

ACCOUNT_NAMEBILL_NOBILL_DATECLIANICHARGE_START_DATEDURFROM_LOCATIONTO_LOCATIONINVOICE_IDCIRCLE
10000010357423128271095119301-Feb-111723006000931488182328-JAN-11 11.30.54.000000000 AM59.04CHANDIGARHJAIPUR271095119
10000011844187128348720198715-Jun-121409470011825531896615-MAY-12 09.10.36.000000000 AM28.03CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531927115-MAY-12 09.10.41.000000000 AM38.32CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531933015-MAY-12 09.10.46.000000000 AM28.81CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531930215-MAY-12 09.10.53.000000000 AM28.96CHANDIGARHBANTWAL348720198

[code].....

I also tried to upload the same data using sqlldr.

sqlldr log file contents is as follows:

Total logical records skipped: 0
Total logical records read: 1857532
Total logical records rejected: 801092
Total logical records discarded: 37

[code].....

So some sqlldr bad file contents is as follows.

100000118441871,283487201987,15-JUN-12,1723958000,9355115251,10-JUN-12 05.56.05.000000 PM,36.99,CHANDIGARH,AMBALA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7520533825,10-JUN-12 05.56.14.000000 PM,44.12,CHANDIGARH,AGRA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9356452151,10-JUN-12 05.56.17.000000 PM,116.83,CHANDIGARH,JALANDHAR,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9331223048,10-JUN-12 05.56.21.000000 PM,28.33,CHANDIGARH,KOLKATA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7827927893,10-JUN-12 05.56.24.000000 PM,3384.33,CHANDIGARH,DELHI,348720198,,

[code].....

View 2 Replies View Related

SQL & PL/SQL :: Updated PartitionID With Partition Number For Each Partition Set

Apr 12, 2010

needed in stored procedure to achieve this...how to get this..(stored procedure)

table:
studentid,sname,partitonid
901,x,null
902,y,null
903,z,null
904,p,null
905,q,null
906,a,null
907,b,null
908,d,null
909,f,null
910,m,null

For the above data set i need to divide into 5 partittions and need to updated the partitonid with the partition number for each partition set,like the below result set

studentid,sname,partitonid
901,x,p1
902,y,p1
903,z,p2
904,p,p2
905,q,p3
906,a,p3
907,b,p4
908,d,p4
909,f,p5
910,m,p5

View 3 Replies View Related

PL/SQL :: Alter Script For Adding Partition And Sub-partition

Mar 28, 2013

write ALTER STATEMENT for adding new partition P1 and SUBPARTITION P1_201001 and P1_201002.

TABLE
=======
CREATE TABLE TEST
(
"REPORT_ID"    NUMBER,
"MONTH_ID"      NUMBER,
"GROUP_ID"      NUMBER,
"AGE_GROUP_ID"  NUMBER,
[code]...........  

View 4 Replies View Related

SQL & PL/SQL :: Dropping A Snapshot

Apr 13, 2008

I am unable to drop a snapshot , i tried even from sys, it is giving the following error,

drop snapshot adm.dup_resource_status
9:46:29 ORA-08103: object no longer exists

but, when i try to create a new snapshot with the same name

CREATE MATERIALIZED VIEW ADM.DUP_RESOURCE_STATUS
..............
I get
ORA-12006: a materialized view with the same user.name already exists.

View 31 Replies View Related

PL/SQL :: Dropping Partitions At Once

Nov 2, 2012

ALTER TABLE table_name DROP PARTITION (partition_1000);
ALTER TABLE table_name DROP PARTITION (partition_1001);
...
.........
......
ALTER TABLE table_name DROP PARTITION(partition_1320);(b

it is a delta partition,so trying to remove 320 partitions at once in pl/sql developer for a single table.

Like this i have to remove for more then 15 tables one by one, will this effect the database like filling up the archinve log destination by writing more logs.

kind of problems that i am going to face , as i am doing it on the production box directly.

View 5 Replies View Related

Dropping A User Profile

Jun 9, 2010

Would like to know:

When you drop a user profile, Oracle automatically assigns the default profile to that user - knowing that no other profile has been assigned to that user.
Does this happen in the same session or after a restart?

A user must have a profile at all times, so if a profile is dropped, then the default profile should be assigned in the same session because if not, then during that session the user has no assigned profile which shouldn't happen?

View 2 Replies View Related

SQL & PL/SQL :: Dropping A Procedure During Execution

Apr 26, 2012

When the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .

View 5 Replies View Related







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