SQL [PL/SQL] developer for Oracle Database

Editor SQL/PLSQL for DBMS Oracle allows you to quickly and conveniently work with databases, administer and monitor work. The editor supports working with server versions Oracle 8.0.3 and older

No additional software is required to work with Oracle DBMS.

All required libraries are included

The default library version is 11G

query editor - dashboard 1

query editor - dashboard 2

query editor - dashboard 5

query editor - dashboard 1

1/5

Didn't find the features you need? Write in the comments at forum and we will add the necessary functionality

Connection to Oracle DBMS

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

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

Team structure:connect"username"/"Password"@"TNSALIAS"

Example:connect sys/oracle@tmpdb

ps: mode indicationas sysdbanot required, the program parses the command into parts and checks the entered parameters

3. In the editor of the sheet connection with the database

Структура листа

Sheet structure

Менеджер соединений

Connection Manager

Connections are created in a single connection manager more

Load db report

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;

select l.RESOURCE_NAME, l.CURRENT_UTILIZATION, l.MAX_UTILIZATION, l.LIMIT_VALUE
from V$RESOURCE_LIMIT l where l.LIMIT_VALUE <>' UNLIMITED' and l.LIMIT_VALUE<>' 0' ;

select ad.status,
ad.target,
ad.schedule,
ads.destination,
ads.RECOVERY_MODE,
ads.DATABASE_MODE,
ad.LOG_SEQUENCE,
ads.ARCHIVED_SEQ#,
ads.APPLIED_SEQ#,
ad.fail_sequence,
ad.error
from V$ARCHIVE_DEST ad, V$ARCHIVE_DEST_STATUS ads
where ad.target='STANDBY'
and ad.DEST_NAME=ads.DEST_NAME

Object tree

Дерево объектов

Sheet joint control

Current circuit

Object search scheme

Object tree

Sheet settings

Commands per sheet

connection control

List of connections saved in the program. Selecting a connection connects to the database

Button "Show connection mabager" - show/hide connection manager

Button "Disconnect current list"- Break the connection with the database of the current sheet

current schema

The default schema for finding objects. Equal to the username under which the connection to the database was made.

If another scheme is selected, an attempt is made to execute the command:

alter session set current_schema = ....;

Schema name

Scheme (user) in which objects are searched when working with a tree of objects (branchSCHEMA)

The object tree

Object tree. Structurally consists of 2 root nodes:

SCHEMA- user objects, tables, views, .....

PUBLIC- "shared objects"

SCHEMA

"SCHEMA"

Cluster

Node "CLUSTER"

A cluster is a schema object that contains data from one or more tables, each of which has one or more common columns. Oracle stores together all rows from all tables with the same cluster key.

Folder menu "CLUSTER"

Generate create command for all objects- generate a code for creating all objects
Generate code for all objects- generate the code for creating all objects, but also display the formation commands and the number of objects

Child node menu

view- generate the code for creating the selected object, display the description of the object from the system catalog

select distinct(object_name) "object_name",
status
from $$VIEWTYPE_objects
where object_type = $$OBJECT_TYPE
and OWNER=$$OWNER
order by object_name

database link

"DATABASE LINK" Node

List of connections to other databases in the selected schema

DATABASE LINK folder menu

Command create link - display a code template for creating a link in the editor window

CREATE DATABASE LINK "Link name"
CONNECT TO [User name]
IDENTIFIED BY [User password]
USING '[tnsalias or description]'

Child Nodes Menu

Testing - check the connection.

in fact, a query of the form is executed:

select 'x' "x" from dual@<dblink name>

View - generate the code for creating the selected object, display the description of the object from the system catalog

"DIRECTORY" node

List of "directories" created in the selected schema. The directory is created with the create directory ... command.

select dd.OWNER||'.'||dd.DIRECTORY_NAME "Name",
dd.DIRECTORY_PATH
from DBA_DIRECTORIES dd
where owner=$$SCHEMA_NAME
order by dd.OWNER, dd.DIRECTORY_NAME

Drop (execute)- delete directory. Operation requires confirmation

Directory editor

Called from or from the menu branch "DIRECTORY"

Create

Create directory

Enter the name of the new directory in the "Name directory" field
In the "Path" field, enter the path to the directory on the server with the DBMS
Click the "Create directory" button

Changing directory privileges

In the list of users "List user" select users for which you want to issue the rights
In the "Right" block, set checks on the required rights
Click the "Apply" button

CerebroSQL - Oracle directory editor right

Node "FUNCTION"

function

List of functions in the selected scheme

select distinct(object_name) "object_name",
status
from $$VIEWTYPE_objects
where object_type = $$OBJECT_TYPE
and OWNER=$$OWNER
order by object_name

Get DDL- generate the function creation code and display it in the editor window

select dbms_metadata.get_ddl('FUNCTION',$$FUNCTION_NAME,$$FUNCTION_OWNER)
as ddl from dual

Show errors- display a list of errors in the function, if any (the data is displayed in the grid). Functions with errors are displayed with a special icon

select line||'->'||position||' ('||text||')' "Error"
from ALL_ERRORS
where owner= $$OWNER
and type='FUNCTION'
and name=$$FUNCTION_NAME
order by sequence

Compile- recompile function

ALTER FUNCTION $$OWNER.$$FUNCTION_NAME COMPILE

Node "INDEX"

index

List of indices in the selected schema

select distinct(object_name) "object_name",
status
from $$VIEWTYPE_objects
where object_type = $$OBJECT_TYPE
and OWNER=$$OWNER
order by object_name

Child node "Column"

List of columns on which the index is built

select column_name
from $$VIEWTYPE_IND_COLUMNS
where index_name=$$INDEX_NAME
and index_owner=$$OWNER
order by column_name

Child node "Table"

Table on whose columns the index is built

select distinct(table_name) "Table_name"
from $$VIEWTYPE_INDEXES
where index_name=$$INDEX_NAME
and owner=$$OWNER

Child node "Expression"

Function index expression list

select column_expression
from $$VIEWTYPE_IND_EXPRESSIONS
where index_name=$$INDEX_NAME
and index_owner=$$OWNER

Get DDL- generate the index creation code and display it in the editor window

index partition

"INDEX PARTITION" node

List of partitioned indexes in the selected schema

The menu is similar to the node menu "INDEX"

The child nodes are the same as the child nodes of the "INDEX", with the exception of

Child node "Partition"

List of index partitions

select partition_name
from $$VIEWTYPE_IND_PARTITIONS
where index_name = $$INDEX_NAME
and index_owner = $$OWNER
order by partition_position desc

table

Node "TABLE"

List of tables in the selected schema.

select distinct(object_name) "object_name",
status
from DBA_objects
where object_type = 'TABLE'
and OWNER=$$OWNER
order by object_name

Child node "Storage"

Table size and tablespace name

select tablespace_name,
round(bytes/1024/1024,3) "size"
from DBA_SEGMENTS
where segment_name = $$TABLE_NAME
and owner=$$OWNER

Child node "Columns"

List of table columns with data type

select column_name||' ('||data_type||
(case
data_type WHEN 'VARCHAR2'
THEN '('||DATA_LENGTH||')'
end)||')' "name"
from $$VIEWTYPE_TAB_COLUMNS
where table_name=$$TABLE_NAME
and owner=$$OWNER
order by column_id

Child node "Index"

List of indexes based on table columns

select i.index_name,
i.INDEX_TYPE,
i.UNIQUENESS,
i.COMPRESSION,
i.TABLESPACE_NAME,
i.STATUS,
i.NUM_ROWS,
i.VISIBILITY
from SYS.DBA_INDEXES i
where table_name = $$TABLE_NAME
and owner =$$OWNER
order by index_name

Child node "Trigger"

List of triggers on the selected table

select trigger_name
from $$VIEWTYPE_TRIGGERS
where table_name=$$TABLE_NAME

and table_owner=$$OWNER

Child node "Constraint"

List of restrictions on data in table columns

select c.CONSTRAINT_NAME "name",
c.SEARCH_CONDITION,
c.STATUS,
c.DEFERRABLE,
c.DEFERRED,
c.VALIDATED
from $$VIEWTYPE_CONSTRAINTS c
where table_name=$$TABLE_NAME

and owner=$$OWNER

Child node "Granted"

Rights to the table granted to other users in the database.

select p.GRANTEE,
p.GRANTOR,
p.PRIVILEGE,
p.GRANTABLE
FROM $$VIEWTYPE_TAB_PRIVS p
where p.owner=$$OWNER
and p.table_name = $$TABLE_NAME
order by p.GRANTEE, p.PRIVILEGE

Child node "Statistics"

Statistics for the selected table

select * from DBA_TAB_STATISTICS
where table_name =$$TABLE_NAME
and owner = $$OWNER

Node menu "TABLE"

Get DDL- generate the table creation code and add it to the editor window

select dbms_metadata.get_ddl('TABLE',$$TABLE_NAME,$$OWNER) as ddl
from dual

table view- view detailed information about the table

General Page- general information

select t.OWNER, t.TABLE_NAME, t.TABLESPACE_NAME, t.STATUS,
t.PCT_FREE, t.PCT_USED, t.INI_TRANS, t.MAX_TRANS,
t.INITIAL_EXTENT, t.NEXT_EXTENT, t.FREELISTS,
t.LOGGING, t.PARTITIONED, t.LAST_ANALYZED, t.MONITORING, tc.COMMENTS
from $$VIEWTYPE_TABLES t, $$VIEWTYPE_TAB_COMMENTS tc
where t.table_name=$$TABLE_NAME
and t.owner=$$OWNER
and tc.OWNER=t.OWNER
and tc.TABLE_NAME=t.TABLE_NAME

select ts.NUM_ROWS, ts.BLOCKS, ts.AVG_ROW_LEN,
ts.SAMPLE_SIZE, ts.LAST_ANALYZED
from $$VIEWTYPE_TAB_STATISTICS ts
where ts.OWNER=$$OWNER
and ts.TABLE_NAME=$$TABLE_NAME

select tm.INSERTS, tm.UPDATES, tm.DELETES, tm.TIMESTAMP, tm.TRUNCATED
from $$VIEWTYPE_TAB_MODIFICATIONS tm
where tm.TABLE_OWNER=$$OWNER
and tm.TABLE_NAME=$$TABLE_NAME

select round(sum(bytes)/1024/1024,2) "MBSize"
from $$VIEWTYPE_SEGMENTS
where segment_name = $$TABLE_NAME and owner=$$OWNER

select round(sum(bytes)/1024/1024,2) "MBSize", count(*) "CountIND"
from $$VIEWTYPE_SEGMENTS
where segment_name in (select index_name
from $$VIEWTYPE_INDEXES
where table_name = $$TABLE_NAME
and table_owner = 'SYSTEM')
and Owner=$$OWNER

Column Page- information about the columns of the table

CerebroSQL - Oracle table view - columns

Constraint Page

Detailed description of data restrictions on tables created on salt shakers

CerebroSQL - Oracle table view - Constraint

select ac.CONSTRAINT_NAME "Name"
from ALL_CONSTRAINTS ac
where ac.table_name=$$TABLE_NAME
and ac.OWNER= $$OWNER
order by CONSTRAINT_NAME

select ac.OWNER,
ac.TABLE_NAME,
ac.CONSTRAINT_NAME,
decode(ac.CONSTRAINT_TYPE,'C','Check constraint',
'P','Primary key',
'U','Unique key',
'R','Referential',
'V','Check option',
'O','Read only')
"CONSTRAINT_TYPE",
ac.STATUS,
nvl(ac.BAD,'-') "BAD",
nvl(ac.RELY,'-') "RELY",
ac.LAST_CHANGE,
nvl(ac.INDEX_OWNER,'-') "INDEX_OWNER",
nvl(ac.INDEX_NAME,'-') "INDEX_NAME",
nvl(ac.INVALID,'-') "INVALID",
nvl(ac.VIEW_RELATED,'-') "VIEW_RELATED",
nvl(ac.R_OWNER,'-') "R_OWNER",
nvl(ac.R_CONSTRAINT_NAME,'-') "R_CONSTRAINT_NAME",
nvl(ac.DELETE_RULE,'-') "DELETE_RULE",
ac.DEFERRABLE,
ac.DEFERRED,
ac.VALIDATED,
ac.GENERATED,
ac.SEARCH_CONDITION
from DBA_CONSTRAINTS ac
where ac.CONSTRAINT_NAME=$$CONSTRAINT_NAME
and ac.TABLE_NAME=$$TABLE_NAME
and ac.OWNER=$$OWNER

Index Page

Detailed information about table indexes

select Index_name
from ALL_INDEXES
where table_name=$$TABLE_NAME
and table_owner=$$OWNER

select i.INDEX_TYPE,
i.TABLE_OWNER||'.'||i.TABLE_NAME "TABLE",
i.UNIQUENESS,
i.COMPRESSION,
i.TABLESPACE_NAME,
i.LOGGING,
i.STATUS,
i.NUM_ROWS,
i.SAMPLE_SIZE,
i.LAST_ANALYZED,
i.PARTITIONED,
i.BUFFER_POOL
from DBA_INDEXES i
where i.OWNER=$$OWNER
and i.INDEX_NAME=$$INDEX_NAME
and i.TABLE_NAME=$$TABLE_NAME

select ic.COLUMN_NAME,
ic.COLUMN_POSITION,
ic.COLUMN_LENGTH,
ic.CHAR_LENGTH,
ic.DESCEND
from DBA_IND_COLUMNS ic
where ic.INDEX_OWNER=$$OWNER
and ic.INDEX_NAME=$$INDEX_NAME
and ic.TABLE_NAME=$$TABLE_NAME

Trigger page

Detailed information on table triggers

select tr.TRIGGER_NAME,
tr.TRIGGER_TYPE,
tr.TRIGGERING_EVENT,
tr.TRIGGER_BODY
from $$VIEWTYPE_TRIGGERS tr
where tr.TABLE_OWNER=$$OWNER
and tr.TABLE_NAME=$$TABLE_NAME
and tr.TRIGGER_NAME=$$TRIGGER_NAME

Show data- extract data from table
Column -> Info- list of table columns with data type and comment. Data displayed in grid
Column -> Add- adds to the editor window an example of a command for adding a column to the table

Пример

# alter table table_name add (
# column1_name column1_datatype column1_constraint,
# column2_name column2_datatype column2_constraint,
# column3_name column3_datatype column3_constraint)

alter table SYSTEM.TESTTB add column (... ... ...)

Column -> Modify- adds an example of a command to change the column to the editor window

Пример

# alter table table_name modify (
# column1_name column1_datatype,
# column2_name column2_datatype,
# column3_name column3_datatype)

alter table SYSTEM.TBL modify (... ...)

Column -> Drop- adds to the editor window an example of a command to remove a column from a table

alter table SYSTEM.TBL drop column ...

POOL -> DEFAULT- move the table to the DEFAULT pool. Command in progress

alter table $$OWNER.$$TABLE_NAME storage ( buffer_pool keep)

POOL -> KEEP - move the table to the KEEP pool. Command in progress

alter table $$OWNER.$$TABLE_NAME storage ( buffer_pool keep)

POOL -> RECYCLE - move the table to the RECYCLE pool. Command in progress

alter table $$OWNER.$$TABLE_NAME storage ( buffer_pool RECYCLE)

Script -> Select- generate and add to the query editor the text of the select query with a listing of all columns and a table alias
Script -> Insert - generate and add command text to the query editor insertlisting all columns
Script -> Delete - generate and add command text to the query editor selectall data from the table
Script -> Truncate - generate and add command text to the query editortruncate tables
Script -> Analyze- generate and add to the query editor the text of the command to update statistics on table
Script -> Drop - generate and add to the query editor the text of the command to delete the table
Script -> Rename- generate and add to the query editor the text of the renaming command tables

Table view

VIEW

Node "VIEW"

List of views in the selected schema

Child node "Column"

View column list

select column_name,
data_type
from DBA_TAB_COLUMNS
where table_name = $$TABLE_NAME
and owner = $$OWNER
order by column_id

public

"PUBLIC"

parameter

Node "PARAMETER"

List of Oracle DB parameters and its meaning

select name, nvl(value, '<Null>') "value"
from V$PARAMETER order by name

Edit-launch the Oracle DBMS Parameter Editor

public directory

"DIRECTORY" node

List of directories in all schemes

select dd.OWNER||'.'||dd.DIRECTORY_NAME "Name",
dd.DIRECTORY_PATH
from DBA_DIRECTORIES dd
order by dd.OWNER, dd.DIRECTORY_NAME

edit rights- launch the permissions editor for the directory
Drop (execute)- delete directory

invalid

Node "INVALID"

List of objects in the invalid status grouped by schemas and object types

select OWNER,
count(*) "Count"
from $$VIEWTYPE_objects
where status='INVALID'
group by owner order by OWNER

Get source- generate the object creation code and display it in the editor window
Compile- recompile object
Drop (execute)- delete object
error list- display in the grid a list of all errors in the database object

select *
from ALL_ERRORS ae
where ae.OWNER=$$OWNER
and ae.TYPE=$$OBJECT_TYPE
and ae.NAME=$$OBJECT_NAME

Node "RECYCLEBIN"

recyclebin

List of tables in the Oracle database bucket grouped by schema

select distinct(owner) "NAME",
round(sum(space)/1024/1024,2) "MB",
sum(space) "sbytes"
from DBA_recyclebin
group by owner order by 2 desc

To create a table from- add to the editor window the command code to create a new table based on the table from the basket

Пример

CREATE TABLE <TABLE NAME> AS SELECT * FROM TESTUSER."BIN$KVmaWfsMSteelsO9Quo3Hw==$0"

Data- retrieve data from table in cart

Node "TABLESPACE"

tablespace

List of tablespaces created in the Oracle database

select tablespace_name||' ('||
substr(contents,1,1)||')'
"Tablesp",
status,
logging,
tablespace_name
from DBA_TABLESPACES
order by tablespace_name

Child node "DATAFILE"

List of data files on disks with current size

select DDF.FILE_NAME||':=>'||
round(ddf.bytes/1024/1024/1024,2)||
' GB' "File",
online_status,
(maxbytes-bytes)/1024 "free"
from DBA_DATA_FILES ddf
where ddf.tablespace_name=$$TABLESPACE_NAME

Child node "OCCUPANTS"

Shown only for space SYSAUX.

SELECT schema_name||': '|| occupant_name ||' -> '||space_usage_kbytes||' KB' "Occup"
FROM v$sysaux_occupants
ORDER BY space_usage_kbytes desc, schema_name,occupant_name

Child node "INFOTS"

Tablespace Details

select tablespace_name,
status,
logging,
force_logging,
extent_management,
segment_space_management,
bigfile,
retention
from DBA_TABLESPACES
where tablespace_name=$$TABLESPACE_NAME

Child node "OBJSIZE"

List of all objects in the tablespace with their size sorted by size

select segment_name,
segment_type,
OWNER,
round((sum(bytes)/1024/1024),2) "ObjSize"
from DBA_SEGMENTS
where tablespace_name=$$TABLESPACE_NAME
group by segment_name, segment_type, owner
order by 4 desc

Node "REDO"

Group information by redo magazine

select l.group#,
round((l.bytes)/1024/1024/1024,2) "Size",
l.status,
l.first_time,
f.MEMBER,
l.ARCHIVED,
l.SEQUENCE#
from V$LOG l, v$logfile f
where l.GROUP# = f.GROUP#
order by 1

"NOLOGGING" node

NOLOGGING

List of tables in the database, operations in which are not logged in the pre-record logs REDO

select owner,
count(*) "CountNo"
from DBA_TABLES
where logging='NO'
group by owner order by 2 desc

select t.table_name,
t.TABLESPACE_NAME,
t.STATUS,
t.LOGGING
from DBA_TABLES t
where logging='NO'
and owner = $$OWNER
order by table_name

NOANALYZE

Node "NOANALYZE"

List of tables that have no statistics

CerebroSQL - public - oracle noanalyze table

select distinct(owner) "OWNER",
count(*) "Count"
from DBA_TABLES
where last_analyzed is null
group by owner
order by 2 desc

select table_name
from DBA_TABLES
where last_analyzed is null
and owner=$$OWNER
order by table_name

USER

"USER" node

List of users in the Oracle database

select username,
account_status
from dba_users
order by username

lock- block user

alter user $$USER_NAME account lock

Unlock- unblock user

alter user $$USER_NAME account unlock

Create SQL- generate a set of commands for creating a user and issuing privileges

SELECT dbms_metadata.get_ddl('USER',$$USERNAME) "DDL" FROM dual
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',$$USERNAME "DDL" from dual
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',$$USERNAME) "DDL" from dual
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',$$USERNAME) "DDL" from dual
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA',$$USERNAME) "DDL" from dual
SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE',$$USERNAME) "DDL" from dual

Change -> Password- create and add to the editor window a command to change the password
Change -> Tablespace - generate and add to the editor window a command to change the default tablespace
Change -> Temp - generate and add to the editor window a command to change the default temporary tablespace
Change -> Rename [sys only]- generate and add commands to the editor window to rename the user in the database.

IMPORTANT: The operation is not documented, perform strictly on test bases!!!

Operation not documented
Run strictly on test bases
update sys.user$ set name = upper('New name') where user = '$$USER';
alter system flush shared_pool
after executing these commands, restart the instance
and recompile all schema objects invalid fix

Create code object- generate a code for creating all objects in the schema. The code is displayed in a separate window.

The task is executed in the main thread, it can be long.

Tree object - tree of objects. Clickable, go to the object code in the " DDL Schema " list

Save - save all code to a file on disk, the file is saved in the directory. \ Tmp \ ddl \

Edit - user editor and view detailed properties

User editor

OBJECT PRIVILEGE Tab

User rights to objects

select p.OWNER,
p.TABLE_NAME,
p.PRIVILEGE,
p.GRANTABLE
from sys.dba_tab_privs p
where p.grantee = $$USERNAME
order by p.table_name,
p.PRIVILEGE

ROLE PRIVILEGE tab

List of database roles given to the user

select granted_role,
admin_option,
default_role
from sys.dba_role_privs
where grantee = $$USERNAME
order by granted_role

"SYSTEM PRIVILEGE" tab

List of system privileges granted to the user

select privilege,
admin_option
from sys.dba_sys_privs
where grantee = $$USERNAME
order by privilege

QUOTA tab

Tablespace user quota

select tq.TABLESPACE_NAME,
case
when tq.MAX_BYTES=-1 then 'Unlimited'
else to_char(round(tq.MAX_BYTES/1024/1024))
end "Max_Bytes",
case
when tq.MAX_BLOCKS=-1 then 'Unlimited'
else to_char(tq.MAX_BLOCKS)
end "Max_Block"
from DBA_TS_QUOTAS tq
where tq.USERNAME=$$USERNAME

SEGMENT Tab

Tablespace space usage data by user

select s.TABLESPACE_NAME,
round(sum(s.BYTES/1024/1024/1024), 3) "Size"
from DBA_SEGMENTS s
where s.OWNER=$$USERNAME
group by TABLESPACE_NAME order by 2 desc

OBJECT SIZE Tab

List of objects in user database with size

select s.SEGMENT_NAME,
s.TABLESPACE_NAME,
round(sum(s.BYTES/1024/1024/1024), 3) "Size"
from DBA_SEGMENTS s where s.OWNER=$$USERNAME
group by TABLESPACE_NAME, s.SEGMENT_NAME
order by 3 desc

OBJECT MAPPING Page

Grouping of objects by types, with information on the objects of each group

select segment_type,
count(*) "Count",
tablespace_name,
round((sum(bytes)/1024/1024)) "Size"
from DBA_SEGMENTS
where owner=$$OWNER
group by segment_type, tablespace_name
order by 2 desc

select segment_name,
tablespace_name,
round(sum(bytes)/1024/1024,2) "Size",
buffer_pool
from dba_segments
where owner=$$OWNER and segment_type=$$SEGMENT_TYPE
group by segment_name,tablespace_name, buffer_pool
order by segment_name

user connect

"USER CONNECT" node

Information on sessions in the database grouped by schemes

select username||' ('||count(*)||')' "username"
from v$session
where username is not null
group by username
order by username

select s.SID,
s.SERIAL#,
s.USERNAME,
s.STATUS,
s.SERVER,
s.SCHEMA#,
s.OSUSER,
s.MACHINE,
s.PORT,
s.TERMINAL,
s.PROGRAM,
s.SQL_ID
from v$session s
where username = $$OWNER
order by sid

Menu items are similar to the "Session manager" tabs

Node "PROFILE"

profile

List of profiles in the Oracle database

select username||' ('||count(*)||')' "username"
from v$session
where username is not null
group by username
order by username

select s.SID,
s.SERIAL#,
s.USERNAME,
s.STATUS,
s.SERVER,
s.SCHEMA#,
s.OSUSER,
s.MACHINE,
s.PORT,
s.TERMINAL,
s.PROGRAM,
s.SQL_ID
from v$session s
where username = $$OWNER
order by sid

View or edit - profile editor

The profile editor is used to view the list of users who have a profile applied and make changes to it.

Node "SESSION"

session

Launch session manager for the current database.

top sql

Node "TOP SQL"

Launch Max Contributor Query Manager to the load on the database

role

Node "ROLE"

List of roles in the database

select r.ROLE||' ['||(select count(*)
from DBA_ROLE_PRIVS rp
where rp.GRANTED_ROLE =r.ROLE)||']' "ROLE"
from DBA_ROLES r
order by r.ROLE

select distinct(grantee) "grantee"
from sys.dba_role_privs
where granted_role = $$ROLENAME
order by grantee

restore point

"RESTORE POINT" node

List of rollback points created in the database

SELECT NAME,
SCN,
TIME,
DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,
STORAGE_SIZE
FROM V$RESTORE_POINT

database link

"DATABASE LINK" Node

List of connections to other databases in all schemas

select owner||'.'||db_link "db_Link",
username,
host
from DBA_DB_LINKS
order by owner, db_link

настройки листа

Sheet settings

Font size- font size of the visual elements of the current sheet
type view- type of system representations of the Oracle DBMS catalog from which data for the object tree is retrieved. In $$VIEWTYPE queries
Compliment load- way to load data for code hint
- work- in real time
- Manual- by hand. The mode is suitable for large databases, when queries to the catalog are performed for a long time
use styler- use syntax and keyword highlighting in the query editor
Statistics query- display query execution statistics. Statistics are collected twice, before and after execution, and the difference is displayed

select st.SID,
sn.NAME,
st.VALUE
from V$SESSTAT st, V$STATNAME sn
where st.STATISTIC# = sn.STATISTIC#
and st.SID=$$SID
and st.VALUE<>0
order by value desc

TrimTrailingSpace- remove spaces at the end of a line
AutoThemeAdapt- adapt colors to windows theme
DelErase- replace selected text as you type or add new characters at the beginning
HiddenCaret - hide cursor position in text

SQL [PL/SQL] developer for Oracle Database

Menu

Connection to Oracle DBMS

​

Sheet structure

Connection Manager

Object tree

connection control

current schema

Schema name

The object tree

"SCHEMA"

Node "CLUSTER"

"DATABASE LINK" Node

​

"DIRECTORY" node

​

Node "FUNCTION"

​

Node "INDEX"

"INDEX PARTITION" node

Node "TABLE"

​

Node "VIEW"

"PUBLIC"

Node "PARAMETER"

​

"DIRECTORY" node

​

Node "INVALID"

​

Node "RECYCLEBIN"

​

Node "TABLESPACE"

​

Menu

Node "REDO"

"NOLOGGING" node

​

Node "NOANALYZE"

​

"USER" node

​

User editor

"USER CONNECT" node

Node "PROFILE"

Node "SESSION"

Node "TOP SQL"

Node "ROLE"

"RESTORE POINT" node

​

"DATABASE LINK" Node

​

Sheet settings