topic Re: Contents of Oracle Library/Tables ...No. of Obs in a Oracle Table in SAS Procedures

Contents of Oracle Library/Tables ...No. of Obs in a Oracle Table

GPatel — Thu, 02 Jun 2011 19:15:14 GMT

Code below gives me table information except for number of physical observations for all the tables in Justice Oracle library.

How can I get list of library name, member name and no. of records in each table?

LIBNAME justice ORACLE
PATH=Total.world
SCHEMA=usa
USER=xxxxx
PASSWORD="xxxxxxx"
READBUFF=1
;

Proc Sql ;
create table test1 as
select libname, memname, nobs
from dictionary.tables
where libname='JUSTICE' ;
quit ;

Re: Contents of Oracle Library/Tables ...No. of Obs in a Oracle Table

polingjw — Thu, 02 Jun 2011 20:15:39 GMT

If your DBA has given you the appropriate permissions, you can query the Oracle system tables. Use “SYS” as the schema when you assign a library to the system tables:

LIBNAME justSYS ORACLE
PATH=Total.world
SCHEMA=’SYS’
USER=xxxxx
PASSWORD="xxxxxxx";

You should be able to query the justSYS.ALL_TABLES view to obtain the number of observations in a given table. Specifically, look at using the TABLE_NAME and NUM_ROWS variables.

Re: Contents of Oracle Library/Tables ...No. of Obs in a Oracle Table

Ksharp — Fri, 03 Jun 2011 02:43:23 GMT

Another way.
If you want, using proc copy or proc cport to copy these tables from Oracle to local computer,then use your code to query.But It is low efficient.

Ksharp