Dropping Users And Recreating?
Jun 18, 2012
we 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
View 6 Replies
ADVERTISEMENT
Jul 26, 2010
Is it possible to change the start with value on a sequence without dropping and recreating it?
View 3 Replies
View Related
Oct 2, 2013
is there a way to modify supertypes without dropping / recreating its subtypes?
View 6 Replies
View Related
Dec 9, 2010
I 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?
View 2 Replies
View Related
Mar 27, 2013
I 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
View 3 Replies
View Related
Jul 11, 2012
I 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]........
View 4 Replies
View Related
Jan 11, 2013
There is a scenario: I dropped all objects of 3 users and dropped other 2 users and then I checked the space from dba_segments it reflects decreased space which is perfectly fine. Problem is that when I checked the space of physical datafile on disk it remains same. How can I restore or shrink or regained the space after dropping objects and users to maintain my storage requirements.
View 1 Replies
View Related
Nov 28, 2011
I 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.
View 17 Replies
View Related
Jun 4, 2010
I 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
View 5 Replies
View Related
Apr 13, 2008
I 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.
View 31 Replies
View Related
Jul 7, 2012
Do i have to create indexes again if i drop a partition on a table?
View 6 Replies
View Related
Nov 2, 2012
ALTER 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.
View 5 Replies
View Related
Jun 9, 2010
Would 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?
View 2 Replies
View Related
Apr 26, 2012
When the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .
View 5 Replies
View Related
Mar 29, 2013
I 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 Related
Aug 9, 2012
I 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.
View 5 Replies
View Related
Jan 21, 2003
I 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?
View 6 Replies
View Related
Oct 21, 2011
I 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?
View 2 Replies
View Related
Aug 21, 2013
when 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
View 1 Replies
View Related
Dec 23, 2012
i 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$
View 11 Replies
View Related
Mar 30, 2012
Got 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
View 1 Replies
View Related
Aug 22, 2012
I 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]....
View 5 Replies
View Related
Nov 18, 2011
How 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]....
View 2 Replies
View Related
Jan 6, 2013
I 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.
View 7 Replies
View Related
Apr 6, 2012
I 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.
View 4 Replies
View Related
Jun 17, 2013
1) 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?
View 9 Replies
View Related
Aug 20, 2010
I 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..
View 3 Replies
View Related
Jul 26, 2012
I 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?
View 2 Replies
View Related
Jan 20, 2011
Our Testing DB is running in No archive log mode. I did a schema level import by dropping the existing user that contain tables, recreate the user and finished the import. Now they want the old tables back.Is there is any way to recover the old tables?
View 5 Replies
View Related
Jun 3, 2011
I have 4 tables as follows.
1.Deploybase(DeployId)
2.LicenseHistory(DeployId,UserId)
3.Users(UserId,User_Detail_id)
4.UserDetails(User_Detail_id)
Tables are joined using below joins.
Deploybase.DeployId=LicenseHistory.DeployId
LicenseHistory.User_id=Users.User_id
Users.User_Detail_id=UserDetails.User_Detail_id
When I join LicenseHistory,Users,UserDetails, all records are unique.
But the issues is: LicenseHistory table has duplicate records for DeployId column which is used to join table Deploybase. As a result when I join it to Deploybase, it gives me repeating entries which I don't want
I want Comments from LicenseHistory,Login from Users and User_name from UserDetails. There are multiple columns from 1st table i.e. Deploybase which I need.After reading on Exists operator, I made following query.
Select DeployId,comment from (select comments as comment,deploy_id as DeployId from LicenseHistory slh where exists (select deploy_id from symdb.symdb_deploybase sdb) and slh.comments is not null)
Above query gives me results from LicenseHistory table only. But I don't know how to add tables Users,UserDetails
View 1 Replies
View Related
