Jump to content


  • Content count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About NigelDams

  • Rank
    Advanced Member
  1. NigelDams

    sysdba lacks privileges

    Hi Donald, thanks for your reply. I actually fixed it in the meantime, the only way I could think of was to go in to rman and 'grant sysdba to sys' which did the trick. But this raises another issue ... before doing this, I could get into rman by typing rman target / as I've done a thousand times before. Now, for the first time, I get this: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-04005: error from target database: ORA-01031: insufficient privileges ?????? If I do "rman target sys/<pwd>" I can get in - but any other username gets the same reply as above
  2. NigelDams

    sysdba lacks privileges

    Hi everyone - I've just created a new oracle database, using 11.2g on windows, and I logged into SQL*Plus using "sys/xxx as sysdba". All good so far. Then I entered 'archive log list' and it said 'insufficient privileges'. I've done this kind of thing for years and years and this is the first time sysdba has lacked that privilege. If I log in as 'sysoper' the command works. I'm using the same database creation scripts I always use and can't think of anything that could possibly be different .... any ideas? Is there some way I can 'grant sysdba to sys' or something like that?
  3. NigelDams

    Tablespace not empty even though I emptied it

    I am in the UK - but I still like cold beer Thanks for the suggestions, I'm still trying to figure out what's going on, as I don't like not knowing
  4. NigelDams

    Tablespace not empty even though I emptied it

    Fixed it - though I'm not sure why. I just put the tablespace offline, then back online and then it allowed me to alter the size of the file, where moments before it had been saying 'file contains used data .... ' Enterprise manager still shows the same list of things it thinks are in there, but the file is now gratifyingly small, so I'm gonna have a beer. Thanks again for all your help. If you have any Gandalf insight on what I've just told you, I'd be glad to hear it.
  5. NigelDams

    Tablespace not empty even though I emptied it

    Hi again Donald, and thanks for all your help. Unfortunately nothing works - I ran all those checks and queries and recyclebin purges, and whatever query I run shows that the tablespace I'm talking about is just empty. Nothing there. But if I try to resize the datafile, it keeps saying there's data getting in the way, and if I use Enterprise Manager it shows me more than a thousand objects ..... users, views, indexes, tons of stuff. Weird.
  6. NigelDams

    Tablespace not empty even though I emptied it

    Hi Donald, and thanks for your reply. After purging recyclebin, the BIN$ objects have gone, but there are still loads and loads of others, with object type in 'USER','VIEW','TRIGGER' The weirdest thing is that (in Enterprise Manager) the same user shows up in the listings for EVERY datafile. And not a single one of these things can be found by doing a query on dba_extents. Stumped (english version of 'caught behind the 8 ball' )
  7. Hi all - I've taken over management of a system that has been running for a couple of years and some of the tables have grown to be 40Gigs in size, with lots of wastage. So I archived off all but the last 60 days of live data and then did an export. Now I have a bunch of small dmp files, and I want to get rid of two out of three data files, by saying 'alter tablespace ... drop datafile ...' So I run the following query: select a.owner, a.segment_name, a.segment_type, b.file_name from dba_segments a, dba_data_files b where a.file_id = b.file_id and b.tablespace in (list of tablespaces I'm interested in) and file_name like (the ones I want to dtop) This query shows me a bunch of tables and indexes properly distributed between 'table' and 'index' tablespaces. I drop all the tables I'm about to re-import, and run the query again. It returns 'no rows' so I think I'm good to go. I run 'alter tablespace ... drop datafile .....' but I get ORA-03262: the file is non-empty So I run the query again and rub my eyes - not a single segment has the file_id of that file. But if I run Enterprise Manager I find a world of stuff, user views, indexes, enormous things named 'BIN$.......' My question is, how do I find all those things and move or delete them so that I can drop the soon-to-be redundant datafile?
  8. NigelDams

    How to see what oracle processes are doing disk I/O

    Hi Deepak and Donald, thanks for your input, very helpful - except that I tried running awrrpt and got this: 427 20 Aug 2015 22:00 1 428 20 Aug 2015 23:00 1 429 21 Aug 2015 00:00 1 430 21 Aug 2015 01:00 1 431 21 Aug 2015 02:00 1 432 21 Aug 2015 03:00 1 433 21 Aug 2015 04:00 1 434 21 Aug 2015 05:00 1 435 21 Aug 2015 06:00 1 436 21 Aug 2015 07:00 1 437 21 Aug 2015 08:00 1 438 21 Aug 2015 09:00 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 343 Begin Snapshot Id specified: 343 Enter value for end_snap: 438 End Snapshot Id specified: 438 declare * ERROR at line 1: ORA-20200: The instance was shutdown between snapshots 343 and 438 For brevity I've left out the top half of the 'snapshots' list .... what's baffling is the 'instance was shut down' error, as the instance was miost definitely not shut down .... and also that error 20200 seems to have three or more quite different interpretations .... :-S
  9. Hi all (and especially Donald) .... I have an 11g installation on Windows server 2012 which is a copy of an existing customer system, but which is doing hundreds of MB of disk I/O per second, as compared to the (still live) old system, running on Server 2008, which is only doing some kilobytes per second. Ironically, the new system isn't even being used yet, but the network guy is alarmed at all the activity and wants to know what's causing it. The activity is pretty much all listed as belonging to "oracle.exe" on various DBF files. I want to write a script that will hopefully show me what Oracle processes are responsible for all this I/O I bought your collection of tuning scripts, Donald, but can't be at all sure which if any of them will do the trick .... can you give me some pointers, please?
  10. NigelDams

    Baffling behaviour of impdp

    Hi again, sorry, been on holiday - erm .... I didn't use any script to measure FRA usage, I just checked 'properties' in flash_recovery_area ...
  11. NigelDams

    Baffling behaviour of impdp

    Thanks I'll try those
  12. NigelDams

    Baffling behaviour of impdp

    Hi all, hope someone can shed some light on this ..... I have a set of DMP files taken from our customer's old machine which I've tried to import onto their new one: A.DMP 1.8 Gb B.DMP 1.1Gb C.DMP 3.7Gb I had put them all into a script to run the imports while I was doing other things, and at first it seemed to go well. The first file imported in little more than half an hour, and didn't exceed a flash recovery area of 10G. The second, however, failed with ORA-00600, which I gather can "only be interpreted by an Oracle Support Analyst". So I have a support call out for that. It turns out that B.DMP caused the FRA to become full. So I recovered the database and executed "backup archivelog all delete input" which reduced the FRA to less than 1 gig out of a maximum of ten. I then instituted the second import, keeping an eye on the FRA. It quickly started mounting up towards the 10 gig mark, so I increased it to 35 gigs. After four hours there was no sign of progress except that the machine was thrashing, CPU and memory almost completely taken up with Oracle processes, and millions of disk reads and writes. At the same time, redo logs were switching many times per minute, so I created two big new ones, which eased things a bit. This second import eventually completed after about 10 hours, and at this time the FRA was 25.8 Gb in size. Each import was performed on the same machine, in identical network circumstances (brand new machine, network traffic and database usage zero) Each import used identical param files, example below: dumpfile=B.dmp logfile=B.log directory=dump_dir table_exists_action=append Here is the output from v$version: SQL> select * from v$version; BANNER ---------------------------------------------------------- Oracle Database 11g Release - 64bit Production PL/SQL Release - Production CORE Production TNS for 64-bit Windows: Version - Production NLSRTL Version - Production SQL> My question is, why would the second import, on the same machine, take ten times longer and use more than twice as much FRA? Maybe the answer is partly because A.DMP contained tables which were "parent" tables, ie they had no referential constraint dependencies on other tables, whereas B.DMP contained tables which had their "parents" in that first set. Can anyone suggest ways I could streamline this exercise? Anyone know how to calculate redo log and FRA sizes for a given size of DMP file? I'm faced with trying to import C.DMP now, which contains the "children" of B.DMP and is 3.7Gb in size, so not looking forward to it much. Any help/hints much appreciated Added: Should have mentioned that we're running Server 2012 on a virtual machine
  13. Hi all, slightly confused here: I ran the following query on hearing that the customer was having trouble with VMS data: select tablespace_name, file_name, bytes/(1024*1024) Mb, maxbytes/(1024*1024) "Max Mb", user_bytes/(1024*1024) "User Mb" from dba_data_files where tablespace_name like '%VMS%'; VMS_INDEX D:\ORACLE\PRODUCT\10.2.0\ORADATA\CUTLAS2\DBF\VMS_INDEX1.DBF 1335 32767.984375 1334.9375 VMS_TABLE D:\ORACLE\PRODUCT\10.2.0\ORADATA\CUTLAS2\DBF\VMS_TABLE1.DBF 2500 2500 2499.9375 This made me think the VMS_TABLE tablespace was full. So I deleted about 60% of all the VMS data, then ran the following: alter table vms_.... enable row movement; alter table vms_.... shrink space I expected this to change the "bytes" part of dba_data_files, but it didn't. Even after an intervening weekend, the 'bytes' and 'maxbytes' figures are the same. The documentation seems to imply that 'bytes' refers to the number of bytes actually used. And in fact, the tablespace is far from full, as shown when I ran: select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Used Size" from dba_segments where tablespace_name like '%VMS%' group by tablespace_name VMS_INDEX 980.9375 VMS_TABLE 290.875 So it looks like my deletion and space-shrinking was not necessary, and that when 'bytes' is nearly equal to 'maxbytes' it means something other than 'tablespace nearly full'. In any case, I'm confused by the disparity between the dba_data_files and dba_segments (and also dba_free_space shows there's masses of free space on the tablespace). Can anyone explain? Oh, I should mention this is the output from v$version: Oracle Database 10g Release - Production PL/SQL Release - Production "CORE Production" TNS for 32-bit Windows: Version - Production NLSRTL Version - Production this is running on Windoze server 2003 standard edition sp1
  14. NigelDams

    convert X,Y coordinates to lat, long

    Hi again, and thanks for your reply ... yes, it is a very complicated bit of mathematics (to me, at any rate) ... I was just hoping someone in the community had had the same problem and solved it. I guess one solution would be to assume that the country is flat (these journeys are not particularly long) but I don't think the customer would go for that. Anyway, spherical trigonometry was not on the syllabus when I was at school!