Contacts     ->      ICQ:    699 512 265,         E-Mail: CerebroSQL@gmail.com

  • YouTube Social  Icon

Список SQL запросов используемых в программе для формирования интерфейса, сбора информации о работе баз данных

 

<1> ... <2> ... <3>

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

[Запросы выполняемые при подключении]

1. select SYS_CONTEXT('USERENV','LANGUAGE') "LANGUAGE",
       SYS_CONTEXT('USERENV','CURRENT_SCHEMA') "CURRENT_SCHEMA",
       SYS_CONTEXT('USERENV','HOST') "HOST",
       SYS_CONTEXT('USERENV','SERVER_HOST') "SERVER_HOST",
       SYS_CONTEXT('USERENV','SID') "SID"
  from dual

2. select version from V$INSTANCE

[Запросы для формирования отчета при подключении]

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

2. select name,
       created,
       log_mode
  from v$database

3. select count(*) "Count" from V$TABLESPACE

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

5. select count(*) "Count" from ALL_USERS

6. 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

7. select name, round(value/1024/1024,2)|| ' MB' "MB" from V$SGA

8. 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

[Переключение текущей схемы для сессии]

alter session set current_schema=<Новая схема>

[Tree public: parameter]

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

[Tree public: Directory list]

select dd.OWNER||'.'||dd.DIRECTORY_NAME "Name", dd.DIRECTORY_PATH from DBA|ALL|USER_DIRECTORIES dd order by dd.OWNER, dd.DIRECTORY_NAME

p.s.: запрос работает в случаи, если в "Type view" выбрано DBA или ALL

[Tree public: List files in Oracle directory]

DECLARE
  pattern VARCHAR2(1024) := '
<Directory path>';
  ns VARCHAR2(1024);
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
  FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP
      DBMS_OUTPUT.PUT_LINE(each_file.name);
  END LOOP;
END;

[Tree public: Schema objects in the status Invalid]

select OWNER, count(*) "Count" from DBA|ALL|USER_objects  where status=''INVALID'' group by owner order by OWNER

[Tree public: The object types in the schema in an Invalid status]

select object_type "Name"');
  from DBA|ALL|USER_OBJECTS');
 where status = 'INVALID'
   and owner='
<Schema name>'
group by object_type

[Tree public: The names of the objects in the schema in the status of Invalid]

select object_name "Name"
  from DBA|ALL|USER_OBJECTS
 where status = 'INVALID'
   and owner='
<Schema name>'
   and object_type='
<Object type>'
order by object_name, object_type

[Tree public: The SQL text of the object status Invalid]

select so.TEXT from ALL_SOURCE so
 where so.OWNER='
<Object owner>'
   and so.NAME='
<Object name>'
   and so.TYPE='
<Object type>'
order by so.LINE

[Tree public: compile the object status Invalid]

alter <Object type> <Object owner>.<Object name> compile

[Tree public: To remove the object status Invalid]

drop <Object type> <Object owner>.<Object name>

[Tree public: Error list object status Invalid]

select *
  from ALL_ERRORS ae
 where ae.OWNER='
<Object owner>'
   and ae.NAME='
<Object name>'

[Tree public: The list of schemes and the amount of space occupied in the cart]

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

[Tree public: Information about the objects in the basket]

select r.OWNER,
       r.OBJECT_NAME,
       r.ORIGINAL_NAME,
       r.OPERATION,
       r.TYPE,
       r.TS_NAME,
       r.CREATETIME,
       r.DROPTIME,
       r.PARTITION_NAME,
       round(r.SPACE/1024/1024) "Size"
  from dba_recyclebin r
 where owner='
<Schema name>'

[Tree public: ASM disk group]

select Name,
       sector_size,
       block_size,
       state,
       type,
       Round(total_mb/1024,1) "Total",
       round(free_mb/1024,1) "Free",
       compatibility,
       database_compatibility
  from V$ASM_DISKGROUP

[Tree public: The information on the disks in a disk group ASM]

select d.NAME,
       d.PATH,
       d.DISK_NUMBER,
       d.MOUNT_STATUS,
       d.HEADER_STATUS,
       d.STATE,
       d.MODE_STATUS,
       d.OS_MB,
       d.TOTAL_MB,
       d.FREE_MB,
       d.CREATE_DATE,
       d.MOUNT_DATE,
       d.READS,
       round( d.BYTES_READ/1024/1024,2) "MB_READ",
       d.WRITES,
       round(d.BYTES_WRITTEN/1024/1024,2) "MB_WRITTEN"
  from V$ASM_DISK d,
       V$ASM_DISKGROUP dg
 where d.GROUP_NUMBER=dg.GROUP_NUMBER
   and dg.NAME = '
<ASM group name>'
order by  DISK_NUMBER

[Tree public: Tablespace list]

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

[Tree public: Tablespace - datafile]

Если contents=Temporary

select FILE_NAME||':=>'||round(bytes/1024/1024/1024,2)||' GB' "File",
       online_status,
       maxbytes/1024-bytes/1024 "free"
  from DBA_DATA_FILES
 where tablespace_name='
<Tablespace name>'

else

select FILE_NAME||':=>'||round(bytes/1024/1024/1024,2)||' GB' "File",
       status "online_status",
       maxbytes/1024-bytes/1024 "free"
  from DBA_TEMP_FILES
 where tablespace_name='
<Tablespace name>'

[Tree public: occupants 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

[Tree public: Tablespace - infoTS]

select tablespace_name,
       status,
       logging,
       force_logging,
       extent_management,
       segment_space_management,
       bigfile,
       retention
  from DBA_TABLESPACES
 where tablespace_name='
<Tablespace name>'

[Tree public: Tablespace - ObjSizeDB]

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

[Tree public: REDO]

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

[Tree public: NOLOGGING - parent]

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

[Tree public: NOLOGGING - Schema]

Определение tablespace по умолчанию для пользователя

select default_tablespace from dba_USERS where username = '<Schema name>'

Список таблиц со статусом Nologging

select t.table_name,
       t.TABLESPACE_NAME,
       t.STATUS,
       t.LOGGING
  from DBA|USER|ALL_TABLES t
 where logging='NO'
   and owner = '<Schema name>'
order by table_name

[Tree public: NOANALYZE - parent]

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

[Tree public: NOANALYZE - Schema]

select table_name
  from DBA|USER|ALL_TABLES
 where last_analyzed is null
   and owner='
<Schema name>'
order by table_name

[Tree public: Count jobs the in schemes]

select schema_user "Job", count(*) "Count" from DBA|USER|ALL_JOBS group by schema_user order by schema_user

[Tree public: Jobs in schema]

select *
  from DBA|USER|ALL_JOBS j
where schema_user='
<Schema name>
'
order by j.job

[Tree public: Run job]

select j.job,
       j.sid,
       j.FAILURES,
       j.LAST_DATE,
       j.LAST_SEC,
       j.THIS_DATE,
       j.THIS_SEC,
       j.INSTANCE
  from DBA|USER|ALL_JOBS_RUNNING j
order by j.job

[Tree public: SCHEDULER]

select owner||'.'||job_name "name", state from DBA|USER|ALL_SCHEDULER_JOBS sj order by owner, job_name

[Tree public: PUBLIC SYNONYM]

select synonym_name "syn",'Table - '||table_owner||'.'||table_name "Tabl"

  from ALL_SYNONYMS

 where owner = 'PUBLIC'
    and synonym_name not like '/%'
 order by synonym_name

[Tree public: USER]

Если Type view = ALL

select username, 'UNKNOWN' account_status from all_users order by username

в противном случаи

select username, account_status from dba_users order by username

[Tree public: USER  - Create SQL]

SELECT dbms_metadata.get_ddl('USER','<Schema name>') "DDL" FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<Schema name>') "DDL" from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','<Schema name>') "DDL" from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<Schema name>') "DDL" from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA','<Schema name>') "DDL" from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('DEFAULT_ROLE','<Schema name>') "DDL" from dual;

[Tree public: USER - Create code object]

определение уникального имени базы данных

select sys_context('USERENV','DB_UNIQUE_NAME') "dbname" from dual;

Определение количества объектов по типам
select object_type, count(object_name) "name" from all_objects where owner='<Schema name>' group by object_type;

Генерация DDL отдельно для таблиц
select dbms_metadata.get_ddl('TABLE','<Table name>','<Schema name>') "ddl", '<Table name>' "Name" from dual;

Генерация DDL по остальным объектам
select object_type, object_name from all_objects where owner='''+Node.Text+''' and object_type<>''TABLE'' order by 1,2
for 0 to count then
  select dbms_metadata.get_ddl('<Object type>','<Object name>','<Schema name>') from dual;

<1> .. <2> ... <3>