COMPRESS Option Along With CREATE TABLE
Sep 26, 2013I am trying out the COMPRESS option along with CREATE TABLE. just wanted to understand if we need the "Advanced Compression" option enabled. Does this require extra license?
View 2 Replies
ADVERTISEMENT
Server Administration :: How To Compress Table
Sep 8, 2011There 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 RelatedCompress Existing Table Which Is Not Partitioned
Aug 1, 2013Im having table which is of 45M rows table [Not partitioned], Now I want to compress the old data other than last 3Months data, I should not go for partition compress. Rarely some select queries will be fired on that Old data. Now how can I compress that table without affecting the Indexes , Dependencies proc, pkgs, Functions.
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - Production"CORE 11.2.0.3.0 Production"
PL/SQL :: How To Compress Multiple Files And Store In Table
Apr 9, 2013I am having many different files and i want to use pure plsql to get these files stored as blob from a table then compress them into 1 files and store that into another table. I did some search and its like possible but didnt get conclusive solution
View 4 Replies View RelatedPossible To Compress Existing Table In Oracle Database
Nov 29, 2012it's possible to compress the existing table in oracle database directly.
View 1 Replies View RelatedSQL & PL/SQL :: Create Materialized Views Depending On Sysdate With Enable Query Rewrite Option
Mar 28, 2011I want to create a materialized view for the last 10 days with the enable query rewrite option.
e.g. i want to create a view with the list of employees who joined the company in the last 10 days.
create materialized view M_Employee
refresh fast on commit
enable query rewrite as
select joining_date , name
from
employee
where joining_datde < TRUNC(sysdate) - 10
I seem to get the error SQL Error: ORA-30353: expression not supported for query rewrite
30353. 00000 - "expression not supported for query rewrite"
*Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE.
This error is self explanatory , but is there any work around to have a query liek this to list the employees based on sysdate.
SQL & PL/SQL :: Option For Temp Table?
Feb 18, 2010What is the Best option for SQL/sybase server Temp table to use/for data manipulation (Insert/update/delete) inside the procedure in oracle Other then global temporary table.
Since we are porting from SQL/Sybase to Oracle we don't want to Create too many global temporary table.
SQL & PL/SQL :: Creating Table - Missing Or Invalid Option Error
Jun 10, 2011I am getting error while trying to create a table
SQL>
SQL> CREATE OR REPLACE TABLE CEE_OSPCM_DETAILS
2 (
3 ORD_NBR VARCHAR2(10) NOT NULL,
4 ORD_APPNDX NUMBER(2) NOT NULL,
5 FRCOI_NBR NUMBER,
6 PRINT_NBR VARCHAR2(4),
[code]....
SQL & PL/SQL :: ORA-01735 / Invalid ALTER TABLE Option ORA-06512
May 12, 2011I ran the following PL/SQL Code & I am getting the following Error:
Drop table MODIFIEDTABLE
/
Commit
/
create table MODIFIEDTABLE(TABNAMES varchar2(100))
/
DECLARE
[code].....
RESULT:
Table dropped.
Commit complete.
Table created.
DECLARE
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
ORA-06512: at line 12
SQL & PL/SQL :: Creating Partition Table - Missing Or Invalid Option
Aug 4, 2011I got a error while creating a partition table
I did following steps
SQL> show user
USER is "ROSE"
SQL>
SQL> create table sales (year number(4),
2 product varchar2(10),amt number(10,2))
3 partition by range (year)
[code]....
ORA-00922: missing or invalid option
SQL>
SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
[code]....
Server Utilities :: SQL Loader - For INSERT Option Table Must Be Empty
Mar 16, 2011l am loading data i.e text file of huge size into oracle 9i db using sqlldr through command prompt, but my table contains already some data so while loading it shows error as
SQL*Loader-601: For INSERT option, table must be empty.
i cant truncate my table since present data is important.
Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table
Apr 29, 2011What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?
Is CREATE EXTERNAL TABLE included in CREATE TABLE?
Server Administration :: How To Compress Index
Oct 23, 2011How 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
SQL & PL/SQL :: Compress Already Existing - Partitioned Index
Jul 5, 2012Today I found myself in a situation where I needed to compress an already existing, partitioned index. First I issued an alter table to rebuild and compress the whole index:
SQL> ALTER INDEX MY_INDEX REBUILD COMPRESS;
ERROR at line 1:ORA-14086: a partitioned index may not be rebuilt as a wholeSo next I tried to rebuild compress one of the partitions:
SQL> ALTER INDEX MY_INDEX REBUILD PARTITION PART1 COMPRESS;
ERROR at line 1:ORA-28659: COMPRESS must be specified at object level first
Turns out that there is no way you can do a rebuild compress on partitioned indexes. I did not realize that until I stumbled across document 312843.1 on Metalink. It says the only way to compress a partitioned index is to drop it and recreate it (through 11.2).
My question is do we have any new feature in Oracle 11gR2 to rebuild partition indexes?
Exadata :: Compress Sub Partition With Pctfree
Nov 12, 2012How 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;
Server Administration :: Difference Between Partition Compress?
Nov 11, 2010I 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.
Secure Backup :: Compress Existing Backup
Oct 4, 2012I have a lot of uncompressed backup. How can I compress it?
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
304 B 0 A DISK 01-OCT-12 1 1 NO BACKUP_DPCTRDB_000_100112093432
305 B 0 A DISK 01-OCT-12 1 1 NO BACKUP_DPCTRDB_000_100112093432
306 B 0 A DISK 01-OCT-12 1 1 NO BACKUP_DPCTRDB_000_100112093432
308 B 0 A DISK 01-OCT-12 1 1 NO BACKUP_DPCTRDB_000_100112093432
309 B 0 A DISK 01-OCT-12 1 1 NO BACKUP_DPCTRDB_000_100112093432
[Code]....
SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable
Aug 30, 2011I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......
SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child
Aug 13, 2012We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
SQL & PL/SQL :: Command Is Used To Create Table By Copying Structure Of Another Table Including Constraints?
Jul 14, 2012what command is used to create a table by copying the structure of another table including constraints ?
View 2 Replies View RelatedCreate Temp Table From Existing Table
Sep 2, 2010I want to create temp table, for this i am using:
CODEcreate global temporary table help_temp
as
select * from help;
but this is creating only the table structure, not copying the table data.
SQL & PL/SQL :: Create Table By Using Another Table With Specific Condition?
Jan 25, 2013can we create table with copying of another table with some specific condition.
example.suppose we have one table which name is emp with three columns.
empid
empname
empjoindate
i want create a table emptemp by using emp table where empjoindate between two dates.
Create A Table Which Contain History Of Main Table
Sep 8, 2008I have to create a table which contain history of a main table. like this:
if the main table is
========================
nametypelengthnot null
Avarchar5Y
Bvarchar5N
Cvarchar5N
Dvarchar5N
========================
[code]....
I've plan to so this by create a trigger in main_table. my problem is my main table have a lot of fields and I can't write a code to control it 1 by 1 like :
if old.A <> new.a
insert into history("A",old.A,new.a)
if old.B <> new.B
insert into history("B",old.b,new.b)
......
I decided to select column name from the data dictonary using this SQL:
SELECT column_name FROM user_tab_columns WHERE table_name = '<<Table Name>>';
and then do a loop over the resultset and use the column name I've got , like this (its just an idea, may be not a write syntax):
BEGIN
.....
FOR i IN 1..:result.COUNT LOOP
if ld.colname[i] <> :new.colname[i]
INSERT INTO history
VALUES ( colname[i], ld.colname[i], :new.colname[i]);
END LOOP;
END;
but I can't write a "old.colname". I try with " old.'colname' ", " ld.'colname' " but it won't work.how to create a history file like I've describe.
Forms :: How To Get Browse Option
Nov 1, 2013I want browse option in oracle forms.
View 3 Replies View RelatedSQL & PL/SQL :: Returning Values With MORE Option
Jan 19, 2012Imagine I have the following scenario:
CREATE TABLE AAA_PLACE
(COD_PLACE NUMBER,
PLACE_NAME VARCHAR2(50 BYTE)
)
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (1, 'Munich');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (2, 'Lisbon');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (3, 'Monaco');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (4, 'Madrid');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (5, 'Milan');
Imagine I want to retrieve those records in only 3 distinct rows, like this:
Munich
Madrid
Others
Is there any way I can do this with an simple Statement?
SQL & PL/SQL :: Multiple Filter Option
Apr 2, 2013in many shopping cart applications they are providing multiple filter option.
suppose filter by brand,fitler by price ranges,filter by color,filter by shape etc..
so how they are achieving the performance.because lot of filter if applied then it should get slow execution.
how to achieve this.I tried as follows.
alter session set nls_date_format = 'dd-MON-yy';
CREATE OR replace PROCEDURE Multiple_filter (p_empno VARCHAR2,
p_ename VARCHAR2,
p_from_hiredate DATE,
p_to_hiredate DATE)
[Code]....
SQL & PL/SQL :: Set MV As Incremental Refresh Option?
Aug 19, 2011i already created metalized view
now i want to set MV as incremental refresh option?
is it possible to set this attribute after mv created..?
Use Split Partition Option?
Jan 28, 2013I am using 11.2.0.2 database.
I got ORA-14074 error says that if you have a MAXVALUE already you will need to either drop the partition that encompasses the MAXVALUE or split partition.So I want to use split partition option through the below statement,
alter table
tablename
split partition
partmax at (XXX)
into
(partition partXXX, partition partmax);
I am concerned about the existing data in the table will it by any change gets deleted, and what about indexes, do I have to rebuild, etc.I have to do this on produciton. I cannot test this as I don't have a test environment and moreover the table is having 70 million records and we don't have time/hardware resources to recreate this by export import in another database.
Parallel Query Option
Oct 23, 2013Just a general query on parallel query. My customer having 4 cpus and running the database in 11.2.0.3 in AIX 5.3(One is in AIX 6.1). Under which circumstances, we can propose to user parallel query options.
View 2 Replies View RelatedLoadjava Command With Encoding Option
Jan 5, 2011I want to know what syntax I should use for encoding option with loadjava command?
here is the scenario--
Our oracle database is already compatible with UTF-8 characters i.e. charset encoding is set as 'AL32UTF8'. I am able to save Chinese characters in the database however when I trigger a stored procedure which eventually use a loaded java class I get an error.
I am suggested to provide -encoding option while running 'loadjava' command but I don't know what syntax I should provide for 'UTF8' encoding.