Useful Oracle Scripts and Commands


oracle-db

The following SQL Scripts used on a regular basis, you might find them helpful in your daily activities for Oracle 8i, 9i, 10g, 11g:

Security Grants
  • grant select on PERSON_TABLE to public with grant option;
  • select * from dba_tab_privs where TABLE_NAME = ‘PERSON_TABLE’
  • select * from dba_role_privs where granted_role = ‘PORTMAN_TABLE’
Re-sizing A Data File
  • alter database datafile ‘/u04/oradata/wpk/temp01.dbf’ resize 500m;
Show All Product Information
  • select * from product_component_version;
Show Row Counts For All Tables That Have ANALYZE On
  • select owner table_name, num_rows from dba_tables where num_rows > 0;
Select All Users Active in The System
  • select sid, serial#,user#, Username, machine, program, server, status, command, type from v$session order by username;
Show What A Current User is Doing
  • select sid, serial#, status, server from v$session where username = ‘BROWNBH’;
Create Count For All Tables
  • select ‘Select count(*) from ‘ ||owner|| ‘.’ ||table_name|| ‘;’ from dba_all_tables order by owner, table_name;
Show All Indexes
  • select owner, index_name, table_type, tablespace_name from dba_indexes where owner <>’SYSTEM’ and owner <> ‘DBSNMP’ and owner <> ‘ORDSYS’ and owner <> ‘OUTLN’ and owner <> ‘SYS’ and owner <> ‘SYSTEM’ order by owner, index_name, tablespace_name;
Show All Tables
  • select owner, table_name, table_type, tablespace_name from dba_all_tables where owner <>’SYSTEM’ and owner <> ‘DBSNMP’ and owner <> ‘ORDSYS’ and owner <> ‘OUTLN’ and owner <> ‘SYS’ and owner <> ‘SYSTEM’ order by owner, table_name, tablespace_name;
Show Space Used
  • select Tablespace_Name, /*Tablespace name*/ Owner, /*Owner of the segment*/ Segment_Name, /*Name of the segment*/ Segment_Type, /*Type of segment (ex. TABLE, INDEX)*/ Extents, /*Number of extents in the segment*/ Blocks, /*Number of db blocks in the segment*/ Bytes /*Number of bytes in the segment*/ from DBA_SEGMENTS where owner <>’SYSTEM’ and owner <> ‘DBSNMP’ and owner <> ‘ORDSYS’ and owner <> ‘OUTLN’ and owner <> ‘SYS’ and owner <> ‘SYSTEM’;
Sum Space by Owner
  • select owner, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by owner;
Sum Space by Tablespace
  • select tablespace_name, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by tablespace_name;
Show Reads and Writes by File Name in Oracle DB
  • select v$datafile.name “File Name”, v$filestat.phyrds “Reads”, v$filestat.phywrts “Writes” from v$filestat,v$datafile where v$filestat.file# = v$datafile.file#;
Show Versions of Software
  • select * from V$VERSION;
Identify Segments That are Getting Close to Their Max-Extent Values
  • select owner,tablespace_name,segment_name,bytes,extents,max_extents from dba_segments where extents*2 > max_extents;
Identifies Segments That are Getting Close to Running Out of Contiguous Free Space
  • select owner, s.tablespace_name, segment_name, s.bytes, next_extent, max(f.bytes) largest from dba_segments s, dba_free_space f where s.tablespace_name = f.tablespace_name(+) group by owner, s.tablespace_name, segment_name, s.bytes, next_extent having next_extent*2 >max(f.bytes);
Displays Archived Redo Log Information
  • select * from v$database;
Display Count Historical Archived Log Information From The Control File
  • select count(*) from v$archived_log;
  • select min(completion_time) from v$archived_log;
Shows Current Archive Destinations
  • select * from v$archive_dest;
Backups Of Archived Logs
  • select count(*) from v$backup_redolog;
Display All Online Redo Log Groups for The database
  • select * from v$log;
Show All Datafiles for Tablespace and Oracle Stuff
  • select * from dba_data_files order by tablespace_name, file_name;

Resources

SQL Server Data Dictionary in Seconds using Extended Properties


sql-server-dd

A data dictionary, or metadata repository, as defined in the IBM Dictionary of Computing, is a “centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format.” The term may have one of several closely related meanings pertaining to databases and database management systems (DBMS):

  • A document describing a database or collection of databases
  • An integral component of a DBMS that is required to determine its structure
  • A piece of middle-ware that extends or supplants the native data dictionary of a DBMS

Resources

Microsoft SQL Server 2008 AIO


SQL Server 2K8

Microsoft SQL Server 2008 (All in One)

Resources

Download by clicking on the respected Link, mentioned above …