Change Start With Value On A Sequence Without Dropping And Recreating It?
Jul 26, 2010Is it possible to change the start with value on a sequence without dropping and recreating it?
View 3 Replies
ADVERTISEMENT
Dropping Users And Recreating?
Jun 18, 2012we are refreshing data base for our application from the base load,Every time when ever there is refresh required we need to drop the users and recreate every thing from the base load. For this we need to kill the sessions and drop the users ,Putting the instance in the restricted mode and refreshing the db.Some time when ever During the killing and dropping process there are some errors like you can not drop the users which is currently connected .
set termout on
set echo on
spool Kill_sessions_drop_users.log
DECLARE
[code]...
how to get rid of this
SQL & PL/SQL :: Modify Supertype Without Dropping / Recreating Its Subtypes?
Oct 2, 2013is there a way to modify supertypes without dropping / recreating its subtypes?
View 6 Replies View RelatedSQL & PL/SQL :: Sequence Next Value Change?
Jul 25, 2011We Require to Change the Sequence Next Value.
If it possible to Update the Sequence Next Value in Oracle.
SQL & PL/SQL :: Recreating Existing Trigger
Nov 28, 2011I am trying to update an existing trigger in my Prod DB, but it may be possible that while recreating the trigger,existing trigger might be under any transaction.
So what could be the possible scenarios? Will I be throwing any error like ORA-0054: resource busy or any other error or what? If it does what could be possible escape ways apart from downtime.
Data Guard :: Recreating Physical Standby
Jun 4, 2010I have an issue whereby our standby instance was down for a couple of days and when it came back online we found an archivelog file was missing, so recovery only happened up to a point. It appears that the archivelog was deleted therefore we are unable to continue with this standby. We are now planning to rebuild the standby database. As far as i know all of the steps originally performed need not be performed to complete this task.
1. Shutdown standby
2. Delete all datafiles & redo logs
3. Copy over datafiles to respective directories
4. Copy online logs and archivelogs to standby.
5. Create a control file for standby database
6. Startup mount standby database
7. start redo apply
Installation :: Fail To Start OCR And OS / Error When Start OracleCSService
Jun 5, 2012I'm trying to install Oracle 10g Enterprise Ed. (on Windows 2003 Std. Ed) and I get some errors:
First “Fail to start OCR” I press "continue" and the installation continue. Next “Operative System Error when start OracleCSService” and when I press "continue" the installation finish.
At the end of installation, It seems like Oracle DB is Ok, but not really...I can't connect to database from Enterprise Manager and the OracleCSService is in "Starting mode".
Forms :: How To Change Forms Trigger Sequence
Feb 11, 2010is it possible to change the forms trigger sequence.
for example, i am writing 'when_validate_item' in item level,block level,form level.First item level will fires and then block level and at last form level. But i want to fire the trigger first at form level then block level and at last item level.
SQL & PL/SQL :: Dropping A Snapshot
Apr 13, 2008I am unable to drop a snapshot , i tried even from sys, it is giving the following error,
drop snapshot adm.dup_resource_status
9:46:29 ORA-08103: object no longer exists
but, when i try to create a new snapshot with the same name
CREATE MATERIALIZED VIEW ADM.DUP_RESOURCE_STATUS
..............
I get
ORA-12006: a materialized view with the same user.name already exists.
PL/SQL :: Dropping A Partition?
Jul 7, 2012Do i have to create indexes again if i drop a partition on a table?
View 6 Replies View RelatedPL/SQL :: Dropping Partitions At Once
Nov 2, 2012ALTER TABLE table_name DROP PARTITION (partition_1000);
ALTER TABLE table_name DROP PARTITION (partition_1001);
...
.........
......
ALTER TABLE table_name DROP PARTITION(partition_1320);(b
it is a delta partition,so trying to remove 320 partitions at once in pl/sql developer for a single table.
Like this i have to remove for more then 15 tables one by one, will this effect the database like filling up the archinve log destination by writing more logs.
kind of problems that i am going to face , as i am doing it on the production box directly.
Dropping A User Profile
Jun 9, 2010Would like to know:
When you drop a user profile, Oracle automatically assigns the default profile to that user - knowing that no other profile has been assigned to that user.
Does this happen in the same session or after a restart?
A user must have a profile at all times, so if a profile is dropped, then the default profile should be assigned in the same session because if not, then during that session the user has no assigned profile which shouldn't happen?
SQL & PL/SQL :: Dropping A Procedure During Execution
Apr 26, 2012When the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .
View 5 Replies View RelatedDropping Encrypted Tablespace
Mar 29, 2013I created an encrypted tablespace for testing. I later dropped it but don't remember if I specified "including contents and datafiles". The tablespace was empty and there are no datafiles for it. However, the information for this dropped tablespace still shows up in v$encrypted_tablespaces. How do I get that lingering information removed?
View 3 Replies View RelatedDropping Column From Huge Table
Aug 9, 2012I am trying to drop a column from a huge partitioned table (non compressed including partitions). I am working on 11gR2 database for information.
i used below approach
1. alter table <tab_name> set t1 unused column;
2. alter table <tab_name> drop unused columns;
then i got the below error message
ORA-39726: unsupported add/drop column operation on compressed tables
First statement did work i could be able to add another column with the same name but still don't want the unused column on the table.
SQL & PL/SQL :: Dropping Users - Queue Tables
Dec 9, 2010I am trying to drop a user but i get the following error
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
A couple of questions on this error:
- I did a search on the forum and the thread [URL] appears to have a solution to it by using
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQCALL', force =>TRUE);
What i am not sure of is which queue will the above statement drop if run as sys and there are multiple schemas with different schema names but with the same queue name?
- We recently rebuilt the database server and prior to the rebuild i have always managed to drop a user without the above error. Is it likely that some setting somewhere is changed?
SQL & PL/SQL :: Dropping Unused Columns In A Partition?
Jan 21, 2003I have been trying to drop an unused column in a partitioned table, and the number of records stored in this unused column was very high. I kept on running into errors as follows:
ORA-01562: failed to extend rollback segment number 10
ORA-01650: unable to extend rollback segment R09 by 256 in tablespace RBS
I tried to "SET TRANSACTION USE ROLLBACK SEGMENT <name>" with a larger rollback segment, but it still did not work. Can I drop the "unused column" from each partition instead?
How to apply that? Or, what are my options besides increasing the size of the rollback segment?
SQL & PL/SQL :: Does Dropping Schema Purges Data
Oct 21, 2011I am working on Oracle 10g. I want to drop a schema and I want to re use the space in data files to other schema.
If I drop the user by "DROP USER USERNAME CASCADE" , tables in that schema are purged by default or do I have to explicitly drop the tables , purge the re cycle bin and then drop the schema?
Dropping Database Also Drop SP File?
Aug 21, 2013when going through some white papers i find athing which made me confusing it stats that dropping a database will also drop the sp file.
is it true
sp file is important is n't it
PL/SQL :: Dropping Connected Users In Oracle DB?
Mar 27, 2013I want to drop some users in Oracle DB using sqlplus but I am getting error:
SQL> DROP USER test CASCADE;
DROP USER test CASCADE
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected.But when I ran below command to know sessions connected I am not getting any results:
SQL> select sid,serial# from v$session where username = 'test';
no rows selected
While Dropping Old Undo Tablespace / Getting Error
Dec 23, 2012i Cannot drop old undo tablespace. While dropping the old undo tablespace we get an error
ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU77$' found, terminate dropping tablespace
SQL> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';
TABLESPACE_NAME STATUS SEGMENT_NAME
------------------------------ ---------------- ------------------------------
SYSTEM ONLINE SYSTEM
APPS_UNDO NEEDS RECOVERY _SYSSMU77$
Server Administration :: Error While Dropping User?
Mar 30, 2012Got following error while droping user
DB:9.2.0.8.0 OS:Windows 2003
SQL> drop user custom cascade;
drop user custom cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-21700: object does not exist or is marked for delete
PL/SQL :: Killing And Dropping Users Connected From Application
Jul 11, 2012I have a data base for the bank application development. So there might be more than one sessions of the application running on the data base as well as there might be more than one data base sessions being connected from the dbas.
There is refresh procedure to refresh the data base from the baseload scripts ,So for that first we need to disconnect the users (Kill the sessions) and then drop the users then recreate the users and populate the users with the base load data.
I have prepared the killing and dropping user script in the following manner, Most of the times it is working fine and killing the users but some times it is not able to kill the drop the users it is erroring out with the error that can not dorp the users as there are active sessions running ,
set termout on
set echo on
spool Kill_sessions_drop_users.log
DECLARE
v_alt_stmt VARCHAR2(1000) := 'ALTER SYSTEM DISCONNECT SESSION ''';
v_kill_stmt VARCHAR2(1000);
[code].......
--This will kill all the sessions except the current session
CURSOR cur_usr_dtls IS SELECT username FROM all_users WHERE username IN ('ACTIVITI','TP','TPCORR','TPTUX','TPVIEW');
v_dr_stmt VARCHAR2(1000);
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
DBMS_OUTPUT.PUT_LINE('The database has been put in the restricted mode');
FOR v_cur_ssn IN cur_session
[code]........
--Here we are making the processor to wait for 60 mins so that that the killing will happen at the OS label
DBMS_LOCK.SLEEP(60);
FOR v_cur_usr_dtls IN cur_usr_dtls LOOP
v_dr_stmt :='DROP USER '||v_cur_usr_dtls.username ||' CASCADE';
BEGIN
EXECUTE IMMEDIATE v_dr_stmt;
[code]........
PL/SQL :: Dropping Partition Dynamically In Interval Partitioning
Aug 22, 2012I have created an Interval Partition Table as show below, Is their any way, i can drop the partition dynamically when i truncate the table as oracle creates them with system generated name? Instead Alter table drop partition !
Create Table Script :
------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE TBL_EMP_SALES
(
EMP_ID NUMBER(38),
EMPNAME VARCHAR2(9),
EMP_SALES_AMOUNT NUMBER(5),
EMP_SALES_DATE DATE,
[Code]....
Insert Script :
------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO TBL_EMP_SALES VALUES (1001,'A',50,SYSDATE);
INSERT INTO TBL_EMP_SALES VALUES (1002,'B',100,SYSDATE+1);
INSERT INTO TBL_EMP_SALES VALUES (1003,'C',80,SYSDATE+2);
[Code]....
Partitions Created :
-------------------------------------------------------------------------------------------------------------------------------------------------
select segment_name,partition_name,bytes from user_segments where segment_name = 'TBL_EMP_SALES';
segment_name partition_name bytes
TBL_EMP_SALES P0 8388608
TBL_EMP_SALES SYS_P602 8388608
TBL_EMP_SALES SYS_P603 8388608
[Code]....
Server Administration :: Dropping Lower Case Tablespace
Nov 18, 2011How can I drop a lower case tablespace name from my DB?
SQL> select * from v$version;
BANNER
------------------------------------------------------------
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 IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
[code]....
Refreshment Slow / Dropping Materialized View Metdata
Jan 6, 2013I had to drop the MV with preserve table and rebuilt it after modifcation of materalized table.Before dropping the materialized view metdata was.
CREATE MATERIALIZED VIEW "DM_TGT_SHARED"."EA_MERGED_DATA_MV"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DM_TGT_SHARED"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
[code]...
now this view taking an ages to refresh as before , there is a MV log i suspect i did not use "with rowid" clause when rebuilding the materliazed view , how can i add with rowid clause which i reckon will improve the MV refreshment.
Server Administration :: ASM / Dropping A Newly Created Disk Group?
Apr 6, 2012I was setting up disks groups and I accidentally created one group (DATA) with "NORMAL" redundancy but wanted it to be "EXTERNAL". I tried using asmca to remove disks from the group, drop the group, change the redundancy..... All of this failed because there was an spfile on the disk group.
I finally got it to work with using this procedure:
sqlplus '/ as sysasm'
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 5 08:58:19 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> drop diskgroup DATA;
drop diskgroup DATA
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup "DATA" contains existing files
[code]....
In summary, I am not sure why changing the redundancy would be so difficult if there is data on the disk group.
Backup & Recovery :: Dropping Tablespace Including Contents And Datafiles
Jun 17, 20131) I have created One tablespace "ABCD" and assigned one user on it "ABCD_AR". I made table through the user and made some transaction.
2) Then I have taken RMAN backup, which include backup of tablespace (ABCD) also.
List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSTEM01.DBF
2 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSAUX01.DBF
3 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUNDOTBS01.DBF
4 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUSERS01.DBF
5 Full 997754 17-JUN-13 C:DATAFABCD01.DBF
3) After taking Rman backup, I issued command - "Drop tablespace ABCD including contents and datafiles;"
4) When i issue "LIst backup" through RMAN, its shows blank for the particular tablespace datafile.
SQL> DROP TABLESPACE ABCD INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
[code]....
how to recover those tablespace's datafile?What happened to earlier tablesapce backup?
Creating And Dropping Global Temp Tables Inside A Single Procedure
Aug 20, 2010I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Do we have any alternatives to this..
Server Administration :: Remove Datafile From Operating System After Dropping Its Tablespace
Jul 26, 2012I want to remove a tablespace with it's datafile.
I've ran: DROP TABLESPACE x_tbs INCLUDING CONTENTS;
I've read(should have read it prior to action) [URL]tm
and I understand now that I should have stated "AND DATAFILES", However, too late now. the tablespace is removed, but the datafile x_tbs is still present in a folder of my OS.
Is it safe to manually remove it by deleting it from the operating system?