whether Oracle has any capability of automatically checking which lossless compression algorithm it should apply by analyzing a data stream on data load? Does Oracle have any compression advisors/wizards that would make recommendations as to type and level of compression?
View 3 Replies
I have a data like,
1) manual_temp_master
auto_idbatch_id sec_idsec_id_type crrncy_cdcreate_Dt price_dt
------------------------------------------------------------------
11234ABC1CUSIPUSD14/05/201014/05/2010
23456XYZ1SEDOLGBP13/05/201013/05/2010
2)manual_temp_detl
auto_idbatch_id Price_bkt_cdscreate_Dtprice_date
---------------------------------------------------------
11234PS114/05/201014/05/2010
11234PS214/05/201014/05/2010
11234PS314/05/201014/05/2010
11234PS414/05/201014/05/2010
[code]....
I want to write a sql query which will fetch the data from manual_temp_master and manual_temp_detl.But from manual_temp_detl table, Price_bkt_cds columns should be displayed as columns. Like the should look like as below:
sec_idsec_id_type crrncy_cd COL_PS1 COL_PS2 COL_PS3 COL_PS4 COL_PS5COL_PS6price_date
--------------------------------------------------------------------------------------
ABC1CUSIPUSDPS1PS2PS3PS4PS5PS614/05/2010
XYZ1SEDOLGBPPS1PS2PS3PS4PS5PS613/05/2010
Is there any way to compress data while export in ORACLE 10g or if there is any other way I can reduce the space consumed by the datafiles.
View 10 Replies View Related1)i have 2 SWP TABLES. while dropping a column, i am getting error -
ORA-39726: unsupported add/drop column operation on compressed tables.
2) when i checked compression status, those were not compressed. But as per our code standard, SWP tables should not be in compress mode.
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR PAYMENT_SWP DISABLED
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR PREPAYMENT_SWP DISABLED
3) as a workaround, i compressed these 2 SWP tables with OLTP option, and then i was able to drop the column from these 2 SWP tables.
4) Below statement is correct or not ?
IF A TABLE USING BLOCK LEVEL COMPRESSION, THEN this error will come - ORA-39726: unsupported add/drop column operation on compressed tables.
if above statement is correct, then how to find out whether table data is using block level compression ?
5) we have DBMS_COMPRESSION.GET_COMPRESSION_TYPE. using this i just tried to find out, but i am getting "1" as output. I am not getting the exact meaning of it.
confirm what is the conclusion on this ?
SQL> declare
rid rowid;
n number;
begin
select max(rowid) into rid from NOVAR.PAYMENT_SWP;
n := dbms_compression.get_compression_type('NOVAR','PAYMENT_SWP',rid);
dbms_output.put_line(n);
end;
/
2 3 4 5 6 7 8 9 1
PL/SQL procedure successfully completed.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> /
1
PL/SQL procedure successfully completed.
SQL> SELECT max(rowid) from NOVAR.PAYMENT_SWP;
MAX(ROWID)
------------------
AAsz4fAHSAAAD3IABs
(ii) 2nd table
SQL> set serveroutput on
SQL> declare
rid rowid;
n number;
begin
select max(rowid) into rid from NOVAR.PREPAYMENT_SWP;
n := dbms_compression.get_compression_type('NOVAR','PREPAYMENT_SWP',rid);
dbms_output.put_line(n);
end;
2 3 4 5 6 7 8 9
10 /
1
PL/SQL procedure successfully completed.
SQL> SELECT max(rowid) from NOVAR.INVOICELINE_SWP;
MAX(ROWID)
------------------
AAsz4ZAEkAAAp8XAAA
We have a requirement from the customer to start using data and index compression in our 11g database.. Is this something available in Oracle 10g,11g without any additional costs? We are not sure if this will work with our application so we will have to test it in-house, is it possible to compress the existing table data/index to test it out?
View 3 Replies View RelatedI want to know whether is it possible to send CLOB data automatically using CDC...i usually have to run a procedure manually to send such data to remote locations.
View 1 Replies View Relatedin a RAC multi-node environment with single instance dataguard..Recovery is not running automatically I have to re-run the recovery command to make them sync and this command works for already archived log files no upcoming archive log file applied I have to re-run the recovery command again for newly archived log files.
View 2 Replies View RelatedI have created a MV on Oracle 10gR2 using dblink. Source database is AS400 DB2.MV script:
CREATE MATERIALIZED VIEW MV_BU
TABLESPACE TB_XXX
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
[code]...
Above query should be refreshing the data every minute but I realized that its not doing it.
i have create database link with msaccess2000 its working fine in forms 6i i am trying to upload data by using forms 6i while compiling form is automatically closed my code is this
begin
insert into baba1
(select *
from baba1@access_db
where dte between to_date(:block3.fdate,'dd-mon-yyyy')
and to_date(:block3.ldate,'dd-mon-yyyy')
[code]....
I have a requirement where i have data spread in rows which i have to pivot kind off operation to find the time taken using a Log table.
below is the existing data is present.
col0 col1 col2 col3 col4
30003695/1/2013 8:41Batch Startedproc_unalloc1345
30003705/1/2013 8:41blah proc_unalloc1345
30003715/1/2013 8:46blah proc_unalloc1345
30003725/1/2013 8:46blah proc_unalloc1345
30003735/1/2013 8:56blah proc_unalloc1345
30003745/1/2013 8:57Unalloc Endedproc_unalloc1345
[code].....
Below is what I am trying to find from the above data.
col1Unalloc Alloc Metric
13450.16 1.07 1.3
the result is Hours.Minutes.
I am trying to minus the two dates and find the time taken for a particular section( eg, unalloc ).
I tried using three union queries .
Select block,timestamp From (
Select 'Unalloc' As Block ,col1,col4 From table_1
Where col2 = 'Batch Started' And col3 = 'proc_unalloc' And
Parms Like '1345'
Union
Select 'Unalloc' As Block ,col1,col4 From table_1
Where col2 = 'Unalloc Ended' And col3 = 'proc_unalloc' And
[code].....
I will minus the start and end time for unalloc to get time taken for un alloc. Then i will minus end time of unalloc and end time for alloc to get time taken for alloc... like wise for metric.
Is it possible that we restrict user at data level? For Example 'A' user can only query employeess of deptno 10 only. He can not query employees of others dept.
View 6 Replies View RelatedWe are migrating database from HP to AIX. Data is all on SAN.I can do it through RMAN convert. What I want to know is if it is possible to move data at storage level without using Convert command?
View 2 Replies View RelatedOur client is having requirements that data should be encrypted through-out the process (from transaction level to backup level).
Requirements are as below -
1) Data should be encrypted and can be access only through application (through front end).
2) Even if pl/sql, sql installed, developer user cannot able to fetch data from database.
3) Even DBA, should not have access to fetch the data from the database.
4) Only assigned DBA, should fetch details from database (provided he/she should have details of database as well as application's user / password).
5) It should be restricted by ip and user details (such as ip of application server with user/password of user).
6) Changes should be purely done from database level only.
How can find the number of standby databases configured for primary database from os level
View 9 Replies View Relatedwhen a user change or delete any record or row in forms data automatically move to other table because i want to compare old and new record.
View 8 Replies View Relatedcreate or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS
CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;
Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?
When I used to work with foxpro we had used dot matrix printer. At that time if we want, we can print in normal mode, expanded mode and compressed mode. it will be part of your spool file.
Now am printing a text file from forms using text_io package. I would like to know in this how do i print the content in compressed mode using CHR function or some other method.
what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.
View 1 Replies View Relatedwhat 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.
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
I am able to duplicate (from active database) 12.1.0.1 database to destination server successfully,however when I add either compressed backupset and/or section size to duplicate commandrecovery at the end of the operation fails due to missing archive log (which is at source server)
******************************************failed duplication****************************************** [oracle@r121 ~]$ rman target sys/sys_pwd@t12 auxiliary sys/sys_pwd@c12Recovery Manager: Release 12.1.0.1.0 - Production on Fri Sep 6 11:30:09 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: T12 (DBID=1222223202)connected to auxiliary database: T12 (not mounted) RMAN> duplicate target database to c12 from active databasesection size 2gspfileparameter_value_convert 't12', 'c12'set db_file_name_convert 't12', 'c12'set log_file_name_convert 't12', 'c12'nofilenamecheck;2> 3> 4> 5> 6> 7>Starting Duplicate Db at 06-SEP-13using target
[code]...
I am getting ORA-39726 error when a dropping a column on a non-compressed table.
SQL> select distinct TABLE_OWNER,COMPRESSION,COMPRESS_FOR from dba_tab_partitions where TABLE_NAME='STM_AE_STMT_HISTORY_DETAIL';
TABLE_OWNER COMPRESSION COMPRESS_FOR
------------------- ---------------------- -------------------
APP_SU DISABLED
SQL> ALTER TABLE APP_SU.STM_AE_STMT_HISTORY_DETAIL DROP COLUMN RATE_DESCR;
ALTER TABLE APP_SU.STM_AE_STMT_HISTORY_DETAIL DROP COLUMN RATE_DESCR
*
ERROR at line 1: ORA-39726: unsupported add/drop column operation on compressed tables
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 Relatedmy 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.
I have this table :
column1 column2
--------- ---------
value1 value2
value1 value3
value2 value4
value3 value7
value7 value1
value8 value9
What I was trying to retrieve is something like that:
Quote:
output_column
---------------
value1, value2, value3, value4, value7
value8, value9
I don´t care about the order of the values in the row. In other words, I want to get disjoint sets of data connected by any of both values.Every pair in the input table is unique.
I have seen in the web that it is possible to do using connect by and hierarchical retrieving but I've been trying to make a lot of combinationts and I can reproduce the output.
We have been recommended to store data in CLOD data type.
Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.
We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?
i have configured physical standby in my local system, to check logshipping i created a table at primary db, wen i tried to check in standby, it says table does not exist..below are primary & standby alert entries..
Primary alert log
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.98)(PORT=1522))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STAND)(SERVER=dedicat ed)(CID=(PROGRAM=d:oracle11gappadministratorproduct11.1.0db_1inORACLE.EXE)(HOST=A960M)(USER=SYSTEM))(SERVER=dedicated)))
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
[code]....
I have few tables in Oracle 9i/10g , and they already have data in them. I am trying to migrate the data coming from various source systems into these Oracle tables. There is a chance that after loading I might get some unwanted data into these tables.
How do I remove just that data which I have loaded recently, and do not disturb the original data it already has.
Need to backup those tables and reload the data back if there is any problem, but I am looking at a different approach. I just don't want to change the existing system, as lot of users use the system.
I have database A (Working in Live environment) and Database B copy of Database (Not live) I have Restored whole database (A) RMAN backup file on Database (B) Previous week now i don't want to change anything in any schema and want to import only updated and new records in the table in Database B
There are around 20 schema If for example i have everything in new database B all required database objects like Procedure,functions, packages with indexes in all tables and data in tables, i just want to add new data and updated data.
IF i do following in source database
expdp directory=dpump_dir dumpfile=table_data.dmp content=data_only schemas=ACCMAIN,HRMAIN,..... include=TABLE
AND Import in destination database B, will it add new data and update existing one in table and not touch the table structure and indexes.
I want to change dbname,sid,data/control file locations in operational dataguard setup i plan to follow as below
1)shutdown primary and standby (stop managed recovery)
2)change db name in init.ora of primary and standby change database name control file location
3)create control file for primary from trace(script) make changes for db name and file locations
4)mount and open primary database
5)create standby control file
6) transfer standby control file to standby
7) mount stand by database and start manage recovery
If this steps will error free do i need to follow any thing additional to this or what is other best way for this or its not possible at all
