Who has SYSDBA or SYSOPER privileges?

24 Feb

It is imperative to know what users have what privileges, especially when it comes to the almighty SYSDBA or SYSOPER privilege. To quickly determine this use this SQL script:

select * from V$PWFILE_USERS;

What’s going on behind the scenes? This query grabs data from the password file The V$ view can be viewed while the database is in any state (closed, no-mount, mount, or open).  The view will also display users with the SYSASM privilege,  this privilege is only applicable to ASM instances.

Tags: , , , , , , , ,

Because sometimes we don’t know EVERYTHING… Tutorials can fill in those blanks.

14 Feb

Sometimes we need help.. sometimes we need a lot of help.. pixel2life is a great site with TONS of tutorials to assist in every aspect of technology you can think of (or at least a lot).. It is a free site, so… there is a little sorting to be done. I’ve found the best way to weed out the junky tutorials is to click the “Most Popular” tab and start from there. Some topics have 1000+ tutorials, others have just a handful.

Adobe Photoshop? Check

Adobe Flash? Check

PHP? Check

HTML? Check 

3D Graphics (AutoCAD, Rhino3D, Bryce,etc)? Check

Audio Editing? Check

MS Office Excel, Outlook, PowerPoint,etc? Check

Databases? Check (MS Access and MySQL only.. but hey you have this for Oracle!)

C++, Python, Visual Basic, Visual C++? Check, Check, Check, Check

CSS, ColdFusion, Java, Perl, Ruby on Rails, WordPress? CHECK!

you get the point.. they’ve got tutorials.. 

http://www.pixel2life.com/

Tags: , , , ,

Oracle: SYSDATE Quickie

19 Dec

Oracle’s time and date functions are very useful but can also be tricky. One thing to always keep in mind is SYSDATE is based upon a day. If you want to break down into hours or minutes some simple math is required.

Brief overview:

SYSDATE + 1 = tomorrow
SYSDATE – 1 = yesterday
SYSDATE + 7 = week from today
SYSDATE – 10 = 10 days ago
SYSDATE + (20/1440) = 20 minutes from now
SYSDATE – (30/1440) = 30 minutes ago

Try this SQL script to display Today, Yesterday and Tomorrow’s dates:

SELECTSYSDATE Today,
SYSDATE – 1 Yesterday,
SYSDATE + 1 Tomorrow
FROM dual;

Tags: , , , , ,

Oracle: Archive(C)log — the Draino Solution (ORA-16014)

15 Dec

Auditing… great for stats not so great for space on your hard drive. Frequently people(non-technical people) say they want “all auditing” turned on… while this is necessary in a few instances, in general it should be avoided. Fine grain auditing is tricky and can cause more problems if used incorrectly(or if you don’t have a lot of HD space).If you want to give your customer/boss a rock solid reason here it is: there are TONS of processes that run in oracle 24/7 throughout the day. Every time one of these processes logs off or on or does anything different it will create a record. This may  not sound like a lot at first, but 100s of processes on and off (some thousands of times a day) really can fill up your hard drive. Without further adieu if you do find yourself in clogged up situation where you cannot even log onto your database this solution is your draino!

**As always before doing ANYTHING take a full backup of your database, just in case something goes wrong**

1. open up CMD
2. set your oracle_sid to the database you need to un-clog
3. log onto RMAN (if you ‘re not familiar with RMAN that’s a-ok we will walk through it nice and slow). To log onto RMAN simply type in CMD “RMAN”
4. next, set the target database and log in as SYS  – RMAN> target sys/password@databasename
5. you’re now all connected! lets backup the archivelog — RMAN> backup archivelog all;
6. you’re now all connected! lets crosscheck the archivelog — RMAN> crosscheck archivelog all;
7. deleting all obsolete, (cross your fingers this works) – RMAN>delete obsolete;
8. if you’re concerned about losing archivelogs at this point I’d recommend logging out of RMAN (exit) and trying to re-connect to your database. Typically your space issues will not be resolved by deleting just the obsolete.. but its worth a shot right?
9.  So… it didn’t work.. log back onto RMAN(see above) and let’s get to deleting. Most importantly think about how far back you want(or can) delete. Edit the ‘sysdate-7′ to whatever date you decide upon. For my use 1 week back was sufficient.
RMAN>delete archivelog all completed before ‘sysdate-7′;
10. Log out of RMAN (exit) and try to log back on to your database. For good measure shutdown and restart!

Tags: , , , , , , ,

How to DISABLE Oracle’s Password Expiration

21 Jun

Password locking and expiration is a useful tool, however (especially in development settings) it can be a huge pain.. If you wish to remove the password lifetime setting and the failed login attempt setting use the SQL query below.  Note: this is for the default profile, if you have users that are using a different profile you will have to also alter those profiles.

ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED;

Rapture

18 May

Finding Duplicates: Part II

14 Apr

Previously, we’ve taken a look at how to find duplicates when working with a single column..
Check out original finding duplicates post here
However, sometimes finding duplicates isn’t that simple. To find duplicate records by multiple columns check out the SQL below:

SELECT a.*
  FROM tablename a
 INNER JOIN (SELECT column1, column2, column3
               FROM tablename
              GROUP BY column1, column2, column3
             HAVING Count(*) > 1) b
    ON a.column1 = b.column1
   AND a.column2 = b.column2
   AND a.column3 = b.column3

Replace the variables with your table and column names and execute. In this example I used 3 columns, however you can do as little as 2 or as many as your table holds.

Tags: , ,

← Older Entries

Follow

Get every new post delivered to your Inbox.

Join 52 other followers