SQL & PL/SQL :: How To Alter PCTFREE

Oct 1, 2010

I have a table Employee which gets updated and Inserted very frequently. Wanted to increase the PCTFREE value for this table

select table_name,pct_free,chain_cnt from user_tables
where table_name='EMPLOYEE'

EMPLOYEE NULL 0

is the output. I altered the pctfree like below

ALTER TABLE EMPLOYEE PCTFREE 30

and I can see Table altered but the value is not reflecting in above command again. Where else I should alter the value and cross check that value is changed.

View 3 Replies

SQL & PL/SQL :: Difference Between Alter Session And Alter System

Jun 19, 2013

What is the difference between alter session and alter system?

View 2 Replies View Related

What Influence PCTFREE On Shrink

Oct 2, 2013

What Influence PCTFREE on Shrink?.

View 1 Replies View Related

SQL & PL/SQL :: How To Change Pctfree Of Partitioned Index

Oct 3, 2011

I have a partitioned index with a pctfree of 10 .I want to change pctfree to 20.How to change?

I tried this

alter index fnsonli.IG01PK rebuild partition SYS_P245 pctfree 20 ;
alter index fnsonli.IG01PK rebuild partition SYS_P245 pctfree 20
*
ERROR at line 1:ORA-14185: incorrect physical attribute specified for this index partition

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

Syntax For Setting Pctfree And Pct Used For A Specific Table?

Apr 25, 2013

tell the syntax for setting pctfree and pct used for a specific table.

View 4 Replies View Related

Way To Alter Tablespace

Jan 22, 2013

I have issue of TABLESPACES on test instance. Tablespace files are TEMP1,TEMP2 and APPS_UNDOTS1. Initially TEMP1 and TEMP2 were of 4 GB but now the have grown to 32 GB each respectively. Resulting an occupied space of 64 GB on test server. I want to reuse that space on test instance as those tablespaces never crosses used space of 1%. Reason of this problem was my DBA set AUTOEXTEND as ON for tablespaces.

Attached File(s)

1.jpeg ( 1.8MB )
Number of downloads: 1

2.jpeg ( 1.6MB )
Number of downloads: 1

3.jpeg ( 1.52MB )
Number of downloads: 0

View 1 Replies View Related

SQL & PL/SQL :: Alter Sequence With No Max Value?

May 4, 2010

is there any way to alter only max value in sequence without specifying the max value.

i know we can alter it like :

-- Alter sequence
alter sequence TEST_SEQ
maxvalue 99999999;

can we alter it without providing the max value and let oracle choose default value for the same, same as we can do it when creating a new sequence.

View 5 Replies View Related

Alter Session On NLS_DATE_FORMAT Being Ignored

Feb 24, 2011

I'm issuing an alter session setting NLS_DATE_FORMAT in a C batch process right after the connection takes place. The format I specify is YYYYMMDDHH24MISS, this is the format used all over the process. In my development environment this works perfectly, but I've had problems in other environments.

CASE A development environment: The process works fine, $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set.

CASE B Test envirnonment 1: The process failed.$NLS_LANG=American_America.WE8ISO8859P1 $NLS_DATE_FORMAT environment variable is not set.For some reason the $NLS_LANG variable seems to have more weight than the alter session command.. why? The process works fine after setting $NLS_DATE_FORMAT to the desired format.

CASE C Test envirnonment 2: The process failed. $NLS_LANG and $NLS_DATE_FORMAT environment variables are not set. Can't get it to work here. why?

View 1 Replies View Related

SQL & PL/SQL :: Alter Partition Key Column?

Sep 13, 2012

I have a partition table which is partition on date_loaded column, can i alter the partition key column to orig_date_loaded column

View 9 Replies View Related

SQL & PL/SQL :: Grant Alter Table?

Jul 26, 2011

if a user have alter table gant but could not alter .. what additional grant it need

SQL> alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3));
alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3))
*
ERROR at line 1:
ORA-00942: table or view does not exist

View 11 Replies View Related

Alter Tablespace Rename

Sep 28, 2012

I would like to rename tablespace A to B , but when I do it tables after rename tablespace have columns tablespace_name.dba_tabkles in A . Is possibility to change it into without move ?

View 6 Replies View Related

SQL & PL/SQL :: Alter Column By Using The Query

May 20, 2010

i have a database in which some tables. Now i want to alter column by using this query.

ALTER TABLE SALE_INVO_DETAIL_COPY
MODIFY ("QTY" number(5,2))

this column properties is QTY NUMBER(5)

now i want to convert it into QTY NUMBER (5,2)

View 2 Replies View Related

SQL & PL/SQL :: DDL Trigger After Alter Table

Jul 22, 2010

I created a DDL trigger which manipulates columns of a table after an "alter table" statement.

For us this was all the time
"alter table XXX add NEW_COL varchar2(20)"
or
"alter table XXX modify NEW_COL varchar2(20)".

Unfortunatly we have now a requirement which statements "alter table XXX drop NEW_COL varchar2(20)".

My trigger works fine - you see no problem directly after statement execution but the table is then in an invalid status.

The connection will be destroyed after you tried to see the data of this table.

Also the export (creating a dump) will not work.

The problem I see is that I the database will do a "modify" on a column which has internal the status "dropped".

How I can get the information what kind of alter table statement will be executed?

Or is there any chance to select only columns from USER_TAB_COLUMNS without the "dropped" flag?

create or replace trigger TRIGGER
after alter
on SCHEMA
declare
vCharSet NLS_DATABASE_PARAMETERS.VALUE%TYPE;
begin
-- Select all columns of a table

select COLUMN_NAME, DATA_TYPE, DATA_LENGTH
from USER_TAB_COLUMNS
where TABLE_NAME = vTABLE_NAME; ...

-- Loop for all columns from our select
...
vSTATEMENT := 'alter table ' || vTABLE_NAME || ' modify ' || VOBJECTREC.COLUMN_NAME || ' ' || VOBJECTREC.DATA_TYPE || '(' || vNEW_DATA_LENGTH || ')';

--For example:

-- alter table XXX modify NEW_COL varchar2(20)

execute immediate vSTATEMENT;

exception
...
end;
/

View 11 Replies View Related

Alter Table Column And Its Contents?

May 4, 2011

I have a table with usernames and passwords. The passwords are stored in plaintext. I would like to issue an ALTER command on the password field to store a hash instead, and then repopulate those fields with an encrypted version of the plaintext passwords that were there before.

I would prefer to do this in a procedure, as I am going to perform it in a test environment first, then eventually in the production environment.

View 2 Replies View Related

Alter Sequence In Procedure Will Not Compile?

Dec 31, 2005

When I try to compile a procedure with this command:

alter sequence myschema.seqmessages increment by 100;

The error says "encountered symbol "ALTER" when expecting...

Is there another way to alter a sequence from a procedure? In this case, I am altering a sequence in another schema that has granted the alter and select privileges for the sequence.

View 7 Replies View Related

SQL & PL/SQL :: Change Password Without Using ALTER Statement?

Aug 9, 2010

Requirement: I need to create a Function to allow users to change their own password when they are logging in to an application. Also, I would prefer to not use the ALTER command.

View 12 Replies View Related

SQL & PL/SQL :: How To Alter Reference Partition (add Or Modify)

Apr 18, 2011

I have created the following partition. but i want to alter in future . how to alter the reference partition. is it avilable in reference partition.

CREATE TABLE EMD_FILE_LOAD_DETAILS_PR
(
FILE_REFERENCE_ID ,
FILE_NAME ,
FILE_TYPE ,
FILE_GROUP_NAME ,

[Code]...

View 1 Replies View Related

SQL & PL/SQL :: Update Column - Add Two 00 After Alter Table

Apr 14, 2012

I have a table PR in that some data is there for instance, My Mr_NO was Char(11) I modify MR_No to Char(13)My Table Structure now is:

MR_No Char(13),
Mr_Date Date

My Previous data is MR_NO=APN00209085

I want to add two 00 after alter the table I want my result data like APN0000209085.I am updating through this command

update PR set Substr(MR_No,4,2)='00'

ERROR at line 1: ORA-00927: missing equal sign

Result I want is APN0000209085

View 13 Replies View Related

SQL & PL/SQL :: Alter Queue Table Column?

Mar 23, 2011

I need to Modify the column(MSGID) data type from RAW to BLOB for a Queue Table, I'm getting the following error.

BANNER
---------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE11.2.0.2.0Production

YUV >

YUV > DESC PDA_REPORT_MESSAGE_QTAB
Name Null?Type
----------------------------------------------------- -------- ------------------------------------
Q_NAME VARCHAR2(30)
MSGID NOT NULL RAW(16)

YUV >
YUV >
YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB);
ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB)
*
ERROR at line 1:
ORA-22858: invalid alteration of datatype

YUV >
YUV >
YUV >
YUV >
YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB);
ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB)
*
ERROR at line 1:
ORA-24005: Inappropriate utilities used to perform DDL on AQ table PDADBA.PDA_REPORT_MESSAGE_QTAB
YUV >

View 10 Replies View Related

SQL & PL/SQL :: Alter Session Number Format?

Dec 20, 2010

how to change the default format of a number value using alter session statement?

I've a problem when I show a value like this "0.123456": the select statement returns ".123456". Is there any way to force a zero value before the character separator?

View 11 Replies View Related

PL/SQL :: Alter Session Query On Linux?

Nov 1, 2013

I was trying to execute below query inside a sql script which is called from a shell script on linux environment. EXECUTE IMMEDIATE 'alter session set events ''10176 trace name context forever'' ';

This thing works at our test environment. But giving error "SP2-0670: Internal number conversion failed" when tried on live(production) environment. What could be the reason? I found another way of writing such queries on net as follows, will it be useful? EXECUTE IMMEDIATE q'|alter session set events '10176 trace name context forever'|

View 1 Replies View Related

How To Monitor ALTER TABLESPACE Ddl Change

Jan 8, 2013

I can use trigger to monitor ALTER TABLESPACE ddl statement against a particular tablespace in a schema.

CREATE OR REPLACE TRIGGER HOT_MAIL_DDL_CHANGE
AFTER ALTER TABLESPACE <tbs_name>
ON SHANNURA.SCHEMA
BEGIN
INSERT INTO HOT_MAIL_DDL_AUDIT_LOG VALUES
(SYSDATE,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
ORA_SYSEVENT,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME
);
END;
/

I think, line no.2 isn't a valid statement. Or can I use AUDIT instead - in fact, I want to monitor/audit only ALTER TABLESPACE ddl statement and that too for a particular tablespace only.

View 3 Replies View Related

PL/SQL :: Alter Session In Select Statement?

Oct 15, 2012

I'm using Oracle SQL Developer and querying on date fields. I use this "alter session" statement to set my default date time format to get my datetime values. My question is, is there a way I can incorporate the following statement into my "select" query so that Crystal can read it? I use the statement below before my select statement to query datetime differences. Problem is, Crystal doesn't like the statement below.

Using TO_CHAR( ) and formatting my date fields that way doesn't work when I have to take the difference between date fields to the minute/second level. That's why I use the alter session command below, but I cannot seem to incorporate it with my select statement as a Crystal data source.

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'

View 14 Replies View Related

Server Administration :: Alter Tablespace

Jan 4, 2011

My tablespace contains two datafiles dfile1.dbf and dfile2.dbf on D drive of my filesystem. Now i copied these dbf files on pasted it on a location in E drive. Now i want my tablespace to use dbf files pasted in new location.

Will i have to Alter Tablespace Add Datafile with Reuse clause

View 2 Replies View Related

Alter Existing Users For Default Tablespace?

Mar 5, 2012

My user tablespace has been full and just I create new tablespace like User_Tablespaces.

I want to Alter Default Tablespace and Quota define of all existing user on User_Tablespaces.

How we do in a SQL Query or PL/SQL query that can all existing users has been alter at a time in a single query.

View 2 Replies View Related

Alter Table Fails - Ran Out Of Undo Space?

Jun 11, 2009

I want to alter a very large table.

ALTER TABLE MYTABLE ADD
(
ENTRY_TSTMP DATE DEFAULT SYSDATE NOT NULL
)

My table is very large and I am getting an error saying I am out of undo space.

The dba says the undo space is as big as the table.

View 1 Replies View Related

Extra Space In File_location For Alter Command

Nov 7, 2010

I do below alter command

alter tablespace RCA_MB_IDX add datafile '/gxsdb/database/oradata5/RCA/RCA_REF_MB_IDX_01.dbf ' size 1024M

But there is extra space in the file location '/gxsdb/database/oradata5/RCA/RCA_REF_MB_IDX_01.dbfX'

I mark X for that space ...

The command is commit .

But there is problem when do backup .

View 2 Replies View Related

Error Call A Function Which Alter Sequence Value

Jun 22, 2011

error call a function which alter sequence value..I created a function to reset a sequence value as below

CREATE OR REPLACE FUNCTION rebuildSequence
return number
As
l_csmsgid1 PLS_INTEGER;
l_csmsgid2 PLS_INTEGER;
l_val PLS_INTEGER;
plsql_block VARCHAR2(500);
[code]....

I verified and executed the pl/sql block without a problem. but when only put into a fuction and call it, got then error.

View 7 Replies View Related

Server Administration :: Alter SGA But It Give Error?

Dec 9, 2010

I try to alter the SGA it give me error The Steps which I do show sga

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

[code]...

View 5 Replies View Related