http://www.orafaq.com/wiki/Scripts
以下是免费的 Oracle 脚本和代码示例。无意间看到，值得学习，拓展一下思路，美中不足是拼写错误较严重。
本文内容
- 一般 SQL 脚本
- 一般 PL/SQL 脚本
- 一般 DBA 脚本
- 对象管理脚本
- 空间管理脚本
- SQL 性能优化脚本
- 数据库安全性和安全审计脚本
- 数据库性能优化脚本
- 备份和恢复脚本
- Oracle XMLDB 和一般 XML 脚本
- Oracle 高级复制脚本
- Oracle (interMedia) 全文检索脚本
- 设计人员库脚本
- Oracle 开发人员表单和报告
- Oracle 应用程序脚本
- Perl 脚本
- AWK 脚本
- Pro*C 程序
- SQLJ 程序
- C 和 C++ 程序
- MS-Windows CMD 和 DOS 批处理脚本
- Visual Basic
- Unix Shell 脚本
- 其他收集脚本的站点
- 警告
一般 SQL 脚本
- Sample SQL matrix report
- Lookup Oracle error messages
- Display Database version, installed options and port string
- "Who am I" script
- Select the Nth highest value from a table
- Select the Nth lowest value from a table
- Demonstrate default column values
- Display table and column comments
- Pass application info through to the Oracle RDBMS
- SQL*Plus Help script
- Test for Leap Years
- Spell out numbers to words
- Demonstrate simple encoding and decoding of messages
- Count the number of rows for ALL tables in current schema
- Demonstrate Oracle database types and object tables
- Demonstrate VARRAY database types
- Demonstrate Oracle temporary tables
- Convert LONG data types to LOBs
- Delete duplicate values from a table
一般 PL/SQL 脚本
- Update/ delete from a huge table with intermittent commits
- Simple program to demonstrate BULK COLLECT and BULK BIND operations
- Profile PL/SQL code for execution statistics
- Select records from a cursor into PL/SQL table
- Password encrypt/decrypt using DBMS Obfuscation Toolkit
- Pass result sets (REF CURSOR) between procedures and functions
- Convert between different numbering systems (binary, octal, decimal and hex)
- Random number/ string generator package
- Function to test for Leap Years
- Print the ASCII table
- Recursive algorithms to calculate Fibonacci and Factorials
- Fetch LOB column values piece-wise from PL/SQL
- Upload and save binary files (like pictures, documents, etc) to/from the DB
- Fetch LONG column values piece-wise from PL/SQL
- Demonstrate writing to a file using the UTL_FILE package
- Map an external file to a database view
- Demonstrate Dynamic SQL
- Demonstrate Java stored procedures
- Execute Operating System commands from PL/SQL (Java call)
- FTP Client (GET and PUT files from PL/SQL)
- Send e-mail messages from PL/SQL (using UTL_TCP)
- Send e-mail messages from PL/SQL with MIME attachments
- Same as above, but implemented using UTL_SMTP
- Mailmerge: Merge data from the table/view data sources into a custom template
- Read an Internet Web pages from PL/SQL
- Track DLL changes (create, drop, alter) within a schema
- Count the number of rows in ALL tables for the current schema
- List tables from schema with more than X rows
- Replace all occurrences of a substring with another substring
- Spell out numbers to words (handy for cheque printing)
- Print cheque amounts in Indian Style
- NYSIIS function (an improvement on SoundeX)
- Converts a string of text into separate soundex values
- Package to generate HTML-type documentation for Oracle objects
一般 DBA 脚本
- Show database uptime in days and hours
- Create database user like an exiting user (with exact privs)
- Switch from one database user to another without password (su.sql)
- Dynamically ZIP large process trace files
- Tabular display of redo-log archiving history (logs/hour)
- List control file structures with usage limits
- Log all database errors to a table
- Demonstrate database and schema level triggers
- Limit resources using the Database Resource Manager
- Log Miner - extract undo statements from log files
- Database cursor usage (open_cursors parameter)
- On-line table reorganizaton using the DBMS_REDEFINITION package
- Create a Primary key column on a table were this is not yet available
对象管理脚本
- List foreign keys to and from a given table
- Script to identify everything to do with a table (Includes Triggers and Constraints)
- Compile invalid database objects in a schema
- Compile all invalid database objects
- Compare indexes on two databases and list the differences
- Re-create all non-system indexes
- Re-build all non-system indexes on-line
- Copy table from one database to another
- Drop a column from a table
- Sets an existing sequence to a value of choice without dropping it
- Reconstruct DDL for tables
- Reconstruct DDL for indexes
- Reconstruct DDL for snapshot logs
- Reconstruct DDL for triggers
空间管理脚本
- Show used/free space per tablespace
- Show used/free space per datafile
- Show used/free space per segment type
- Save summary of database space history over time
- List segments that can not extend (tablespace full)
- List objects in the SYSTEM tablespace that doesn't belong to SYS or SYSTEM
- Oracle segment sizing recommendations
- Show database growth in Meg per month for the last year
- List segments with more than 200 Meg of free DB Blocks
- List tables with high water mark not equal to used blocks
- Check whether a tablespace is fragmented and show fragmentation type
- Check index fragmentation status for a schema
SQL 性能优化脚本
- Explain SQL execution plan
- List analyzed tables with not-analyzed indexes
- List all indexes for a given table
- Analyze all table and index partitions individually
数据库安全性和安全审计脚本
- Block users from using tools like TOAD, SQL Navigator and PLSQL Developer on production databases (DB login trigger)
- Audit User Logins and start traces for selected users (User Login Trigger)
- List database auditing information
- Try to connect to well known database users
- Database users with deadly system privileges assigned to them
- Database users with deadly roles assigned to them
- Security related database initialization parameters and password file users
- List security related profile information
- List Unix OS users that can startup, shutdown and admin Databases
数据库性能优化脚本
- Measure the Buffer Cache Hit Ratio
- Display Database SGA Statistics
- Reports free memory available in the SGA
- List available INIT.ORA parms
- List unsupported INIT.ORA parms
- List active database transactions
- Rollback segment statistics
- Display database sessions using rollback segments
- Lookup database details for a given Unix process id
- Display database locks and latches (with tables names, etc)
- Another lock monitor script
- Display and release DBMS_LOCK locks
- OraYAPS - Yet another Oracle Performance Tuning script
备份和恢复脚本
- Simple on-line backup script (user managed - put tablespaces in backup mode and copy out)
- Check for tablespaces in backup mode and take them out of backup mode
- Take database data files out of backup mode
- List RMAN Backups registered in RMAN catalog database
- Monitor running RMAN Backups
- Summary of datafiles and archlogs backed up with RMAN over past 24 hours
- List completed RMAN Backups for past 24 hours
- Jack van Zanen's RMAN Scripts
- Demonstrate Oracle 8i transportable tablespaces
Oracle XMLDB 和一般 XML 脚本
Oracle 高级复制脚本
- Setup users, DB Links and schedules for Oracle Advanced Replication
- Define replication groups with replication objects (simple)
- Define replication groups with replication objects (more advanced)
- Monitor replication status, sites and groups
- Show transaction details - including old and new column values
- Apply/ Delete Errors
- Remove replication support from database
Oracle (interMedia) 全文检索脚本
- Install Oracle Context on a database
- Demo the creation and maintenance of text indexes
- Load operating system files into Oracle database tables
- Demo thesaurus functionalities
设计人员库脚本
- List Developer/2000 Applications
- List table primary and foreign key references
- List column descriptions per table
- List Developer Repository Owners
Oracle 开发人员表单和报告
- Create a report of a Forms item and block properties
- Create MS-Word document from Developer Forms
- Tetris for Developer 6i
Oracle 应用程序脚本
Perl 脚本
Perl (Practical Extraction and Report Language) scripts:
- Extract Oracle HOME and path from Windows Registry
- OERR command for Windows
- Inserts or retrieves a BLOB from an Oracle database
- Oracle Log Switch Analyzer
- Simple script for checking log switches
- Oracle Alert Log Monitor
- Load Oracle NameServer from TNSNAMES.ORA file
- Script to migrate data from FileMaker Pro to Oracle
AWK 脚本
- Indexfile beautifier (reformats indexfiles produced by 'imp indexfile=')
- Extract SQL Statements from export dump file
- Convert flat files to CSV format
Pro*C 程序
- Select records from a database
- Fetch multiple records from database
- Execute a database procedure with known IN OUT arguments
- Connect to more than one database to do work
- Describe a database procedure and print its arguments
SQLJ 程序
- Select records from a database
- Fetch multiple records from database
- Connect to more than one database to do work
- Execute a database procedure with known IN OUT arguments
- Execute DML against a database
- Fetch data from a REF CURSOR
C 和 C++ 程序
- Extracts archive log header information
- Wrapper for Oracle SQL*Plus to give you command editing and history
- Security fix for problem with arguments showing up in Unix 'ps -ef' output
- Execute operating systems commands from PL/SQL (external procedure call)
微软 Windows CMD 和 DOS 批处理脚本
- Extracts data from ODBC sources (including Excel and Access files) to different formats
- Registry update to add "SQL*Plus here" to right click menus
- Show machine info and Oracle environment variables
- Start an Oracle database
- Stop an Oracle database
- Check if database is up and running
- Start and stop the Oracle SQL*Net Listener
- Start and stop Oracle related Windows Services
- Run SQL*Plus script from DOS batch file
- Export contents of an Oracle database
- Schedule a job to execute at regular intervals
- OCopy.bat - Hot backup of oracle databases
- OraInit.bat - Start/Stop oracle service
Visual Basic
- Switch ALERT.LOG and LISTENER.LOG files on Windows Systems
- Schedule Archivelog backup when less than X Meg free and more than Y files to backup
Unix Shell 脚本
- Run the same SQL on multiple DBs from a centralized server
- Print alertlog messages with date/time stamp on the same line
- Manage Oracle trace files (delete old/ send mail for new)
- Maintain a daily cycle of Oracle alert log, trace and SQL*Net files
- Generic script to date, compress and delete old log files
- Generate "DB Creation Scripts" from a running instance
- An interactive ORAENV script
- Perform an on-line database backup
- Export database directly to tape with label
- Copy tables in parallel between databases (intelligent scheduler)
- Automate the generation of STATSPACK reports in a logical manner
- Delete RMAN backupsets older than a specified number of days
- Recatalog deleted archived log files with RMAN
- List Installed Oracle Products
- SQL*Plus replacement shell: Scroll through command history!!! (ZIP file)
- Check if a set of databases is running
- Check if databases are available by connecting via listener
- Wait until a database is available
- Monitor if an Oracle OID (LDAP) Server is functioning
- Monitor if an Oracle Names Server is functioning
- Swap (roll) and initialise the listener's .log files
- Check if all entries in the TNSNAMES.ORA file is valid
- Monitor if a Web Server is running or not
- Download a file from a Web server (eg. latest TNSNAMES.ORA)
- Parameter driven script to rebuild indexes intelligently
- Send E-mail from Unix with some file attachments (using mailx)
- Send E-mail from Unix with some file attachments (using sendmail)
- Unix sed script to search and replace a string in all files in a directory
- Map C-language structure to binary data file
- List free space for disk groups (Veritas Volume Manager)
- Create new raw volume for a database file (Veritas Volume Manager)
- Delete a raw volume (Veritas Volume Manager)
- Rename a raw volume (Veritas Volume Manager)
其他收集脚本的站点
- OraMag's Code Depot
- Material Dreams' DBA Scripts
- Biju's Oracle tips and scripts page
- Steve Rea's Oracle Tips, Tricks, and Scripts
警告
这些脚本可能危险，潜在破坏数据或操作系统。确保你理解，使用前，在你环境自定义，并测试所有的脚本。