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.
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
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 RelatedHow 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;
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
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
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]...
How can i check whether a partition tbale have default partition?
View 9 Replies View RelatedHow to move a partition of a table to db_keep_cache ?
View 8 Replies View RelatedHow 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
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?
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.
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.
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.
what is
ORACLE_HOME
ORACLE_BASE
difference between ORACLE_HOME and ORACLE_BASE
a table can have many schema or just 1 schema which is the table owner?
View 5 Replies View RelatedWHAT IS THE DIFFERENCE BETWEEN ORA ERROR AND LRM ERROR?
View 9 Replies View RelatedI 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
Is there a script that it create partitions by auto? who can share it?
View 1 Replies View Relatedtwo 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
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.
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]......
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.
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),
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?
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
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 Relatedsplitting 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
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.
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
