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 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
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.
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?
I 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.
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?
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 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?
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
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.
i tried creating an context index. But it is aborted in the middle. do to reuse the spce i want to drop the index. But it shows an error that " cannot drop index marked as process" how to delete the index.
I am trying to drop a index tablespace I moved all indexes to new tablespace there is no indexes in that tablespace even any objects but still it is not dropping.it is giving error as below.
SQL> drop tablespace ret_index including contents; drop tablespace ret_index including contents * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> alter tablespace ret_index drop datafile '/path/index3.dbf'; alter tablespace ret_index drop datafile '/path/ret_index3.dbf' * ERROR at line 1: ORA-03262: the file is non-empty
try to resize
SQL> alter database datafile 4 resize 100m; alter database datafile 4 resize 100m * ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value
we have 25 GB free space in Index tablespace, to avoid space issue i have added 10GB of space. Next shift DBA, rebuild INDEX of the from INDEX tablespace. Now i can check, only 27GB free space in INDEX tablespace...
SQL> Create Table tb_compress_test 2 As 3 Select * From dba_objects;
Table created.
SQL> Create Index idx_object_id On tb_compress_test(object_id);
Index created.
SQL> Alter Index idx_object_id Move Compress; Alter Index idx_object_id Move Compress * ERROR at line 1: ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
What exactly mean density of the index in db meanings ? I mean when you configure rebuilt of the index and how do you determine the value of density? for e.g when I have density 40 what does it mean ? next question : how shall I determine which tables are included in index ?
In our production database server Index tablespace is not growing now... On average index tablespace increases 200 MB to 250 MB per day. Last two weeks index tablespace does not increase..
I am giving you all a statistics of our production db Tablespace usage..
We are trying to create an index using parallelism. The table contains 24 Million row. But It takes very long time. Also we are unable to see any sessions confirming that the index creation is using parallelism. The creation goes on for more than 3 hours. SQL statement executed :
CREATE INDEX ANT.ANT_OM_TRAN ON APPS.ANT_OM_TRAN_INDX (last_update_date) PARALLEL 4 TABLESPACE APPS_TS_IDX;
I found out the session using the query
select sid,serial#,program,module,osuser,username,machine,inst_id,sql_id from gv$session where type not in ( 'BACKGROUND') and machine='APCUSDE' and osuser='applmgr' and program like 'sqlplus%' order by logon_time ; select * from gv$sqltext where sql_id='akrr2ww1ukq5z';
The given queries were used to check if it was using parallelism, but i was unable to find anything.
SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set", NAME "Stat Name", VALUE FROM GV$PX_SESSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC# AND NAME LIKE 'PHYSICAL READS' AND VALUE > 0 ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET; SELECT * FROM V$PX_PROCESS;
how can i make sure that, it is using parallelism.
I ran into an issue in a project where a function is recreating an index-organized table by doing:Table Structure:
CREATE TABLE table_iot( ...) ORGANIZATION INDEX OVERFLOW ...;
Recreate Steps:
1) Populate global temporary staging table (gtt) with data -- where gtt is staging for target index-organized table (iot) 2) Lock the target index-organized table (iot) 3) Copy old iot data to gtt -- gtt now contains old and new data 4) Create new index-organized table (iot2) from gtt -- iot2 now contains old and new data
[code]...
Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.The following statement rebuilds the index-organized table admin_docindex:
When you create a MAV, you automatically get a hidden column and an index. Here's an example,drop user jon cascade;
grant dba to jon identified by jon; conn jon/jon create table emp as select * from scott.emp; create materialized view mv1 enable query rewrite as select deptno,sum(sal) from emp group by deptno; select object_name,object_type from user_objects; select index_name,column_name from user_ind_columns where table_name='MV1'; select column_name,hidden_column from user_tab_cols where table_name='MV1'; select deptno,"SUM(SAL)",sys_nc00003$ from mv1;
