Snowflake : spool output in log file

In almost all databases features, we have a spool file option, we will discuss the same spool option in snowflake

Assume, you need to execute the script which is a little long and the output can not be captured in a single screenshot, so we can make use of spooling the output in the log file. 

once you are ready to execute the script, browse to the location where you want to save a log file, in the snowsql command prompt use the below option (!spool on/off)

C:\Users>cd C:\Users\database

C:\Users\database>

C:\Users\database>snowsql -a **** -u technosnow

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>!spool snowflake_first_log.log

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>SELECT CURRENT_DATABASE();

+--------------------+

| CURRENT_DATABASE() |

|--------------------|

| SNOWFLAKE          |

+--------------------+

1 Row(s) produced. Time Elapsed: 0.503s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>SELECT CURRENT_SCHEMA();

+--------------------+

| CURRENT_SCHEMA()   |

|--------------------|

| INFORMATION_SCHEMA |

+--------------------+

1 Row(s) produced. Time Elapsed: 0.509s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>!spool off;

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>

In this way, you can re-direct the output of scripts executed in the snowflake database

Read More

MongoDB : How to get config file and log file location

In MongoDB configuration files and log files are important files as far as the daily routine for DBA. We may need to check parameters in the configuration and file and errors in log files. So here we will discuss where and how to check the location of these files

Configuration files (mongod.conf) 

The below command will help to list different files and their location, once you run it, the output will look like the below, but make sure you run it through the admin database, you can see highlighted part shows the location and name of the MongoDB configuration file

[Also read - Database and Collections]

db.runCommand({getCmdLineOpts:1})

> use admin

switched to db admin

> db.runCommand({getCmdLineOpts:1});

{

        "argv" : [

                "C:\\Program Files\\MongoDB\\Server\\5.0\\bin\\mongod.exe",

                "--config",

                "C:\\Program Files\\MongoDB\\Server\\5.0\\bin\\mongod.cfg",

                "--service"

        ],

        "parsed" : {

                "config" : "C:\\Program Files\\MongoDB\\Server\\5.0\\bin\\mongod.cfg",

                "net" : {

                        "bindIp" : "127.0.0.1",

                        "port" : 27017

                },

                "service" : true,

                "storage" : {

                        "dbPath" : "C:\\Program Files\\MongoDB\\Server\\5.0\\data",

                        "journal" : {

                                "enabled" : true

                        }

                },

                "systemLog" : {

                        "destination" : "file",

                        "logAppend" : true,

                        "path" : "C:\\Program Files\\MongoDB\\Server\\5.0\\log\\mongod.log"

                }

        },

        "ok" : 1

}

> 

Log file

In this same command above, you can see the parameter "systemLog" and location is also given there only, marked in red color. 

There is one other to check log file location using mongod.conf file, in the configuration file, you can below the parameter 

# where to write logging data.

systemLog:

  destination: file

  logAppend: true

  path:  C:\Program Files\MongoDB\Server\5.0\log\mongod.log

 

Read More

Granting access on dynamic views (ORA-02030: can only select from fixed tables/views)

There were situations when the application team asks for access to dynamics views such as V$SESSION or similar dynamic views. Access on V$SESSION is required in order to monitor sessions from the application end, so this is a common request

Here, we will discuss, granting access to dynamic views, if you try to grant access to these dynamic views, it will throw ORA error ORA-02030 as snipped below

SQL> grant select on v$session to tech_owner;

grant select on v$session to tech_owner

*

ERROR at line 1:

ORA-02030: can only select from fixed tables/views 

SQL> 

The meaning of this error is, we can assign select access to tables and views only. We will see the object type of this V$SESSION dynamic view 

[Also Read - Oracle SCN]

SQL> select owner, object_name, object_type from dba_objects where object_Name='V$SESSION';

OWNER        OBJECT_NAME          OBJECT_TYPE

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

PUBLIC       V$SESSION            SYNONYM

We can see, V$SESSION is neither view nor table, now we will see base object of this synonym

SQL> select owner, synonym_name, table_owner,

  2  table_name from dba_synonyms where synonym_name='V$SESSION';

OWNER        SYNONYM_NAME      TABLE_OWNER        TABLE_NAME

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

PUBLIC       V$SESSION         SYS                 V_$SESSION 

here we can see the base object for synonym V$SESSION is V_$SESSION, now we will see the type of this object V_$SESSION 

SQL> select owner, object_name, object_type from dba_objects where object_Name='V_$SESSION';

OWNER        OBJECT_NAME          OBJECT_TYPE

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

SYS          V_$SESSION           VIEW 

 We can see V_$SESSION is a view so we can grant access on V_$SESSION to get access on V$SESSION dynamic view  

SQL> grant select on v_$session to tech_owner;

 

Grant succeeded.

Read More