Server Administration :: Difference Between Partition Compress?

Nov 11, 2010

I 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.

View 3 Replies


ADVERTISEMENT

Server Administration :: How To Compress Index

Oct 23, 2011

How to compress a index?

SQL> Create Table tb_compress_test
2 As
3 Select * From dba_objects;

Table created.

SQL> Create Index idx_object_id On tb_compress_test(object_id);

Index created.

SQL> Alter Index idx_object_id Move Compress;
Alter Index idx_object_id Move Compress
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

View 9 Replies View Related

Server Administration :: How To Compress Table

Sep 8, 2011

There is a huge table,i want to compress it,how can i do? alter table tb_my_table compress After executed the sql,the size of table have any change.

View 2 Replies View Related

Exadata :: Compress Sub Partition With Pctfree

Nov 12, 2012

How to compress sub partition on exadata, using 'for query high' and pctfree 10 options? I used this statement, but I get only ORA-14160:this physical attribute may not be specified for a table subpartition.

alter table table_name move subpartition subpartition_name PCTFREE 10 compress for query high;

View 9 Replies View Related

Server Administration :: Cannot Use Function In Partition-key

Aug 18, 2011

It 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

View 8 Replies View Related

Server Administration :: Difference Between V_$ And V$ Views

Oct 19, 2005

difference between V$ and V_$ views. It looks same to me. lets take the example of V$LOG and V_$LOG , both views returns same set of columns when described.

SQL> desc V$LOG
Name Null? Type
----------------------------------------- -------- -----------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE

SQL> desc V_$LOG
Name Null? Type
----------------------------------------- -------- -----------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE

View 3 Replies View Related

Server Administration :: Creating Tablespace In Raw Partition?

Aug 17, 2012

i 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]...

View 1 Replies View Related

Server Administration :: Default Partition In Table?

Oct 17, 2011

How can i check whether a partition tbale have default partition?

View 9 Replies View Related

Server Administration :: Move Partition To Cache

Feb 28, 2012

How to move a partition of a table to db_keep_cache ?

View 8 Replies View Related

Server Administration :: How To Check A Partition Have Been Compressed

Oct 20, 2011

How 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

View 5 Replies View Related

Server Administration :: How Partition Can Be Brought Offline

Jan 4, 2012

I 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?

View 23 Replies View Related

Server Administration :: Error While Exchanging Partition?

Mar 15, 2012

I 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.

View 1 Replies View Related

Server Administration :: Disable Partition Feature

Feb 2, 2012

I 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.

View 3 Replies View Related

Server Administration :: Privileges For DROP Partition

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

Server Administration :: Difference Between ORACLE_HOME And ORACLE_BASE?

Oct 3, 2011

what is

ORACLE_HOME
ORACLE_BASE

difference between ORACLE_HOME and ORACLE_BASE

View 1 Replies View Related

Server Administration :: Difference Between Table_owner And Schema?

Sep 14, 2011

a table can have many schema or just 1 schema which is the table owner?

View 5 Replies View Related

Server Administration :: Difference Between LRM And Oracle Error

Jun 26, 2011

WHAT IS THE DIFFERENCE BETWEEN ORA ERROR AND LRM ERROR?

View 9 Replies View Related

Server Administration :: Exchange Partition With Update Indexes

Nov 7, 2011

I 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

View 4 Replies View Related

Server Administration :: Get Script That Create Partition By Auto?

Oct 27, 2011

Is there a script that it create partitions by auto? who can share it?

View 1 Replies View Related

Server Administration :: Difference Between Bytes And User_bytes In Dba_data_files

Nov 27, 2012

two columns of dba_data_files (Bytes & user_bytes).

select bytes/1024/1024, maxbytes/1024/1024, user_bytes/1024/1024 from dba_data_files;
1532767.9843754.9375
249032767.984375489.9375
39032767.98437589.9375
453032767.984375529.9375
5204832767.9843752047.9375
6204832767.9843752047.9375
7204802047.9375

View 1 Replies View Related

Server Administration :: Difference Between A User And Schema On Oracle?

Apr 26, 2010

difference between a user and schema on Oracle.By difference, I do not mean what is a user and what is a schema.

I understand that if we create a user, the schema gets created by default for that user. Does that mean that we cannot create a user/schema alone.

What if I just want to create a user and grant him some read access on an existing schema or just want to create a schema.

View 7 Replies View Related

Server Administration :: Difference In SQL Results From Oracle 9i To 11gR2?

Jan 2, 2011

I've migrated from Oracle 9i to Oracle 11gR2, when i'm checking my application on Oracle 11gR2, i found that some of the query result are differed from the previous version of Oracle 9i, which is very illogical to me. I've checked the data for all related tables which are involved in one of the query. I've checked all indexes also. Still i'm not convinced that Oracle is doing anything wrong. I cannot recreate the whole scenario here, because of many tables involved in that query.

One of the sample query code.

select 'ProductMaster' producttype,e.schemecd,e.SCHEMESHORTDESC as SCHEMEDESC,to_char(e.schemefrdate,'dd/MM/yyyy')as schemefrdate, to_char(e.schemetodate,'dd/MM/yyyy') as schemetodate,e.mkid ,e.countrycd,e.statecd,e.districtcd,f.schemetype, a.productcd,a.packsizecd,a.packtypecd,a.sortorder1, a.countrycd,a.statecd,a.districtcd,a.p_uniqueid,a.mrp, (a.productcd ||'-'||b.countryname||'-'||c.statename||'-'||d.districtname|| ' -pkt.prc:'||a.pktprice ||' -mrp:'||a.mrp) as pcode
from wb.wbproductdetails a

[code]......

View 17 Replies View Related

Server Administration :: Create Subpartition For Existing List Partition

Dec 14, 2012

We 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.

View 3 Replies View Related

Server Administration :: Partition Exchange In Hash Partitioned Table

Mar 13, 2012

While 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),

View 2 Replies View Related

Server Administration :: Impact On Table Of Partition Truncate / Delete?

Oct 21, 2012

Any 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?

View 10 Replies View Related

Server Administration :: ORA-14099 / All Rows In Table Do Not Qualify For Specified Partition

Sep 1, 2011

The 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

View 12 Replies View Related

Server Administration :: Find Out Difference Of Data For Some Tables Between Current And Previous Day

May 26, 2011

We want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.

View 5 Replies View Related

Server Administration :: Splitting Table Partition Without Making Primary Key Index Unusable?

Apr 8, 2013

splitting 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

View 2 Replies View Related

SQL & PL/SQL :: Difference In Number Of Records In GROUP BY And PARTITION BY

Feb 17, 2012

If I run the following query I got 997 records by using GROUP BY.

SELECT c.ins_no, b.pd_date,a.project_id,
a.tech_no
FROM mis.tranche_balance a,
FMSRPT.fund_reporting_period b,
ods.proj_info_lookup c,
ods.institution d
WHERE a.su_date = b.pd_date
AND a.project_id = c.project_id
AND c.ins_no = d.ins_no
AND d.sif_code LIKE 'P%'
AND d.sif_code <> 'P-DA'
AND a.date_stamp >='01-JAN-2011'
AND pd_date='31-MAR-2011'
GROUP BY c.ins_no,
b.pd_date,
a.project_id,
a.tech_no;

I want to show the extra columns a.date_stamp and a.su_date in the out put so that I have used PARTITION BY in the second query but I got 1079 records.

SELECT c.ins_no, b.pd_date,a.date_stamp,a.su_date, a.project_id,
a.tech_no,
COUNT(*) OVER(PARTITION BY c.ins_no,
b.pd_date,
a.project_id,
a.tech_no)c
[code]....

why I got 1079 records.how to show the two extra columns in the out put whcich are not used in GROUP BY clause.

View 8 Replies View Related

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







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