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?
View 4 Replies
ADVERTISEMENT
Mar 19, 2012
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
how I get rid of this dba_repgroup.
View 2 Replies
View Related
Jul 26, 2012
I am trying to enable auditing as
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.
how to find audit record.
View 5 Replies
View Related
Sep 19, 2011
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]....
View 4 Replies
View Related
Sep 11, 2007
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
... Creating STATS$SNAPSHOT_ID Sequence Sequence created.
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:
SQL> conn /as sysdba
Connected.
SQL>
SQL> select username, account_status
[code]...
What's the STATS@SNAPSHOT_ID synonym? Can I remove it from PUBLIC owner and recreate?
View 13 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
Sep 20, 2010
version: 10.2.0.4
OS: windows server 2003
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.
View 10 Replies
View Related
May 30, 2010
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?
View 9 Replies
View Related
Mar 4, 2011
I have been asked to take the export of a schema and then asked to remove all dependencies on tablespace. Provide me the syntax.
View 1 Replies
View Related
Sep 12, 2011
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.
View 6 Replies
View Related
Mar 27, 2007
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?
View 1 Replies
View Related
Jun 1, 2013
How the loop back entry in /etc/hosts relates to listener?
View 1 Replies
View Related
Apr 21, 2010
how can I distinct this query to get just one entry for one day.
this query:
select to_date(to_char(TIMESTAMP, 'YYYY-MON-DD HH24.MI.SS'), 'YYYY-MON-DD HH24.MI.SS') datum from event_table where id=15 ORDER by timestamp
returns:
Datum
01-MAY-09
01-MAY-09
01-MAY-09
..
..
..
02-MAY-09
02-MAY-09
.
.
but i want to have:
Datum
01-MAY-09
02-MAY-09
03-MAY-09
.
.
View 5 Replies
View Related
Jan 28, 2010
i am working on attendance system using barcode scanner.
i want the user can only mark his/her attendance by scanning the attendance card.and cannot use keyboard.
View 9 Replies
View Related
Jun 4, 2013
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?
View 10 Replies
View Related
Feb 25, 2010
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
View 3 Replies
View Related
Mar 9, 2011
where i could get the order entry application wich is found in oracle 10g forms book ?
View 4 Replies
View Related
Nov 19, 2011
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.
View 5 Replies
View Related
Nov 25, 2010
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.
View 1 Replies
View Related
May 17, 2011
How can i avoid duplicate entry at entry level in form rather than when i pressed save button
View 2 Replies
View Related
Feb 25, 2013
I have to check if an entered item is identical to a previous entered item.
Tried to use the Page Level Validation but am confused with the way to do it.
P.S> The issue is that the entered item should be identical to part of the previous item (i.e. substr(P5_item, 1, 8)...)
View 3 Replies
View Related
May 16, 2011
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
View 3 Replies
View Related
Jul 11, 2012
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'?
View 0 Replies
View Related
Jul 6, 2010
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?
View 5 Replies
View Related
Dec 10, 2012
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 ?
......
10-DEC-2012 12:48:18 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=B_ADMIN))(SERVER=dedicated)(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58460)) * establish * orcl * 0
10-DEC-2012 12:48:20 * service_update * orcl * 0
10-DEC-2012 12:51:35 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=B_ADMIN))
[code]...
View 4 Replies
View Related
Nov 8, 2012
declare
min_as_of_dt date;
max_as_of_dt date := to_date('30-SEP-2012', 'DD-MON-YYYY');
begin
DBMS_OUTPUT.ENABLE(10000000);
[code]......
Problem: rows are not getting deleted after running this script.
View 1 Replies
View Related
Nov 4, 2011
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?
View 2 Replies
View Related
Mar 27, 2012
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 .
View 14 Replies
View Related
Jun 13, 2013
how to retrieve deleted rows in oracle?
View 7 Replies
View Related
Jun 7, 2010
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
View 2 Replies
View Related
