Table Information - including: rows counts, column counts and size

The script below can be run to get a Row Count (from Stats), Column Count and size (in MEG) for all Tables in a Schema:

select 
   a.table_name, 
   a.num_rows ROW_count_FROM_STATS,
   b.col_count,
   C.MEG
from 
   dba_tables a, (select TABLE_NAME, count(column_name) as col_count from dba_tab_cols where owner='&owner' GROUP BY TABLE_NAME
ORDER BY TABLE_NAME) b,
(SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type = 'TABLE' and owner = '&owner'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 5  /* Ignore really small tables */
ORDER BY SUM(bytes) desc) C
where 
   a.owner = '&owner'
   and A.TABLE_NAME = b.table_name(+)
   AND A.TABLE_NAME = C.TABLE_NAME(+)
order by 
   a.table_name;

Proxy User in Oracle

If you find yourself with the requirement to allow a user to create objects in another user's schema, you may want to take a look at Proxy User functionality.

As a test, I created a new user (abtest) and, as expected, was not able to create an object in my regular (aballo) Schema. You can perhaps grant ‘EXECUTE PROCEDURE’ and do the DDL in the statement (which is messy)….

I then tried logging is as “ABTEST” as a Proxy User for “ABALLO” and this DID appear to work. A test is below…  maybe this will work for your requirements? This is also useful when you don't want to give out a particular user's password.

16:43:11 ODST> create user abtest

16:43:31   2  identified by pwd4ab

16:43:44   3  default tablespace users

16:44:35   4  temporary tablespace temp_group1

16:45:11   5  quota 1m on users;

User created.

17:00:48 ODST> grant create session,create table to abtest;

Grant succeeded.

17:00:53 ODST> connect abtest/pwd4ab@odst;

Connected.

17:01:46 ODST> CREATE TABLE ABALLO.T2

17:01:47   2  (

17:01:47   3    N1  NUMBER,

17:01:47   4    N2  NUMBER

17:01:47   5  )

17:01:47   6  TABLESPACE USERS

17:01:47   7  RESULT_CACHE (MODE DEFAULT)

17:01:47   8  PCTUSED    0

17:01:47   9  PCTFREE    1

17:01:47  10  INITRANS   1

17:01:47  11  MAXTRANS   255

17:01:47  12  STORAGE    (

17:01:47  13              PCTINCREASE      0

17:01:47  14              BUFFER_POOL      DEFAULT

17:01:47  15              FLASH_CACHE      DEFAULT

17:01:47  16              CELL_FLASH_CACHE DEFAULT

17:01:47  17             )

17:01:47  18  LOGGING

17:01:47  19  NOCOMPRESS

17:01:47  20  NOCACHE

17:01:47  21  NOPARALLEL

17:01:47  22  MONITORING;

CREATE TABLE ABALLO.T2

*

ERROR at line 1:

ORA-01031: insufficient privileges

17:13:02 ODST> connect ###mgr/##########@odst

Connected.

17:24:39 ODST> alter user aballo grant connect through abtest;

User altered.

17:28:44 ODST> connect abtest[aballo]/pwd4ab@odst;

Connected.

17:29:22 ODST> CREATE TABLE ABALLO.T2

17:29:27   2  (

17:29:27   3    N1  NUMBER,

17:29:27   4    N2  NUMBER

17:29:27   5  )

17:29:27   6  TABLESPACE USERS

17:29:27   7  RESULT_CACHE (MODE DEFAULT)

17:29:27   8  PCTUSED    0

17:29:27   9  PCTFREE    1

17:29:27  10  INITRANS   1

17:29:27  11  MAXTRANS   255

17:29:27  12  STORAGE    (

17:29:27  13              PCTINCREASE      0

17:29:27  14              BUFFER_POOL      DEFAULT

17:29:27  15              FLASH_CACHE      DEFAULT

17:29:27  16              CELL_FLASH_CACHE DEFAULT

17:29:27  17             )

17:29:27  18  LOGGING

17:29:27  19  NOCOMPRESS

17:29:27  20  NOCACHE

17:29:27  21  NOPARALLEL

17:29:27  22  MONITORING;

Table created.

17:51:44 ODST> select user from dual;

USER

------------------------------

ABALLO

17:51:59 ODST> select sys_context('userenv','proxy_user') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')

--------------------------------------------------------------------------

ABTEST

17:52:05 ODST>

Capturing Elapsed Time of a SQL Statement

Looking to create a dashboard for our BI Tool that will enable me to capture report run times and display a red/yellow/green light for our reporting sub system to end users. 

My first task is to create (and schedule) a sample report that calculates run time deltas. 


COLUMN start_time NEW_VALUE start
SELECT systimestamp(9) start_time FROM dual;

<your sql statement goes here>

COLUMN end_time NEW_VALUE end 
SELECT systimestamp(9) end_time FROM dual; 
Column elapsed_time NEW_VALUE elapsed 
SELECT TO_TIMESTAMP_TZ('&end', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') - TO_TIMESTAMP_TZ('&start', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') elapsed FROM dual;  

My time output looks like this:

START_TIME                         

-----------------------------------

4/18/2013 1:10:30.758782000 PM -06:

00                                 

                                                                                

1 row selected.

END_TIME                           

-----------------------------------

4/18/2013 1:10:30.873328000 PM -06:

00                                 

                                                                                

1 row selected.

old: SELECT TO_TIMESTAMP_TZ('&end', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') - TO_TIMESTAMP_TZ('&start', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') elapsed FROM dual

new: SELECT TO_TIMESTAMP_TZ('4/18/2013 1:10:30.873328000 PM -06:00', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') - TO_TIMESTAMP_TZ('4/18/2013 1:10:30.758782000 PM -06:00', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') elapsed FROM dual

ELAPSED                                           

--------------------------------------------------

+00 00:00:00.114546

                             

1 row selected.

Now you can capture this run-time  maybe add a few other columns of useful information, and store in a Table that a performance metric dashboard report reads from.