How To See Privileges In Oracle Directories

In this post I show a simple query for get privileges for users on directories. You can query those privileges by using the next query;

 
COLUMN GRANTOR       FORMAT A10
COLUMN GRANTEE       FORMAT A20
COLUMN TABLE_SCHEMA  FORMAT A10
COLUMN TABLE_NAME    FORMAT A20
COLUMN PRIVILEGE     FORMAT A10

SELECT *
FROM ALL_TAB_PRIVS
WHERE 
  TABLE_NAME IN (SELECT DIRECTORY_NAME FROM DBA_DIRECTORIES);

GRANTOR    GRANTEE              TABLE_SCHE TABLE_NAME           PRIVILEGE  GRA HIE COM TYPE
__________ ____________________ __________ ____________________ __________ ___ ___ ___ _______________
SYS        EXP_FULL_DATABASE    SYS        DATA_PUMP_DIR        READ       NO  NO  NO  DIRECTORY
SYS        EXP_FULL_DATABASE    SYS        DATA_PUMP_DIR        WRITE      NO  NO  NO  DIRECTORY
SYS        IMP_FULL_DATABASE    SYS        DATA_PUMP_DIR        READ       NO  NO  NO  DIRECTORY
SYS        IMP_FULL_DATABASE    SYS        DATA_PUMP_DIR        WRITE      NO  NO  NO  DIRECTORY

 

HTH – Antonio NAVARRO

 

Advertisements

Command sleep In CMD

A coworker asked me how I could put a sleep in a windows batch script. Sleep is a very popular command in Unix and Linux environments, it expects the number of seconds indicated as a parameter before continuing with the next instruction.

The problem is that this command does not exist in Windows, there are some implementations that do that function, although Windows implements its own command, if I remember right from Windows 7.

You can ever document it from a cmd, with timeout / ?, as shown below (I have the language of this machine currently in Spanish);

 

 
C:\>timeout /?

TIMEOUT [/T] tiempo_espera [/NOBREAK]

Descripción:
    Esta utilidad acepta un parámetro de tiempo de espera para esperar el
    un período de tiempo determinado (en segundos) o hasta que se presiona
    alguna tecla. También acepta un parámetro para omitir la presión de tecla.

Lista de parámetros:
    /T   tiempo_espera     Especifica el número de segundos que hay
                           que esperar.
                           El intervalo válido es de -1 a 99999 segundos.

    /NOBREAK               Omite las presiones de tecla y espera el tiempo
                           especificado.

    /?                     Muestra este mensaje de ayuda.

NOTA: un valor de tiempo de espera -1 significa esperar indefinidamente
una presión de teclado.

Ejemplos:
    TIMEOUT /?
    TIMEOUT /T 10
    TIMEOUT /T 300 /NOBREAK
    TIMEOUT /T -1

 

To perform a 5-second stop you would have to execute the following command. It will take out a message indicating when it needs to finish the stop.

 
C:\>timeout /t 5

Esperando 5 segundos, presione una tecla para continuar ...

HTH – Antonio NAVARRO

How To Deinstall SQLTRACE

In this post I like show the correct way to remove the sqltrace schemas and procedures. First of all move to <location_for_sqltrace_packages>/install and connect / as sysdba to the database and execute @sqdrop.sql;

output for the command;

 

 
shone-1/cloud/prod/server102/ADM/SQLT/install> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jan 29 12:17:22 2018

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> @sqdrop.sql

PL/SQL procedure successfully completed.

... uninstalling SQLT, please wait

TADOBJ completed.

PL/SQL procedure successfully completed.

SQDOLD completed. Ignore errors from this script

PL/SQL procedure successfully completed.

SQDOBJ completed. Ignore errors from this script

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    my_count INTEGER;
  3
  4  BEGIN
  5    SELECT COUNT(*)
  6      INTO my_count
  7      FROM sys.dba_users
  8     WHERE username = 'TRCADMIN';
  9
 10    IF my_count = 0 THEN
 11      BEGIN
 12        EXECUTE IMMEDIATE 'DROP PROCEDURE sys.sqlt$_trca$_dir_set';
 13      EXCEPTION
 14        WHEN OTHERS THEN
 15          DBMS_OUTPUT.PUT_LINE('Cannot drop procedure sys.sqlt$_trca$_dir_set. '||SQLERRM);
 16      END;
 17
 18      FOR i IN (SELECT directory_name
 19                  FROM sys.dba_directories
 20                 WHERE directory_name IN ('SQLT$UDUMP', 'SQLT$BDUMP', 'SQLT$STAGE', 'TRCA$INPUT1', 'TRCA$INPUT2', 'TRCA$STAGE'))
 21      LOOP
 22        BEGIN
 23          EXECUTE IMMEDIATE 'DROP DIRECTORY '||i.directory_name;
 24          DBMS_OUTPUT.PUT_LINE('Dropped directory '||i.directory_name||'.');
 25        EXCEPTION
 26          WHEN OTHERS THEN
 27            DBMS_OUTPUT.PUT_LINE('Cannot drop directory '||i.directory_name||'. '||SQLERRM);
 28        END;
 29      END LOOP;
 30    END IF;
 31  END;
 32  /
Dropped directory SQLT$STAGE.
Dropped directory TRCA$STAGE.
Dropped directory SQLT$UDUMP.
Dropped directory SQLT$BDUMP.
Dropped directory TRCA$INPUT1.
Dropped directory TRCA$INPUT2.

PL/SQL procedure successfully completed.

SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.
About to DROP users SQLTXPLAIN and SQLTXADMIN. Press RETURN to continue.

SQL>
SQL> DROP USER &&tool_administer_schema. CASCADE;
old   1: DROP USER &&tool_administer_schema. CASCADE
new   1: DROP USER SQLTXADMIN CASCADE

User dropped.

SQL> DROP USER &&tool_repository_schema. CASCADE;
old   1: DROP USER &&tool_repository_schema. CASCADE
new   1: DROP USER SQLTXPLAIN CASCADE

User dropped.

SQL> DROP ROLE &&role_name.;
old   1: DROP ROLE &&role_name.
new   1: DROP ROLE SQLT_USER_ROLE

Role dropped.

SQL>
SQL> SET ECHO OFF;

SQDUSR completed.

SQDROP completed.
SQL>
SQL>

 

HTH – Antonio NAVARRO

ORA-31693, ORA-29913, ORA-29400, KUP-11011, KUP-11014 When Executing Impdp

Today I get the next error when perform a full import.

 
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-31693: Table data object "CLOUD"."HCO_TELMEM_BRO" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11011: the following file is not valid for this load operation
KUP-11014: internal metadata in file /temporary/uploads/fullcloud33.dmp is not valid

It is a bug (20690515), by use multiples dump files, generated in 10g, maybe trying reduce their number by create them more bigger work. If you hava support you can download from ML/MOS. In other case, you can use like workaround, if your tables support it, set up ACCESS_METHOD=DIRECT_PATH.

HTH – Antonio NAVARRO

 

ORA-31603 Error When Get DDL For A Profile

Today I was getting the DDL for differents objects in a database, the problem is when I get the Profiles definition, by using the next query;

 
SELECT DBMS_METADATA.GET_DDL('PROFILE',PROFILE)
 FROM (SELECT UNIQUE PROFILE FROM DBA_PROFILES);  

ERROR:

ORA-31603: object "DEFAULT" of type PROFILE not found in schema "SYS"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

The problem is the default profile. It is make not sense export (import) and default profile fron one database to other. The solution is exclude the default profile like show below;

 
SELECT DBMS_METADATA.GET_DDL('PROFILE',PROFILE)
FROM (SELECT UNIQUE PROFILE FROM DBA_PROFILES)
WHERE profile != 'DEFAULT';

HTH – Antonio NAVARRO

 

 

ORA-32004 And ORA-27102 After Increase Memory

Today I was increasing the Memory_target for a database when I get the next error;

SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-27102: out of memory
SVR4 Error: 12: Not enough space
Additional information: 1671
Additional information: 16106127360
Additional information: 12851347456

The problem looks clear, Not enough space, of course, in my case (using Solaris 11.3 with ZFS) this is a well know problem, if ZFS is on default it is very easy use all physical memory;

You can see the memory consume from root user executin the next command

echo ::memstat | mdb -k

The output is below, in this case the 56% of memroy is used by ZFS

Page Summary                 Pages             Bytes  %Tot
----------------- ----------------  ----------------  ----
Kernel                     1551129             11.8G   18%
Defdump prealloc            182104              1.3G    2%
ZFS Metadata                258844              1.9G    3%
ZFS File Data              4657165             35.5G   56%
Anon                        637853              4.8G    8%
Exec and libs                97157            759.0M    1%
Page cache                   57244            447.2M    1%
OSM                         469504              3.5G    6%
Free (cachelist)             14532            113.5M    0%
Free (freelist)             530445              4.0G    6%
Total                      8388608               64G

From ZFS Doc;

zfs_arc_max

Description
Determines the maximum size of the ZFS Adaptive Replacement Cache (ARC). However, see user_reserve_hint_pct.

Default
75% of memory on systems with less than 4 GB of memory

physmem minus 1 GB on systems with greater than 4 GB of memory

Free the memory in used by ZFS and limit it solve the problem.

 

HTH – Antonio NAVARRO