SQL Editor [PL/SQL] for Oracle Database

Creating a new sheet

1. By clicking the "New list for Oracle"

2. File - New - Oracle

3. Keyboard shortcut: Shift + F1

4. By cloning the current sheet: Ctrl + t

​

Connecting to Oracle DBMS

​

1. Select a previously saved connection from the drop-down list "Connection control"

2. By running the connect ... command in the query editor.

Command structure: connect <username>/<Password>@<TNSALIAS>

Example: connect sys/oracle@tmpdb

ps: specifying the as sysdba mode is not required, the program parses the command into parts and checks the entered parameters

3. In the editor of the sheet-to-database connection

​

Sheet structure

Connection manager

Connection list

​

List of previously saved connections

​

Connection parameter tab

Alias

Or block name from tnsnames.ora file or connection string in the format: host:port/service_name

"Local TNS editor" button

Tnsnames.ora local file editor

Login

Username under which to connect to the database

Password

User password under which to connect

New password

Change the password to the specified one. After changing the password (the "Change password" button), copy it into the "Password" field and save the changes

Mode

Connection mode

Switch "Load db report"

After connecting to the database, generate a short report on the status of the database

​

TNS settings tab

Vendor lib (oci.dll)

The full path to the oci.dll file from the Oracle client installed directory

TNS_ADMIN

Directory on disk with tnsnames.ora file

NLS_LANG

Client encoding parameters

​

Test button

Check the connection to the database using the entered values

Connect button

Connect to Oracle database using entered values

Save button

Save connection parameters for later use

"Delete" button

Delete selected connection

Load db report

-> Request to retrieve data <-

select instance_name,
       host_name,
       version,
       startup_time,
       status,
       archiver
  from v$instance;

​

select name,
       created,
       log_mode,
       open_mode,
       database_role,
       SWITCHOVER_STATUS,
       PLATFORM_NAME
from v$database;

​

select count(*) "Count" from V$TABLESPACE;

​

select count(*) "Count",
       round (sum(bytes/1024/1024/1024), 2) || ' GB '"SizeFile"
  from DBA_DATA_FILES;

​

select count(*) "Count" from ALL_USERS;

​

select count(distinct(l.group#)) "Count",
       round(l.bytes / 1024/1024) "MB",
       count(lf.GROUP#) / count (distinct (l.group#)) "File"
  from V$LOG l,
       V$LOGFILE lf
where l.GROUP# = lf.GROUP#
group by bytes;

​

select name, round (value / 1024 / 1024.2) || 'MB' "MB" from V$SGA;

​