How to list all users and their number of owned objects

Join dba_users with dba_objects using a correlated subquery, and you'll get all the schemas, both those which own objects and those who don't:

set lines 200
col username format a20
col created format a30
col "Num_obj" format 999999
SELECT u.username,u.created,(SELECT COUNT(*) FROM dba_objects o WHERE o.owner = u.username)"Num_obj"
FROM dba_users u
-- to leave out all oracle maintained schemas, comment out the following
-- WHERE  u.oracle_maintained = 'N'
ORDER BY 3 asc;

Example output:

USERNAME             CREATED                        Num_obj
-------------------- ------------------------------ -------
ANONYMOUS            14.06.2016                           0
ADDM_USER            14.06.2016                           0
GSMUSER              14.06.2016                           0
SYSDG                14.06.2016                           0
DIP                  14.06.2016                           0
SYSBACKUP            14.06.2016                           0
SCOTT                14.06.2016                           0
USER1                20.06.2016                           0
USER2                20.06.2016                           0
SYSKM                14.06.2016                           0
XS$NULL              14.06.2016                           0
GSMCATUSER           14.06.2016                           0
APPQOSSYS            14.06.2016                           5
ORACLE_OCM           14.06.2016                           6
OUTLN                14.06.2016                          10
AUDSYS               14.06.2016                          12
OJVMSYS              14.06.2016                          16
USER3                20.06.2016                          34
DBSNMP               14.06.2016                          55
USER4                20.06.2016                          95
WMSYS                14.06.2016                         389
CTXSYS               14.06.2016                         409
SYSTEM               14.06.2016                         641
XDB                  14.06.2016                         961
SYS                  14.06.2016                       42173

If you have a large number of accounts that own no objects at all, you may want to exclude them. Do so by checking for the existence of the particular account in dba_objects.
If there are any rows at all in there, the account owns at least 1 object. Make sure to select a literal, not a value from the database. This is good practice; performance is not affected:

SELECT u.username,u.created,(SELECT COUNT(*) FROM dba_objects o WHERE o.owner = u.username )"Num_obj"
FROM dba_users u
WHERE EXISTS (SELECT 1 FROM dba_objects o WHERE o.owner = u.username)
ORDER BY 3 asc;

The RANK function for DBAs

The analytic function RANK is perhaps not the most commonly used by DBAs.
For what it's worth, here is a short demonstration:

1. Find the number of rows for all the tables in a schema:

select table_name, num_rows 
from dba_tables 
where owner='SYSTEM' 
and num_rows >0 
ORDER BY NUM_ROWS;

The above simple query results in:

TABLE_NAME                      NUM_ROWS
------------------------------ ---------
REDO_DB                                1
REPCAT$_TEMPLATE_TYPES                 2
REPCAT$_AUDIT_ATTRIBUTE                2
REPCAT$_TEMPLATE_STATUS                3
AQ$_INTERNET_AGENT_PRIVS               4
AQ$_INTERNET_AGENTS                    6
AQ$_QUEUE_TABLES                      15
REPCAT$_RESOLUTION_METHOD             19
AQ$_QUEUES                            28
REPCAT$_OBJECT_TYPES                  28
MVIEW$_ADV_PARAMETERS                 40
LOGSTDBY$SKIP_SUPPORT                351
HELP                                 938

13 rows selected.

To check where in the sort order a table with, say, 10 rows, would be, use the RANK function:

select RANK(10) WITHIN GROUP(order by num_rows)"Rank" 
from dba_tables 
where owner='SYSTEM' 
and num_rows >0;

      Rank
----------
         7

A table with 10 rows would be on 7th place, in the sort order shown above (ascending by default), squeezed in between the tables AQ$_INTERNET_AGENTS and AQ$_QUEUE_TABLES.

How to compare the number of objects per schema in two databases

After a migration of a database to a new server, or after any kind of replication, you would like to get some verification that your objects were created.

A potentially very useful query could help you with this task.

You need a database link from the target database back to the source database

create public database link proddb01_old 
connect to system
identified by mysecretpassword
using 'myproddb01';

Verify that the link works as intended:

SQL> select d.name,i.host_name from v$database d, v$instance i;

NAME      HOST_NAME
--------- -------------------------------
PRODDB01 myserver01.mydomain.com

Save the following in a script, and execute it from the target database via sqlplus:

set lines 200
col count_new format 99999
col count_old format 99999
col "object owner" format a30
col "Result" format a20
set pages 100

prompt ============================================
prompt compare users and their object count
prompt ============================================

WITH newprod_users AS(
 SELECT target.owner AS new_owner, count(*) AS count_new
 FROM   dba_objects target
 GROUP BY owner
 ORDER BY 1
),
oldprod_users AS (
 SELECT source.owner AS old_owner, count(*) AS count_old
 FROM   dba_objects@proddb01_old  source
 GROUP BY owner
 ORDER BY 1)
SELECT newprod_users.new_owner "object owner", 
       newprod_users.count_new "NEW PROD",  
       oldprod_users.count_old "OLD PROD",
       DECODE(newprod_users.count_new,
              oldprod_users.count_old,'Num rows identical','Num row differs') "Result"
FROM   newprod_users JOIN oldprod_users
ON     newprod_users.new_owner = oldprod_users.old_owner
ORDER BY "Result" DESC;

Example output:

============================================
compare number of users
============================================

object owner                     NEW PROD  OLD_PROD Result
------------------------------ ---------- ---------- --------------------

USER1                               1329       1329 Num rows identical
USER2                                  2          2 Num rows identical
USER3                               1750       1750 Num rows identical
USER4                                409        389 Num row differs
USER5                                961       1167 Num row differs
USER6                                251        256 Num row differs

The report can be easily modified to produce for example a semi-colon separated list, which can in turn be pulled into an MS Excel file if desirable.

How to handle message "Expected XDB Resources are not there" when OLAP API is invalid after an upgrade

Background:
After an upgrade, you have invalid components in you registry.
To fix the OLAP components you have followed "Oracle Olap API Invalid After Adding or Upgrading OLAP (Doc ID 466363.1)" and created the

xoq_validate_verbose

procedure in the SYS schema.

You run it with the following output:

exec xoq_validate_verbose
compatible:11.2.0.4.0 ok:True
No install errors were found so component remains valid. ok:True
expected XDB resources are not there. ok:False

PL/SQL procedure successfully completed. 

Now, reload the OLAP API:

sqlplus / as sysdba@$ORACLE_HOME/olap/admin/xoqrelod.sql 

If the output from the above script shows that the index XDB.XDB$ACL_XIDX is disabled, make sure to enable it first:

alter index xdb.XDB$ACL_XIDX enable;

Then rerun the xoqrelod.sql script.


Check the registry, OLAP API should now be valid:

select comp_id, comp_name, version,status 
from dba_registry
where comp_id='XOQ'
/

COMP_ID              COMP_NAME                                VERSION                        STATUS
-------------------- ---------------------------------------- ------------------------------ -----------
XOQ                  Oracle OLAP API                          11.2.0.4.0                     VALID

How to solve ORA-28017: The password file is in the legacy format

SQL> alter user sys identified by iossS1Qwmk_kKfGHqs0UVu93xxswQ;
alter user sys identified by iossS1Qwmk_kKfGHqs0UVu93xxswQ
*
ERROR at line 1:
ORA-28017: The password file is in the legacy format.

Cause:

#oerr ora 28017
28017, 00000, "The password file is in the legacy format."
// *Cause:    There are multiple possibilities for the cause of the error:
//
//              * An attempt was made to grant SYSBACKUP, SYSDG or SYSKM.
//              * These administrative privileges could not be granted unless
//                the password file used a newer format ("12" or higher).
//              * An attempt was made to grant a privilege to a user who
//                has a large password hash which cannot be stored in
//                the password file unless the password file uses a newer
//                format ("12" or higher).
//              * An attempt was made to grant or revoke a common administrative
//                privilege in a CDB
// *Action:   Regenerate the password file in the newer format ("12" or higher).
//            Use the newer password file format ("12" or higher) if you need to
//            grant a user the SYSBACKUP, SYSDG or SYSKM administrative
//            privileges, or if you need to grant a privilege to a user
//            who has a large password hash value.

Cause: the password file is of the wrong format. You typically see this error after a migration from 11g to 12c.

Solution: regenerate the password file.

First, check the users that are affected by the change:

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
DBAMON                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

In my case, two users are affected: SYS and DBAMON.


Regenerate the password file:

# orapwd file=$ORACLE_HOME/dbs/orapwproddb01 entries=5 force=y

Enter password for SYS:

Check the password file users again. SYS was added as a result of the recreation of the password file:

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

To add DBAMON as a sysdba user, grant the SYSDBA privilege to the account:

SQL> grant sysdba to DBAMON;

Grant succeeded.

Check again, and DBAMON is now registered as a privileged user in the password file:

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
DBAMON                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

Simple sed-command for exchanging a string in multiple files

sed -i "s/oldstring/newstring/g" 

For example

sed -i "s/11204/12102/g" /etc/oratab

MS word characters that have no representation in WE8ISO8859P1 and WE8ISO8859P15

During a globalization effort, I found the following interesting information about the difference between the WE8MSWIN1252 and the WE8ISO8859P15 character set

* 27 codepoints are NOT defined/used in WE8ISO8859P15 but are filled in / used in WE8MSWIN1252

(note that the WE8MSWIN1252 codepoints

* 91 = U+2018 : LEFT SINGLE QUOTATION MARK
* 92 = U+2019 : RIGHT SINGLE QUOTATION MARK
* 93 = U+201C : LEFT DOUBLE QUOTATION MARK
* 94 = U+201D : RIGHT DOUBLE QUOTATION MARK

are the default quotation marks of the Microsoft Word product, so if you have data that comes from Microsoft Office products you *need* a WE8MSWIN1252 database characterset.

Make sure you clients NLS_LANG is also correct: WE8ISO8859P15 is *not* correct as NLS_LANG for windows clients
Note 179133.1 The correct NLS_LANG in a Windows Environment


A more common problem is that in an environment using English and West European or Latin American ( French, Spanish, Portuguese, Dutch, Italian,...) windows clients. a lot of setups use a NLS_LANG set to WE8ISO8859P15 on the client side. For windows systems this is not correct and provokes in most cases that there are actually WE8MSWIN1252 codes stored in the WE8ISO8859P15 database. The most commonly seen characters are the € symbol and these qoutes: ‘’“” - these are the 1252 "smart qoutes" used in Microsoft Office. They look similar to the "normal" US7ASCII qoute " in most fonts, but are different characters often and result in confusion. The Courrier New font for example distinct them quite good visibly.


So watch out for cut-n-paste errors based on MS Word documents! They often result in characters that have no representation under the most commonly used non-Unicode character set.