Server Administration :: How To Remove Deleted Entry From Sys.ts$
Oct 15, 2011
### Changes made ###
1 week before we did a change on tablespace segment management - from MANUAL to AUTO by following method:
1. create INVD2 & INVX2 & LOBD tablespace.
2. Move TABLE from INVD to INVD2.
3. Rebuild INDEX from INVX to INVX2.
4. Move LOBSEGMENT from INVD to LOBD tablespace.
5. After confirm no segments exist in old tablespace, offline and drop INVD & INVX.
6. Change default tablespace for INV user to INVD2.
7. RENAME TABLESPACE INVD2 to INVD, INVX2 to INVX.
8. Change default tablespace for INV user to INVD back.
9. Run Gather Schema Stat for INV using UNIX scheduler which work usually. However, error ended with ORA-03113 & ORA-03114.
10. Manual execute with same statement the following day, procedure completed successfull.
After 1 week later, inventory forms detected error FRM-40735 in all forms. Checked the gather schema stat job was run in the morning before user feedback..
AFter refer notes from metalink, I understand this is a bug where RENAME of the tablespace could not rename as the previous one, as the deleted entry is still exist in sys.ts$?
There is no segments exist in the deleted tablespace, or any user default tablespace is assigned to the deleted tablespace.
My Question:How can we delete the deleted entry from sys.ts$?And should we rename the tablespace from INVD to INVD3 (or can we use back INVD2) to avoid any unforseen error again?
SELECT * FROM dba_repgroup; returns a single row sname='VL_REPG', gname='VL_REPG', owner='PUBLIC'.
How do I get rid of it?
select * from dba_registered_mview_groups; returns no rows selected select * from dba_objects where object_name = 'VL_REG'; returns no rows selected
I triedSQL> l 1 BEGIN 2 dbms_repcat.comment_on_mview_repsites(gowner => 'PUBLIC', gname => 'VL_REPG', COMMENT => 'asdf'); 3* END; SQL> / BEGIN * ERROR at line 1: ORA-23314: database is not a materialized view site for "PUBLIC"."VL_REPG" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.DBMS_REPCAT_UTL", line 7376 ORA-06512: at "SYS.DBMS_REPCAT", line 509 ORA-06512: at line 2
SQL> alter system set audit_trail=OS SCOPE=SPFILE;
System altered.
SQL> STARTUP FORCE ORACLE instance started.
Total System Global Area 171966464 bytes Fixed Size 2019320 bytes Variable Size 113246216 bytes Database Buffers 50331648 bytes Redo Buffers 6369280 bytes Database mounted. Database opened.
SQL> show parameter audit
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/orcl/adu mp audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string OS SQL>
SQL> create user apexos identified by abc1;
User created.
SQL> grant connect, resource to apexos;
Grant succeeded.
SQL> audit select table, insert table by apexos by access;
Audit succeeded.
SQL> audit table by apexos by access;
SQL> SELECT audit_option, failure, success, user_name FROM dba_stmt_audit_opts;
AUDIT_OPTION FAILURE SUCCESS USER_NAME ---------------------------------------- ---------- ---------- ------------------------------ TABLE BY ACCESS BY ACCESS APEXOS SELECT TABLE BY ACCESS BY ACCESS APEXOS INSERT TABLE BY ACCESS BY ACCESS APEXOS
cONN APPOS/ABC1
SQL> CREATE TABLE TAB1 (ID NUMBER, NAME VARCHAR2(20));
Table created.
SQL> insert into tab1 values (10, 'Michel');
1 row created.
SQL> insert into tab1 values (30, 'Andrew');
1 row created.
SQL> select * from tab1;
ID NAME ---------- -------------------- 10 Michel 30 Andrew
SQL> /
ID NAME ---------- -------------------- 10 Michel 30 Andrew
SQL>
SQL> select username, timestamp, action_name, action, SES_ACTIONs, sql_text 2 from USER_audit_trail where username='APEXOS';
no rows selected
SQL>
I also did not find any file contiaing the above statement as audit record in /u01/app/oracle/admin/orcl/adump.
There are numerous old file in the /u01/app/oracle/admin/orcl/adump locaton. But When I executed the sql statement then that time no audit file was not generated in the location.
Is there any way I can find out what caused the database to crash; either a history of commands executed within the database, I lost my bdump directory before the scheduled backup ran and the only logs available are after I re-created the directory.
SQL> startup ORA-00444: background process "PMON" failed while starting ORA-07446: sdnfy: bad value '' for parameter . SQL> [code]....
I've got to collect statistics by DBMS_STAT. For the first time, the spcreate.sql is to be called, however, it returned one error following:
If this script is automatically called from spcreate (which is the supported method), all STATSPACK segments will be created in the PERFSTAT user's default tablespace.
Using perfstat tablespace to store Statspack objects
create public synonym STATS$SNAPSHOT_ID for STATS$SNAPSHOT_ID * ERROR at line 1: ORA-00955: name is already used by an existing object Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
And then, I checked it again with its owner:
SQL> select owner, synonym_name 2 from dba_synonyms 3 where synonym_name='STATS$SNAPSHOT_ID';
OWNER SYNONYM_NAME ------------------------------ ------------------------------ PUBLIC STATS$SNAPSHOT_ID
Then, I checked again after dropping schema PERFSTAT:
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?
I am not able to delete one month old archive log file manually on windows which doesn't having info about the standby on v$archived_log view of primary database. the sequence were already applied to the standby database. It shows the status as deleted in v$archived_log. while deleting the file manually. it showing an error like another program or person is using it.
Suppose that, I have two tables: emp, dept emp records the empid, emp_name, deptid dept records the deptid, dept_name
Here is a record, it's a president or some special position in company, so it's deptid is set to NULL. Here comes the question, how can I print all the emp_name with their deptartment name?
I know how to print all the emp_name with their department name if they have dept_id, but is that possible that I merge the record with dept_id NULL?
Developer is importing some large volumes of data. As the filesystem was filling up fast, I removed the all the archive log files. Will this affect the functioning of database? It is a development environment.
How do i check the oracle database for an entry of the same type before i submit an entry.
basically i am taking information from a form and passing it into php which in turn uses oracle to store it in a database, how can i check that there is no entry of the same type in the database?
Variation of a post I entered last week.I have 2 queries, 1 obtains a list of paired entries, and the other for a given pair of entries returns a list of common locations.Both these queries behave exactly as I expect (although trimmed significantly for this example)With C++ code, i can recursively work through the results from query 1, and plug them into query 2, but this has a large time overhead.Preference would be to nest the queries so result is extracted during 1 connection, and not constantly requerying the database for additional sequences.
SELECT SWTT_HEADER.THR_ID AS S_HDR, DWTT_HEADER.THR_ID AS D_HDR FROM RTDEV.TRIP_HEADERS SWTT_HEADER INNER JOIN RTDEV.TRIP_HEADERS DWTT_HEADER ON SWTT_HEADER.TRIP_NAME = DWTT_HEADER.TRIP_NAME WHERE (SWTT_HEADER.TME_ID IN (1)) AND (DWTT_HEADER.TME_ID IN
(2))Query 1 above gives me exactly the trip pairs I need, can range from 1 pair to a few thousand..Query 2 below gives me the common locations for a specific pair as extracted line by line from query 1.What I am looking for, is how to combine these 2 queries so that the values of THR_ID in query 2 are recursively pulled from query 1.
SELECT DISTINCT DWTT_BODIES.MASTER_NODE AS LOCN FROM RTDEV.TRIP_HEADERS SWTT_HEADER INNER JOIN RTDEV.TRIP_HEADERS DWTT_HEADER ON SWTT_HEADER.TRIP_NAME = DWTT_HEADER.TRIP_NAME [code]...
From the sample data, i expect query 1 to return pairs 1,9 2,10, 3,11 4,12 5,13 6,14 7,15 and 8,16 Entering 1 and 9 (first pair) as the THR_ID values in query 2 will yield B,C , as these 2 locations are common to both trips. Entering 2 and 10 (2nd pair) as the THR_ID values in query 2 will yield G,H , as these 2 locations are common to both trips.
The aim is to achieve B,C,G,H from a single query.I figure if I can write C++ code for it, there has to be an SQL shorthand?
i am new to oracle form , i am using orcale 10g forms, i have following requirements.
1-I have a Data Block names Hes_Modules which is connected to a table HES_MODULES having following fields (modid,moddescr).
i want when form open it should show all enteries in table, My form conatin 5 fields and scrollbar.After form open it should allow to update and insert the reords in form
I have an issue of not able to connect to the database after switchover without making changes in TNS entry after the switchover when I try to connect to then the primary database (which is standby before ) , the tns entry has entries for both the primary and secondary but the order of the primay and seconday database is to be changed manually till then it keep showing ERROR: ORA-01033: ORACLE initialization or shutdown in progress.
We had an escalation wherein one of team members accidentally deleted an LDAP entry for a database. We use Oracle Net Manager to add/delete the connect descriptor.
Are there any logs using which we can find out as to who deleted the entry.
I have a form with three block when i finish entry in 3rd block i use go_block for going to 2nd one but as i fired it oracle ask me for save trasaction
Actually i want to save entry in all form at same time
As there is no boolean datatype in oracle (I wonder why, it exists in ms-sql, and I'm still quite new to oracle) you have several ways to 'simulate' a boolean. (1/0, 'Y'/'N', in german 'J'/'N', or this in small caps...)
Now for a checkbox you have to include the 'true'-value in the LOV. But what, if your database doesn't use '1', as you have implemented, but 'Y', or what, if the 'false'-value is not '0' but null? Each time you would have to adopt your STATIC2:...
So I am wondering about a flexible solution, and I would like to ask if and how it is possible. (Havn't found anything via searching for that yet.)
Instead of writing:
STATIC2: ;Y ... and having only the 'Y/N' implementation working, how about it would look something like that: STATIC2: ;NOT('0' OR 'N' OR 'n' OR null) ... and a lot of reasonable boolean-implementations are incorporated. You could use a standard LOV for all checkboxes, dealing with different 'booleans'
But just like that, its not working...
So, a) it there a syntax for that at this point? and b) what do you think about that in general, or whats your 'best practice'?
review/correct my steps for adding an entry to the orapwd file.Is there an environment variable I need to set to tell it the name of the password file. In addition, how do I get a "user" in the password file when orapwd accepts file, password and entries as paramaters. Does this mean I need to give user "sys" sysdba privs?
USERNAME SYSDBA SYSOPER ------------------------------ ------ ------- SYS TRUE TRUE /oracle/10.2.0.1.0/dbs> orapwd file=orapwd_pri password=sys entries=50; /oracle/10.2.0.1.0/dbs> ls -tl orapwd_pri -rwxrwxr-x 1 oracle dba 7680 Jul 06 12:09 orapwd_pri /oracle/10.2.0.1.0/dbs> sqlplus "/ as sysdba" SQL> grant sysdba to xxx; grant sysdba to xxx
ERROR at line 1: ORA-01994: GRANT failed: password file missing or disabled SQL> show parameter password;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE
Is there a special way I have to name the file or setup a env to find it?
I have a DB on my local machine and it was working fine till yesterday.Today suddenly I started receiving error "IO Error : The Network Adapter Could not establish the connection".
1) My first question is that is there some log entry corresponding to this failed connection attempt in listener.log or alert.log ? If yes, how to identify it ?
2) My second question is : What to deduct from listener logs pasted below?I know about "service_update" messages, but what about others (having CONNECT_DATA in them) ? Also, what is "WARNING: Subscription for node down event still pending" in below logs ?
I have a major problem my oracle_home/bin directory was delete mistakenly by on of our user. Because i cannot use all oracle utitilessqlplus,dbca,netca,netmgr. how do i the cover this problem?
i want to insert values in another table whatever i deleted. i don't want whole information like backup, i just want deleted information only. whenever any 1 will try to delete any information on my database ,easily i would get to know what,when he has deleted .
declare v_show_documentVARCHAR2 (2000) := '/reports/rwservlet?'; v_connectVARCHAR2 (200) := 'userid=scot/tiger@connect_string'; v_report_serverVARCHAR2 (30) := 'rep_cs-oracle'; -- i make this server name by using this command:C:DevSuiteHome_1BIN wserver server=rep60 start >>when i started it getting to shutdowing directly!!!!! i don't know why [code].....
the error is:
before the browser is opened!! an error is occur is that: javaw.exe -Entry point Not found the procedure entry point kguuseg could not be located in the dynamic link library oraclient10.dll
then the browser is opened ,this error written in it:
REP-52266: The in-process Reports Server rep_cs-oracle failed to start.org.omg.CORBA.OBJECT_NOT_EXIST: vmcid: SUN minor 204 completed: No
