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


ADVERTISEMENT

Server Administration :: ORA-39726 / Unsupported Add / Drop Column Operation On Compressed Tables

Jan 11, 2012

my user is trying to drop columns, but she gets below error:

SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables

39726. 00000 - "unsupported add/drop column operation on compressed tables"

i just checked whether table is compressed or not, it is not compressed it seems:

select owner, table_name,COMPRESSION,COMPRESS_FOR from dba_tables
2 where owner = 'EQUIPMENT' AND TABLE_NAME = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
EQUIPMENT ETHRNT_VRTL_CNXN_HRLY_AGG_SWP DISABLED

1 row selected. i am able to set one column as UNUSED. and then i am able to see the count accordingly from below view:

select * from DBA_UNUSED_COL_TABS
where owner ='EQUIPMENT' and table_name = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';

but not able to drop the unused columns. if i tried to drop a column directly from the table, that also giving above error.

View 7 Replies View Related

Server Administration :: Server Hang Very Often / How To Check Reason For Hanging

Mar 14, 2012

We a running oracle9i database and forms 6i application.

We have 4 gb ram size and sized the sga for 700mb pga for 400mb for past 4 months there is no problem.

Now from last week we encounter the server hang, and we do restart. How to check reason for hanging.what are all the check list to carried out.

View 19 Replies View Related

Server Administration :: How To Check ASM Is Used

Jul 8, 2010

How can I check whether the ASM is used in the environment? how I can check for it and if being used, how to connect to that instance later on.

View 5 Replies View Related

Server Administration :: How To Check Alertlog File

Mar 3, 2012

you have some 500 Db how will u check the alertlog file. do u do it manually or is their any tool to check the alertlog files. how to check it.

View 7 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 :: How To Check Whether Database Is Dedicated Or Shared

Feb 3, 2012

How to check whether database is dedicated or shared ?

View 1 Replies View Related

Server Administration :: Quick Check On Hidden Parameter?

Mar 20, 2013

quick check on a hidden parameter? I need to know the default value and possible values for _serial_direct_read on various releases, I have only 11.2.0.3 available right now and I'ld like to know this for 11.2.x.x 11.1.x.x, and 10.2.x.x.

Below is the query that will show what it is currently which (unless you've changed it) will be the default: auto for my 11.2.0.3. Then to see the options, try to set it to something wrong, my options are false/true/never/auto/always. I think false/true were the only choices for 10.2 and are maintained only for compatibility. But I can't rememebnr 11.1.

orcl>
orcl> set lin 80
orcl> select KSPPSTVL from x$ksppcv join x$ksppi using (indx) where ksppinm='_serial_direct_read';
KSPPSTVL
--------------------------------------------------------------------------------
auto

orcl>
orcl> alter system set "_serial_direct_read"=rubbish;
alter system set "_serial_direct_read"=rubbish
*
ERROR at line 1: ORA-00096: invalid value RUBBISH for parameter _serial_direct_read, must be from among false, true, never, auto, always

orcl>
update: added 11.1.x.x to my wishlist

View 10 Replies View Related

Server Administration :: How To Check Oracle Database Uses Java Or Not

Jul 23, 2010

Actually in our environment , sysadmins are upgrading java versions on Unix (HP & Solaris) Machines and they have asked the query whether oracle database uses Java or not?

If it uses then all the databases need to be shut down for their planned activities and if not no need to shutdown the databases.

View 17 Replies View Related

Server Administration :: How To Check Error ORA-01555 / Snapshot Too Old

Dec 23, 2011

i am getting this error during expdp ORA-01555 -snapshot too old

1. i increased undo_retention i.e 50000

2. i execute expdp during late night when almost zero user is online so no commit from any other session.

3. we have a table having BLOB data type field in a table which is 500 GB in size.complete DB size is 700 GB

where and why this error exists.if corruption of BLOB so how to check that etc. the same error is occurring in three of our data centres during expdp.

View 4 Replies View Related

Server Administration :: Check Tables Located In Which Datafiles?

Aug 6, 2010

would want to check which tables located in which datafiles.

Tried with this command:
select B.FILE_NAME from dba_segments a, dba_data_files b
where a.header_file=b.file_ID and segment_name='TABLE_NAME'

Seems like it only shows 1 datafile. Some tables are large and i suppose it's resided in more than 1 datafile.

View 1 Replies View Related

Server Administration :: Check Free Extents In Tablespace

Apr 29, 2012

which data dictionary view would you query to see the free extents in a tablespace.

i use dba_free_space;

SQL> select tablespace_name,sum(bytes),sum(blocks) from dba_free_space where
tablespace_name = 'SYSTEM' GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME SUM(BYTES) SUM(BLOCKS)
------------------------------ ---------- -----------
SYSTEM 9830400 1200

View 2 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 :: 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 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 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 :: Check Status Of Listener And Read Only From DB Query?

Jun 13, 2010

I need to display the parameter and status of DB for listener and Read Only.

I know those value could be get from command line , but could we get the values of Listener and Read only by SQL/PlSQL? So I can get it through the query of DB.

View 8 Replies View Related

Server Administration :: How To Check Last Modification Date Of Resource_name In Profile

Feb 9, 2010

we have a few profiles in dba_profile. How to check what is the last modification date of the the resources inside each profile.

View 6 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 Utilities :: Export Schema Which Contains Space More Than 100 GB Using Compressed Mode?

Jul 13, 2010

I need to export one schema which contains of huge space of 100GB in oracle 9i ,can u pls provide the command to export in the compress mode.

View 3 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

Performance Tuning :: Check Count For Each Partition?

May 2, 2012

We have 525 partitions and we want find out partition wise total count. finding partitionwise count in a particular table.

View 12 Replies View Related







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