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.
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 ?
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.
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
I need a PL/SQL stored procedure which will accept a datafile name as parameter and dynamically create and execute "alter tablespace" command to add this passed datafile dynamically.
How to import dump into specific tablespace instead of default tablespace users.
I want to import my dump file to newly created tablespace ,so how can i do that . I have created new user called cvm and while creating it i mentioned default tablespace to newly created tablespace . But when i try to import my dumo file it goes to users tablespace .
i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?
creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for Linux: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production
My os version is
Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
My database is OLP system.
My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?
Easy to maintain when you have single tablespace. but hard to track the IO issues if you have one single tablespace.
I have one database which is recently upgraded from oracle 8.1.5 to oracle 10.2.0.4.The database is having around 300 tablespace and total size of the database is 1.5 TB.
The database was created in oracle 8i and all the teblespace were DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.
This database is a mission critical database and very less downtime can be allowed.
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.
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?
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
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.
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.
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.
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 >
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?
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'|
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'
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.
