Many applications will create user accounts in the database that do not own anything. They have privs to query the main application schema, but will never be used to create tables, views, etc.
When you’re browsing the database via the Tree in SQL Developer, you probably won’t want to see these ‘fake’ users.
Just Apply a Filter
Make sure your data dictionary stats are up to date – this extra check for object counts could get expensive, esp if you have thousands of accounts.
Remember, if you want to administer these accounts, use the DBA panel.
10 Comments
It would be nice if Oracle database got out of the 20th century and supported schemas and users as separate concepts (I know this radical). Oracle database is starting to look very old and brittle.
And I realize this has zero to do with SQL Developer, but I have limited ways to influence Oracle.
Yes, b/c having schemas and users never made life difficult in the MSFT space either…like restoring backups only to learn you have no logins?
Why give the user an account at all if they don’t need one? Let the application sort it out.
You at least have your multiple databases per server install, identity columns, implicit cursors for procedural calls, and limit (fetch first X rows only)features in 12c now – you’re still going to hang onto us being ‘old and brittle?’
I just do what the customers want. 12c is phenomenal, but there are many pieces that need some love that are being ignored. I know first hand that some of these issues are very difficult for Oracle Co. to tackle, but not doing so will hurt some day. 🙂
I’m listening, so why be so vague? Tell me what you are looking for, otherwise I’ll assume you’re just a troll 🙂
You want the list.
OK.
Many of things are simple.
First varchars should be able to store a zero length string. We have to code around this for Oracle. Every other datavbase supports it.
Second (and no one does this, but Oracle owns Java and SQL):
select * from TABLE where column IN (:collection).
I have to write silly code to handle a collection.
PL/SQL dbms_output.put_line should write the output to the current device IMMEDIATELY. What does outputting a line have to do with a transaction?
Plans should include the schema. If I have two sematically identical schemas, one with a small amount of data and the other with a huge amount of data, I need different plans (probably) for the same SQL statement. This is huge in hosted environments where multiple customers are hosted on a single instance.
When I worked for Oracle, a long long time ago, there was a HUGE emphasis on ease of use that just seems to have been lost.
I’ll name another: UUIDs. SQL Server has created a data type for UUIDS. Oracle chose to just use RAW(32). While RAW(32) works to store the data, it is a pain. I have to write code to format the output like a UUID. And if I have a nicely formatted UUID I have to strip out the hyphens to make it consumable by Oracle.
Now granted, none of these things are huge (except maybe the awful plans when you have multiple identical schemas). But all these things add up.
Oh, I just remembered another huge one. Schema backup (aka Data Pump). How do you restore this? Oh, it is by trial and error. The format does not give you any hint as to how to perform the import. So I had to write code for our Oracle customers to do a DP EXP in a predictable way so that when we import it we know how. (And we have to get extra information). For SQL Server, the customer simple detaches the database and copies the files or they backup the database. Either one is simple and I can tell how to restore it by simply looking at the extension.
I could go one for days, weeks, or months. In many cases no RDBMS does something well, but Oracle is the leader and they own Java. So I expect more.
>> Yes, b/c having schemas and users never made life difficult in the MSFT space either…like restoring backups only to learn you have no logins?
Thats like saying you cant restore both Tables and Views through the same backup mechanism. Obviously a separation between users and schemas should not impact the ability to restore a Database through a backup. See http://en.wikipedia.org/wiki/Separation_of_concerns . Users and (Data)Schemas are 2 different concerns and should be separated.
I remember matching up users and logins when restoring databases in SQL Server being a real PITA, but maybe I’m not remembering correctly.
The “Object Count = 0” filter appears to exclude schemas with packages if they have no tables, views etc. Is there an option to “include packages in Object Count”?
(SQL Developer 4.0.2)
it shouldn’t be excluding your user in that scenario, here’s what we’re running
select * from (select USERNAME
FROM SYS.ALL_USERS au
WHERE au.USERNAME != USER
) WHERE (select count(1) from all_objects where USERNAME = OWNER) > :OBJECT_COUNT;
object types ‘PACKAGE’ and ‘PACKAGE BODY’ are both tracked in ALL_OBJECTS
Oops – my bad.