Module pysqlqueries

{'datafile': (u'''select 'Datafiles', file_name from dba_data_files
                where  (%s) /*%s*/ order by %s''',
              'file_name'),
 'directory': (u'''select owner, directory_name from all_directories
                where  (%s) and owner like '%s' order by %s''',
               'directory_name'),
 'function': (u'''select distinct owner, name from all_source
                where (%s) and owner like '%s' and type='FUNCTION' ord
...

{'commentFromNameAndOwner': u'''select comments from all_tab_comments
                        where table_name=:1
                        and owner=:2''',
 'dbfStatusFromName': u'''select status from dba_data_files
                        where file_name=:1''',
 'objectStatusFromName': u'''select status from all_objects
                        where object_name=:1''',
 'objectStatusFromNameAndOwner': u'''select status from all_objects
...

{'pathFromName': u'''select directory_path
                        from all_directories
                        where directory_name=:1'''}

{'allocatedBytesFromName': u'''select bytes
                        from dba_data_files
                        where file_name=:1''',
 'freeBytesFromName': u'''select nvl(sum(fr.bytes), 0)
                        from dba_data_files df, dba_free_space fr
                        where df.file_id=fr.file_id
                          and df.file_name=:1''',
 'tablespaceFromName': u'''select tablespace_name
...

{'hostFromOwnerAndName': u'''select owner, host
                        from all_db_links
                        where db_link=:1''',
 'usernameFromOwnerAndName': u'''select owner, username
                        from all_db_links
                        where db_link=:1'''}

{'indexedColumnsFromOwnerAndName': u'''select COLUMN_NAME, COLUMN_POSI
TION
                                              from  ALL_IND_COLUMNS
                                              where index_owner=:1
                                              and index_name=:2
                                              order by COLUMN_POSITION
''',
 'propertiesFromOwnerAndName': u''' select TABLE_OWNER, TABLE_NAME, IN
...

{'ddlFromTypeNameAndOwner': u'''select
                                         dbms_metadata.get_ddl(:1, :2,
 :3)
                                         from dual'''}

{'queryFromOwnerAndName': u'''select owner, query
                        from all_mviews
                        where owner=:1
                          and mview_name=:2'''}

{'proceduresFromOwnerAndName': u'''select procedure_name
                        from all_procedures
                        where owner=:1
                          and object_name=:2''',
 'sourceFromOwnerAndName': u''' select line, replace(replace(text, chr
(10), ' '), chr(13), ' ')
                        from  all_source
                        where owner=:1
...

{'lastFromOwnerAndName': u'''select sequence_owner, last_number
                        from all_sequences
                        where sequence_owner=:1
                          and sequence_name=:2''',
 'maxFromOwnerAndName': u'''select sequence_owner, max_value
                        from all_sequences
                        where sequence_owner=:1
                          and sequence_name=:2''',
...

{'targetFromOwnerAndName': u'''select table_owner, table_name
                        from all_synonyms
                        where owner=:1
                          and synonym_name=:2'''}

{'sourceFromOwnerAndNameAndType': u'''select text from all_source
                                        where owner=:1
                                        and name=:2
                                        and type=:3
                                        order by line'''}

{'avgRowLengthFromOwnerAndName': u'''select avg_row_len
                                              from all_tables
                                              where owner=:1
                                              and table_name=:2''',
 'indexedColFromOwnerAndName': u'''select column_name, index_name, col
umn_position
                                              from all_ind_columns
                                              where table_owner=:1
...

{'datafilesFromName': u'''select file_name
                        from dba_data_files
                        where tablespace_name=:1'''}

{'columnsFromDBAAndName': u'''select a.column_name, a.data_type||'('||
a.data_length||')', a.nullable, c.comments
                    from dba_tab_columns a, dba_col_comments c
                    where a.owner=:1
                      and a.owner=c.owner
                      and a.table_name=:2
                      and a.table_name=c.table_name
                      and a.column_name=c.column_name''',
...

{'bodyFromOwnerAndName': u'''select trigger_body
                        from all_triggers
                        where owner=:1
                          and trigger_name=:2''',
 'eventFromOwnerAndName': u'''select triggering_event
                        from all_triggers
                        where owner=:1
                          and trigger_name=:2''',
...

{'defaultTbsFromName': u'''select default_tablespace
                        from dba_users
                        where username=:1''',
 'nbIndexesFromNameAndTbs': u'''select count(*)
                        from all_indexes
                        where owner=:1
                          and tablespace_name like :2''',
 'nbTablesFromNameAndTbs': u'''select count(*)
...

{'queryFromOwnerAndName': u'''select owner, text
                        from all_views
                        where owner=:1
                          and view_name=:2''',
 'replaceQueryFromFullName': u'create or replace view %s as %s',
 'replaceQueryFromName': u'create or replace view %s as %s'}

{'all': u'''Select a.Sid "Id", a.Serial# "Serial", a.SchemaName "Schem
a",
                        a.OsUser "Osuser", substr(a.Machine, 0, decode
(instr(a.Machine, '.'), 0, length(a.Machine), instr(a.Machine, '.')-1)
) "Machine", a.Program "Program",
                        To_Char(a.Logon_Time, 'MON-DD HH24:MI') "Logge
d Since",
                        b.Block_Gets "Blk Gets", b.Consistent_Gets "Co
...

{'columnsFromOwnerAndTable': u'''SELECT column_name
                                            , data_type
                                            , (SELECT position
                                               FROM all_cons_columns c
ol, all_constraints cst
                                               WHERE cst.owner=col.own
er
                                                 AND tab.owner=col.own
...

{'refByFromOwnerAndName': u'''SELECT referenced_owner, referenced_name
, referenced_type
                                       FROM all_dependencies
                                       WHERE owner=:1
                                         AND name=:2''',
 'refOnFromOwnerAndName': u'''SELECT owner, name, 'None'
                                       FROM all_dependencies
                                       WHERE referenced_owner=:1
...

{'IndexesFromOwnerAndTbs': u'''SELECT i.index_name, i.num_rows, i.dist
inct_keys, s.bytes, i.table_name
                                     FROM all_indexes i, dba_segments 
s
                                     WHERE i.owner=:1
                                       AND i.tablespace_name=:2
                                       AND i.owner=s.owner
                                       AND i.index_name=s.segment_name
...

{'addm_report_text': u'select dbms_advisor.get_task_report(:1, :2, :3)
 from sys.dual',
 'awr_report_html': u'select * from table(dbms_workload_repository.awr
_report_html(:1, :2, :3, :4))',
 'awr_report_text': u'select * from table(dbms_workload_repository.awr
_report_text(:1, :2, :3, :4))',
 'db_id': u'select to_char(dbid) from v$database',
 'instance_num': u'select to_char(instance_number) from v$instance',
...

{'indexesForTbsAndUser': u'SELECT a.owner "Owner", a.tablespace_name "
Tablespace", a.segment_name "Index", DECODE(COUNT(a.partition_name), 0
, '', '*') "Part?", COUNT(c.blevel) "Level", c.distinct_keys "Keys", a
.blocks "Size(blk)", ROUND(a.blocks*b.block_size/1024/1024, 1) "Size(M
o)", ROUND((100*a.blocks)/:1, 1) "Size(%)" FROM DBA_SEGMENTS a, DBA_TA
BLESPACES b, DBA_INDEXES c WHERE a.tablespace_name LIKE :2 AND a.owner
 LIKE :3 AND a.tablespace_name=b.tablespace_name AND a.segment_name=c.
index_name AND a.segment_name NOT LIKE '%PLAN_TABLE' AND a.segment_typ
...