Топ-100
 
CerebroSQL

SQL developer [PL/SQL] for Oracle Database

Didn't find the features you want? Write in the comments on the forum   and we will add the required functionality
 

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

create new page for Oracle.jpg
 

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 manager for Oracle.jpg
 
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;

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
Tree view for Oracle object

Sheet joint control

Current circuit

Object search scheme

Object tree

Sheet settings

Commands per sheet

Connection control

List of connections saved in the program. When you select a connection, you connect to the database

"Show connection mabager" button - show/hide the connection manager

"Disconnect current list " button - Disconnect 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 is made.

If you select a different scheme, 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 the object tree ( SCHEMA branch)

The object tree

Object tree. Structurally consists of 2 root nodes:

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

PUBLIC - "shared objects"

 
 

"SCHEMA"

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.

CerebroSQL - schema - cluster

Folder menu "CLUSTER"

  • Generate create command for all objects - generate the 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 Nodes Menu

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

-> Request to retrieve data <-

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

-----

 
DATABASE LINK Node

List of connections to other databases in the selected schema

CerebroSQL - schema - database link

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.

CerebroSQL - schema - directory

-> Request to retrieve data <-

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

-----

Menu

  • Drop (execute) - delete a directory. Operation requires confirmation

 

Directory editor

Called from or from the menu branch "DIRECTORY"

  • Create

CerebroSQL - Oracle directory editor

 

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
 
FUNCTION node

List of functions in the selected scheme

CerebroSQL - schema - function

-> Request to retrieve data <-

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

-----

Menu

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

-> Request to retrieve data <-

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 (data is displayed in the grid) Functions with errors are displayed with a special icon

-> Request to retrieve data <-

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

-----

 

  • Compile - recompile a function

-> Request to retrieve data <-

ALTER FUNCTION $$OWNER.$$FUNCTION_NAME COMPILE

-----

 
INDEX node

List of indices in the selected schema

CerebroSQL - schema - index

-> Request to retrieve data <-

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 by which the index is built

-> Request to retrieve data <-

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

-----

Child node "Table"

The table by columns of which the index is built

-> Request to retrieve data <-

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

-----

 

Child node "Expression"

List of Function Index Expressions

-> Request to retrieve data <-

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

-----

Menu

  • Get DDL - generate the code for creating the index and display it in the editor window

 
INDEX PARTITION Node

List of partitioned indexes in the selected schema

CerebroSQL - schema - index partition

The menu is similar to the menu of the "INDEX" node

The child nodes are the same as the child nodes of the "INDEX" node, except

Child node "Partition"

Index Partition List

-> Request to retrieve data <-

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

-----

 
TABLE Node

List of tables in the selected schema.

CerebroSQL - schema - table list

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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 built on table columns

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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"

The rights to the table are given to other users in the database.

-> Request to retrieve data <-

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"

Selected table statistics

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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

 
Oracle table info - general.jpg

 

-> Request to retrieve data <-

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 = $$OWNER)
   and Owner = $$OWNER

-----

"Column" page - information about table columns

CerebroSQL - Oracle table view - columns

 

Constraint Page

Detailed description of data restrictions on tables created on salt shakers

CerebroSQL - Oracle table view - Constraint

 

-> Request to retrieve data <-

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

Details on table indexes

CerebroSQL - Oracle table view - index

 

-> Request to retrieve data <-

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

Details on table triggers

CerebroSQL - Oacle table view - trigger

-> Request to retrieve data <-

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 a table

  • Column -> Info - list of table columns with data type and comment. Data is displayed in a grid

  • Column -> Add - will add to the editor window an example of a command to add a column to a table

-> Code example <-

# 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 - will add an example of a command to change a column to the editor window

-> Code example <-

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

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

-----

  • Column -> Drop - will add to the editor window an example command for deleting a column from a table

-> Code example <-

alter table SYSTEM.TBL drop column ...

-----

  • POOL -> DEFAULT - move the table to the DEFAULT pool. The command is executed

-> Code example <-

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

-----

  • POOL -> KEEP - move the table to the KEEP pool. The command is executed

-> Code example <-

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

-----

  • POOL -> RECYCLE - move the table to the RECYCLE pool. The command is executed

-> Code example <-

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

-----

  • Script -> Select - generate and add a select query text to the query editor with a list of all columns and table alias

-> Code example <-

select t.ID,
       t.NAME,
       t.CURR_DATE
from SYSTEM.TBL t;

-----

  • Script -> Insert - form and add the insert command text to the query editor with a list of all columns

-> Code example <-

insert into SYSTEM.TBL (ID, NAME, CURR_DATE)
values ​​(:ID,:NAME,:CURR_DATE);

-----

  • Script -> Delete - form and add to the query editor the text of the command delect of all data from the table

-> Code example <-

delete from SYSTEM.TBL;

-----

  • Script -> Truncate - generate and add the text of the table truncate command to the query editor

-> Code example <-

truncate table SYSTEM.TBL;

-----

  • Script -> Analyze - generate and add to the query editor a command text for updating statistics on a table

-> Code example <-

begin
dbms_stats.gather_table_stats (ownname => 'SYSTEM',
     tabname => 'TBL',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
     degree => DBMS_STATS.DEFAULT_DEGREE,
     granularity => 'ALL',
     cascade => DBMS_STATS.AUTO_CASCADE,
     force => true);
end;

-----

  • Script -> Drop - generate and add to the query editor the text of the command to drop the table

-> Code example <-

drop table SYSTEM.TBL

-----

  • Script -> Rename - generate and add the renaming command text to the query editor tables

-> Code example <-

alter table SYSTEM.TBL rename to

-----

VIEW node

List of views in the selected schema

 
VIEW node

List of views in the selected schema

CerebroSQL - schema - view

Child node "Column"

List of view columns

-> Request to retrieve data <-

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

-----

 

"PUBLIC"

 
PARAMETER node

List of Oracle DB parameters and its meaning

CerebroSQL - public - parameter

-> Request to retrieve data <-

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

-----

Menu

 
DIRECTORY node

List of directories in all schemes

CerebroSQL - public - directory

-> Request to retrieve data <-

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

-----

Menu

  • Edit rights - run the editor of rights for the directory

  • Drop (execute) - delete a directory

 
INVALID node

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

CerebroSQL - public - invalid

-> Request to retrieve data <-

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

-----

Menu

  • Get source - generate the code for creating an object and display it in the editor window

  • Compile - recompile an object

  • Drop (execute) - delete an object

  • Error list - display in the grid a list of all errors in the database object

-> Request to retrieve data <-

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

-----

 
RECYCLEBIN Node

List of tables in the Oracle database bucket grouped by schema

CerebroSQL - public - recyclebin

-> Request to retrieve data <-

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

-----

Menu

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

-> Code example <-

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

-----

  • Data - retrieve data from a table in the cart

 
TABLESPACE Node

List of tablespaces created in the Oracle database

CerebroSQL - public - oracle tablespace

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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"

Displayed for SYSAUX space only .

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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

-> Request to retrieve data <-

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

-----

Menu

  • Show storage manager - start storage manager for this database

Node "REDO"

Group information by redo magazine

 
Node "REDO"

Group information by redo magazine

CerebroSQL - public - redo

-> Request to retrieve data <-

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

-----

 
Node "NOLOGGING"

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

CerebroSQL - public - nologging

-> Request to retrieve data <-

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

-----

 
Node "NOANALYZE"

List of tables that have no statistics

CerebroSQL - public - oracle noanalyze table

-> Request to retrieve data <-

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 node

List of users in the Oracle database

CerebroSQL - public - users.jpg

-> Request to retrieve data <-

select username,
       account_status
  from dba_users
order by username

-----

Menu

  • Lock - lock the user

-> Command <-

alter user $$USER_NAME account lock

-----

  • Unlock - unblock user

-> Command <-

alter user $$USER_NAME account unlock

-----

  • Create SQL - form a set of commands to create a user and grant privileges

-> Request to retrieve data <-

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 - generate and add a command to change the password to the editor window

-> Code example <-

alter user $$USERNAME identified by ...

-----

  • Change -> Tablespace  - generate and add to the editor window a command to change the default table space

-> Code example <-

alter user $$USERNAME default tablespace

-----

  • Change -> Temp  - create and add a command to the editor window to change the default temporary table space

-> Code example <-

alter user $$USERNAME temporary tablespace

-----

  • Change -> Rename [sys only] - generate and add commands to the editor window to rename the user to the database. 

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

-> Code example <-

--Operation not documented !!!
- !!! Execute strictly on test bases !!!
update sys.user$ set name = upper ('<New name>') where user = 'TESTUSER'
alter system flush shared_pool
/ * after executing these commands, restart the instance * /
/ * and recompile all schema objects, invalid fix
 

-----

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

The task is performed in the main thread, it can take a long time.

Oracle view DDL schema

 

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

Oracle edit user - alter user

 

OBJECT PRIVILEGE Tab

User rights to objects

Oracle edit user - object privilege

 

-> Request to retrieve data <-

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

Oracle edit user - role privilege

 

-> Request to retrieve data <-

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 given to the user

Oracle edit user - system privilege

 

-> Request to retrieve data <-

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

-----

QUOTA tab

User quota for table spaces  

Oracle edit user - quota

 

-> Request to retrieve data <-

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

User tablespace usage data

Oracle edit user - segment

 

-> Request to retrieve data <-

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 the user database with size

Oracle edit user - object size

 

-> Request to retrieve data <-

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 objects of each group

Oracle edit user - object mapping

 

-> Request to retrieve data <-

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"

Information on sessions in the database grouped by schemes

CerebroSQL - public - user connect

-> Request to retrieve data <-

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

Menu items are similar to the "Session manager" tabs

 
Node "PROFILE"

List of profiles in the Oracle database

CerebroSQL - public - profile

-> Request to retrieve data <-

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

  • View or edit - profile editor

Oracle profile editor

 

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

 
SESSION node

Launch session manager for the current database.

 
TOP SQL Node

Launch Max Contributor Query Manager  to the load on the database  

 
ROLE node

List of roles in the database

CerebroSQL - public - role

-> Request to retrieve data <-

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 s