免费的 Oracle 脚本和代码示例

http://www.orafaq.com/wiki/Scripts

 

以下是免费的 Oracle 脚本和代码示例。无意间看到，值得学习，拓展一下思路，美中不足是拼写错误较严重。

 

本文内容

1. 一般 SQL 脚本
2. 一般 PL/SQL 脚本
3. 一般 DBA 脚本
4. 对象管理脚本
5. 空间管理脚本
6. SQL 性能优化脚本
7. 数据库安全性和安全审计脚本
8. 数据库性能优化脚本
9. 备份和恢复脚本
10. Oracle XMLDB 和一般 XML 脚本
11. Oracle 高级复制脚本
12. Oracle (interMedia) 全文检索脚本
13. 设计人员库脚本
14. Oracle 开发人员表单和报告
15. Oracle 应用程序脚本
16. Perl 脚本
17. AWK 脚本
18. Pro*C 程序
19. SQLJ 程序
20. C 和 C++ 程序
21. MS-Windows CMD 和 DOS 批处理脚本
22. Visual Basic
23. Unix Shell 脚本
24. 其他收集脚本的站点
25. 警告

 

一般 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 脚本

• A method for logging the changes to an xml table

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 应用程序脚本

• Print Oracle Apps versions
• Display status of all Concurrent Managers

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

警告

这些脚本可能危险，潜在破坏数据或操作系统。确保你理解，使用前，在你环境自定义，并测试所有的脚本。