Oracle - Count all rows in all tables in a database

 Count all rows in all tables in a database

CREATE GLOBAL TEMPORARY TABLE TMP_TOTALES (
  TABLA VARCHAR2(32),
  TOTAL NUMBER
)
ON COMMIT DELETE ROWS;
         
         

set serveroutput ON;
DECLARE        
          total NUMBER:=NULL;
          cursor tablas_cursor IS select TABLE_NAME from all_Tables where owner = 'myUser' AND NOT table_name like '%$%' ORDER BY TABLE_NAME ASC;
           
          v_sqltxt        varchar2(32767);
          no_privs        exception;
         pragma exception_init(no_privs, -1031);
 BEGIN
     FOR tabla IN tablas_cursor loop
        v_sqltxt:='select count(1) from '|| tabla.TABLE_NAME ||'';      
        BEGIN
          EXECUTE immediate v_sqltxt INTO total;
          INSERT INTO TMP_TOTALES(TABLA, TOTAL) VALUES (tabla.TABLE_NAME, total);
          dbms_output.put_line(tabla.TABLE_NAME || ' ' || total);                  
         END;     
      END loop;        
END;
/


select * from TMP_TOTALES ORDER BY TOTAL DESC;

Oracle DB - Check tablespaces usage

 Tablespaces and datafiles usage:

select
    DTFIL.tablespace_name, DTFIL.file_name,
    ROUND(DTFIL.bytes/power(1024,2),2) USED_MB,
    DTFIL.autoextensible,
    Round(DTFIL.maxbytes/power(1024,3), 2) Maxsize_GB,
    Round((DTFIL.bytes / DTFIL.maxbytes)*100, 2) USED,
    round((DTFIL.increment_by * TBSP.block_size)/power(1024,2), 2) step_MB,
    DTFIL.increment_by
from
    dba_data_files DTFIL join dba_tablespaces TBSP on DTFIL.TABLESPACE_NAME = TBSP.TABLESPACE_NAME
order by DTFIL.tablespace_name, DTFIL.file_name;

Tablespaces usage, including datafiles:

select
    DTFIL.tablespace_name,
    Round((sum(DTFIL.bytes) / sum(DTFIL.maxbytes))*100, 2) USED
from
    dba_data_files DTFIL join dba_tablespaces TBSP on DTFIL.TABLESPACE_NAME = TBSP.TABLESPACE_NAME
group by DTFIL.tablespace_name
order by 2 desc;

Tablespaces usage acording with actual allocated space:

-- Work tablespaces
SELECT
       a.tablespace_name AS tablespace_name,
       (100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc)* 100)) percent_used,
       (100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc)* 100, 2)) pct_used,
       round(a.bytes_alloc / 1024/ 1024,2) allocated,
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024/ 1024,2) used,      
       round(nvl(b.bytes_free, 0) / 1024/ 1024,2) free,
       a.datafiles
from (select
        f.tablespace_name,
        sum(f.bytes) bytes_alloc,
        sum(decode(f.autoextensible, 'yes', f.maxbytes, 'N0',f.bytes)) maxbytes,
        count(1) datafiles
      FROM
        dba_data_files f
      GROUP BY tablespace_name) a,
    (SELECT 
        f.tablespace_name,
        SUM(f.bytes) bytes_free
     FROM
        dba_free_space f
     GROUP BY tablespace_name) b
WHERE
    a.tablespace_name = b.tablespace_name(+)
 
UNION ALL
 
-- TEMP Tablespaces
select
    t.tablespace_name,
    t.percent_used,
    t.pct_used,
    t.allocated,
    t.used,
    t.free,
    f.datafiles
from
    (select
        h.tablespace_name as tablespace_name,
        (100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free))* 100)) percent_used,
        (100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free))* 100,2)) pct_used,
        round(sum(h.bytes_free + h.bytes_used) / 1048576,2) allocated,
        round(sum(nvl(p.bytes_used, 0))/ 1048576,2) used,
        round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576,2) free
    from
        sys.gv_$TEMP_SPACE_HEADER h,
        sys.gv_$Temp_extent_pool p,
        dba_temp_files f
        where
            p.file_id(+) = h.file_id
            and p.tablespace_name(+) = h.tablespace_name
            and f.file_id = h.file_id
            and f.tablespace_name = h.tablespace_name
        group by h.tablespace_name ) t,
    (select tablespace_name, count(1) datafiles from dba_temp_files group by tablespace_name ) f
where
    t.tablespace_name = f.tablespace_name
ORDER BY 3 desc

Oracle DB - SQLPLUS connect

Setup

System properties configuration

export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin

Connection

Connect with tnsnames from command line.

sqlplus username/pass@'(description=(address=(protocol=tcp)(host=myServer)(port=1521))(connect_data=(sid=mySID)))'

Connect with tnsnames from file

sqlplus username/pass@ORCL

Where tnsnames.ora file is as follows.

ORCL=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myServer)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myService)
    )
  )

Run SQL file

Run SQL from command line

sqlplus username/pass@'(description=(address=(protocol=tcp)(host=myServer)(port=1521))(connect_data=(sid=mySID)))' @myFile.sql

SQL file

set linesize 100 pagesize 50

select
    DTFIL.tablespace_name,
    Round((sum(DTFIL.bytes) / sum(DTFIL.maxbytes))*100, 2) USED
from
    dba_data_files DTFIL join dba_tablespaces TBSP on DTFIL.TABLESPACE_NAME = TBSP.TABLESPACE_NAME
group by DTFIL.tablespace_name
order by 2 desc;

exit;

ORA-00942 Table or View Does Not Exist - Identify target table

During the installation of a third party application which uses Oracle Database, I found the following error in server log:

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

The error was clear, the application was trying to execute an SQL statement against a table that was not in database, or the user did not have permissions. But, which table or view was that?

In order to discover the SQL the client was invoking I found this post: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2975793633621#284628100346267414. In my post I´ll just refactor it in order to easy understanding.

The idea behind of this is to register a trigger for server error 942, obtain  context information and the sql being executed, and save all this info in other table. The complete code is as follow:

-- Table to store errors
create table ERROR942 (id number, 
    "time" timestamp,
    "event" varchar(100), 
    "user" varchar(100),
    "server_error" varchar2(4000),
    "sql" varchar2(4000) );

-- Sequence to generate the table id
create sequence SEQ_ERROR942;


-- Trigger to insert errors in table
create or replace trigger TGR_SERVER942
after servererror on database
declare
   l_sql_text ora_name_list_t;
   full_sql varchar2(4000);
   l_n number;
begin
    if ( is_servererror(942) )
    then        
        full_sql := '';
        l_n := ora_sql_txt( l_sql_text );
        for i in 1 .. l_n
        loop
           full_sql := full_sql || l_sql_text(i);
        end loop;
        
         insert into ERROR942 values (SEQ_ERROR942.nextval, current_date, ora_sysevent, ora_login_user,  ora_server_error(1), full_sql);
    end if;
end;

commit;

The main aspects here are:

• We create ERROR942 table in order to store all errors
• We create SEQ_ERROR942 in order to generate the previous table ids
• We create TGR_SERVER942 trigger in order to capture server errors and store then in the table. 

You can test the solution as follow:

select * from UNEXIST_TABLE;

select * ERROR942;

Oracle - Avoid user password expiration

When you connect to Oracle you see this warning: "The password wil expire within %s days"

Check all profiles:

select * from dba_profiles

Check the profile assigned to one user:

select profile from DBA_USERS where username = 'MyUser';

For this profile, filter its limits:

select resource_name,limit from dba_profiles where profile='DEFAULT';

Check user account status and expiration date for this user:

select username, account_status, expiry_date from dba_users where username='MyUser';

With this information we know that our user is assigned to the Default profile, which has an expiration policy of 180 days. Also, this user´s password will expire on 09/01/2018. To avoid this, just update the profile´s policy.

alter profile DEFAULT limit PASSWORD_LIFE_TIME  unlimited;

The last thing to do is reset the password to its own value, in order to restart the account status to OPEN. Depending on your Oracle version, you can obtain this password by two means:

select password from dba_users where username='MyUser';

select spare4 from sys.user$ where name='MyUser';

Only one of them will return a non NULL value. Then update the user.

alter user MyUser identified by values 'S:9090878D...;T:193A657B...';

Oracle 11g - Export/Import as different user

Origin Machine

• User to export: user1
• System credentials: SYSTEM/pass1

Execute de following command:

exp system/pass1 FILE=user1.dmp OWNER=user1 LOG=user1-dump.log

Now you have the dump file, wich contains all user database info (views includes). In order to see the text content you can use this command:

strings user1.dmp | more

If you also need to export de user itself, you can use de followings SQL commands;

SQL> CONNECT SYSTEM/pass1;

SQL> SELECT DBMS_METADATA.GET_DDL('USER','SCOTT') FROM dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT') from dual;

Destination Machine

• User to import: user2
• System credentials: SYSTEM/pass2

The user2 is empty.

Execute de following command:

imp system/pass2 FILE=user1.dmp FROMUSER=user1 TOUSER=user2 LOG=user2-load.log