I have a question about recyclebin.When i drop a table,the talbe will be moved to recyclebin,the name is changed to BIN$...,but the constraint built on the table aren't moved to recyclebin and their name are also BIN$...,why?
View 1 RepliesI am getting the following errors when I try drop a tablespace.
I already did the following.
a) The tablespace & its datafiles offline.
b) I have purged dba_recyclebin.
SQL> drop tablespace db_maintenance including contents and datafiles;
drop tablespace db_maintenance including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
I want to drop a column in a huge table which contain about 420,000,000 rows,i use the alter table drop coumn command to execute,and found it takes a long time and generate huge redo.
Is there any quickly way to drop a column in a huge table?
Im facing the problem whenever I try to drop a user. Following thing that I m trying..
system@vahan> drop user knp cascade;
Error at line 1:
ORA-00604: error occured at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
I can not drop datafile in a tablespace, how can i do?
SQL> Alter Database Datafile '/u02/app/oracle/oradata/oracl/hxl06.dbf'
Offline 2
3 /
Database altered.
SQL> Alter Tablespace tps_hxl
Drop Datafile '/u02/app/oracle/oradata/oracl/hxl06.dbf'; 2
Alter Tablespace tps_hxl
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace
How to drop a datafile of tablespace.
SQL> alter database datafile '/u01/app/oracle/oradata/oracl/hxl01.dbf' offline drop;
Database altered.The command success,but the dic view show the datafile also.
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
--------------------------------------------- ------------------------------
/u01/app/oracle/oradata/oracl/users01.dbf USERS
/u01/app/oracle/oradata/oracl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/oracl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/oracl/system01.dbf SYSTEM
/u01/app/oracle/oradata/oracl/hxl02.dbf TPS_TEST
/u01/app/oracle/oradata/oracl/hxl01.dbf TPS_TEST
6 rows selected.
I created new database. Now I want to drop database. So I had login as sys user and executed shutdown immediate command. The database was closed.Then I tried to execute startup restrict mount command so that I can run drop database command. Then it is giving error as
ORA-12154: TNS:could not resolve the connect identifier specified
I tried to restart the service,also checked lsnrctl command,and tnsnames.ora file.Everything is fine,but still gives the same error as above.
On a SAP system, am trying to drop six indexes, largest is 300MB and smallest is 50MB.
I tried running drop index sapusername.index_name on the 50MB index via SQL*Plus and it seems to be taking forever. anything I can check on the database on why it is taking such a long time?
I can leave it to run overnight but worried that when I come back the next day, it will still be hanged. Is there any quick way of dropping the index, .i.e. drop immediate ...
Am not using SAP's BRTOOLs as it is also hanging from there and the SAP-ADMIN had approved for the DBA to drop it from our end instead.
I want to drop some users which are no longer been used .What are the precautions i need to take before i drop users? I have taken logical backup (Export) of users i want to drop.Is there anything i missed out before i drop user?
View 4 Replies View RelatedI have one user CD_APP. I have one partition table CD.T_FCDR_DT. User has got ALTER/INSERT/UPDATE/DELETE/SELECT privileges on the table..
Now when I try to drop a partition, I get error as below:
-------------------------------------
SQL> show user
USER is "CD_APP"
SQL> ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES;
ALTER TABLE CD.T_FCDR_DT DROP PARTITION D01 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-01031: insufficient privileges
--------------------------------------
Do I have to grant some other privileges for this user.
SQL> drop MATERIALIZED view log on afccv.tbl_voicechat;
drop MATERIALIZED view log on afccv.tbl_voicechat
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
i dont want to kill the sessions or anything is there any way to set prority to do this task?
I was told to move 8 tables along with constraints,indexes,grants,rows,triggers from one database to another database.I did export and import for that.The command i used was
exp p5/chevuri@db3.SBC.COM file=C:alaexp.dmp log=C:alaexp.log
tables= ('tab1','tab2','tab3','tab4','tab5','tab6','tab7') rows=y indexes=y grants=y
constraints=y triggers=y direct=y
Below is the export output log.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 -
64bit Production With the Partitioning, OLAP, Data Mining and Real
Application Testing options Export done in WE8MSWIN1252 character set
and AL16UTF16 NCHAR
character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Direct Path ...
. . exporting table tab1 12 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
[code]...
Here is the import output log
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via direct path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing JAM's objects into JAM
.
[code]...
Everything got imported successfully . Still i have a doubt in export and import command, whether the command that i used for export and import was correct or if there is anything need to be added in command.
We are unable to drop user due to below error, how to drop the below user without shutdown the database.
SQL> drop user mvm_2010 cascade;
drop user mvm_2010 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
No session was available for particular user.
my user is trying to drop columns, but she gets below error:
SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables
39726. 00000 - "unsupported add/drop column operation on compressed tables"
i just checked whether table is compressed or not, it is not compressed it seems:
select owner, table_name,COMPRESSION,COMPRESS_FOR from dba_tables
2 where owner = 'EQUIPMENT' AND TABLE_NAME = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
EQUIPMENT ETHRNT_VRTL_CNXN_HRLY_AGG_SWP DISABLED
1 row selected. i am able to set one column as UNUSED. and then i am able to see the count accordingly from below view:
select * from DBA_UNUSED_COL_TABS
where owner ='EQUIPMENT' and table_name = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
but not able to drop the unused columns. if i tried to drop a column directly from the table, that also giving above error.
Through an Oracle Apex application I need to create/drop a user/schema in another Oracle database. i.e., create/drop user remotely using an Oracle Apex application.
View 12 Replies View RelatedI went through the link given in orafaq and tried to work on my system but the things did not work.
SQL> select *
2 from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> ALTER SYSTEM SET recyclebin = ON;
System altered.
SQL> SELECT Value FROM V$parameter WHERE Name = 'recyclebin';
VALUE
--------------------------------------------------------------------------------
ON
SQL> CREATE TABLE TEST_RBIN(VAL NUMBER);
Table created.
SQL> INSERT INTO TEST_RBIN(VAL) VALUES(10);
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL> DROP TABLE TEST_RBIN;
Table dropped.
SQL> SHOW RECYCLEBIN;
SQL> SELECT * FROM USER_RECYCLEBIN;
no rows selected
I created a materialized view with REFRESH FAST WITH PRIMARY KEY, which I assume generated its own PK for the materialized view (well, something certainly did, and it wasn't me); however, I did not include USING INDEX TABLESPACE <tablespace>, so the PK was generated in the same tablespace as the materialized view itself. The "problem" is, this particular database has a separate tablespace into which all of the indexes are supposed to go.
Is there any way, short of dropping and recreating the materialized view (which took 24 hours in the first place), to move the system-generated PK to a different tablespace?
For that matter, is there a reason why I wouldn't want to do this?
I would like to know the Replication method which is fast and the best approach,we need two schemas to be moved/replicated to a new reporting database.It appears that data is to be flown in one way,do we proceed with Materialized view replication or please clarify about Oracle Streams and Advanced replication. what are the factors to decide the replication method.
View 3 Replies View RelatedSome triggers are dropped and stored in recycle bin. when i am trying to restore it by the command
1* alter trigger "BIN$FFRO1R1LSuSIZ6uyLocD6g==$0" rename to WFNOTIFICATION_GEN_PK
SQL> /
alter trigger "BIN$FFRO1R1LSuSIZ6uyLocD6g==$0" rename to WFNOTIFICATION_GEN_PK
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?
Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?
Using oracle 11.2.0.3...How long does the content in recycle bin stay?
e.g is it dependant on size of undo tablespace and expires after certain time? Also if drop table, then recreate table with same name will this wipe the dropped table from the recycle bin automatically?
I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.
So far the only option I have is to export and then import back the data.
know if there is any way to move a partitioned table between table spaces of different block size?
We deleted millions of records from a table.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?
We are facing space crunch in our 4 development 11i ERP environment servers and decided to reclaim the space . We are having 11.1.0.7 Database . What we observed is, there are many objects present in the recyclebin and we went on to purge the reclybin of MSC user.
SQL>select owner,sum(space)* 8 / 1024/1024 "Size in GB" from dba_recyclebin group by owner order by sum(space) ;
OWNER Size in GB
--------------- ----------
MSC 127.672485
IRCUS 22.5664063
GL 2.98492432
APPS .38079834
XXDII_ESB .192993164
APPLSYS .001159668
TMPBKP .000427246
But even after purging the recycle bin of MSC User , we didn't find any changes in DBA_FREE_SPACE for those tablespaces in which recyclebin objects were present. We would have seen the freed space in dba_free_space.
When drop a table what happen to view,sequence,synonyms,index,constraint for the table
View 9 Replies View RelatedThere two users a and b,and the table b.test_part.And one procedure under a ,text like below:
create or replace procedure a.sp_test
is
vs_sqls varchar2(32767);
begin
vs_sqls:='alter table b.test_part truncate partition p_day';
execute immediate vs_sqls;
end;
now,i have to grant drop any table to a.but in fact,i prefer to drop the special one table "b.test_partany" rather than any other table.how ? no by trigger!
I am trying to Truncate the table but it doesnt get truncated. When I issue the command it even doesnt throw the error. I also tried to drop the table but cant even able to drop the table. I thought table might be locked. But it allows me delete a row from the table.
I am using Oracle 11g Release 11.1.0.7.0
I have created a table with space by mistake.how to drop that..?
QL> select * from cat where table_name like 'OM_DW_RTNORD%';
TABLE_NAME TABLE_TYPE
------------------------------ -----------
OM_DW_RTNORD? TABLE
OM_DW_RTNORD TABLE
[code]....
no rows selected
SQL> drop table OM_DW_RTNORD? purge;
drop table OM_DW_RTNORD? purge
*
ERROR at line 1:ORA-00911: invalid character
how to drop global temporary table?
while droping global temporary table we are getting below error
"ORA-14452: attempt to create, alter or drop an index on temporary table already in use"
,how to drop the queue table from our database.when iam trying to drop the queue table aim getting this error
error dropping AQ$_DEMO_QUEUE_TABLE_G;
ORA-00942:TABLE OR VIEW DOES NOT EXIST