Data Server Scripts

Frank_Kwan · 发表于 2002-8-28 01:34

Sort of old-fashion DBA/Dev, or train by old-fashion DBAs, so I'm still craze about "scripts". Recently Metalink updated a list of Data Services' (database) scripts.

For those beginners, either DBAs/Devs, it's a good chance by reading and learning these scripts.

For those experts, it may refresh your mind to read other experts' scripts.

For those without the account in Metalink, find a good friend of you whom has and then help you download it. I think, they're opened-source.
If you really couldn't get them, draw me a line and see if I could work around for you.

Please don't treat yourself as a "Recycle bin", download and read those you're really interested in.

Enjoy your life.
-frank

----------------------------------------------------------------------------------------------
Here's a list of Metalink's scripts related to Data Services.

CONSTRAINTS (条限)
Check For Foreign Key Locking Issues GSX 1019527.6 - 查外条限被锁
Determine An Object's Dependencies GSX 1020289.6 - 查数据对象的依属关系
Generate List Of Table Dependencies GSX 1020089.6 - 查数据表之间的依属关系
Listing Foreign Key Constraints GSX 1039297.6 - 查外条限的条限
Report Table Constraints GSX 1019930.6 - 查数据表的条限
Script For Capturing Table Constraints GSX 1019469.6 - 修改数据表的条限
Script To Create SQL to Enable/Disable Table Constraints GSX 1019923.6 -修改数据表条限的状态
Script To Eliminate Non-Unique Rows GSX 1019920.6 - 删去重覆的行
Script To Generate A Delete Cascade Report GSX 1019471.6 - 纪录串连删除
Script To Locate Tables W/O Primary Keys GSX 1019927.6 - 查没有主健的数据表
Script To Report Constraints For A User GSX 1019586.6 - 查用户的数据表条限

DBA_INFORMATION (数据库资料)
Create View To Display All Open Pipes GSX 1020087.6 - 查SGA上的数据管道
Delete Commit Procedure GSX 1020306.6 - 确定删除的程式
List Inactive Users GSX 1020004.6 - 查非活动用户
Recompiling Invalid Objects Automatically Note 73995.1 - 重新辑录无效的数据对象
Removing Offending Synonyms of Invalid Objects GSX 1039309.6 - 删除无效数据对象的同义对象
Report Dispatcher & Process Mapping Using Mts GSX 1019594.6 - 查MTS dispatchers上的O/S用户
Report Redo Log Information GSX 1019932.6 - 查重造纪录的资料
Report Sequence Values GSX 1019931.6 - 查顺序链的资料
Script For Creating A Data Base Schema Summary GSX 1019462.6 - 查数据格的资料
Script To Display Files Needed To Perform Hot Backup GSX 1065578.6 - 查需要热备份的文件
Script to do END BACKUP on all datafiles GSX 1070152.6
Script To Drop Some Or All Objects From Schema GSX 1019468.6
Script to find who owns TEMP Segments in V7 Note 40758.1
Script To Fully Describe A Table GSX 1020077.6
Script To List Programs For A User GSX 1019926.6
Script To Monitor Session Idle Times GSX 97515.1
Script To Obtain Session Information GSX 1019526.6
Script To Produce A Database Summary Report GSX 1019546.6
Script To Provide Database Performance Overview GSX 1019548.6
Script To Provide Listing Of All Database Files GSX 1020082.6
Script to Rectify Differences between two tables in a Replicated Environment
Script To Report Instance Parameters GSX 1019914.6
Script To Return What Columns Have Null Values Data GSX 1061042.6
Script To Show Audit Options/Audit Trail GSX 1019552.6
SQL: DIAG- Determining Dependency Information (Oracle8) Note 62435.1
SQL Finding Objects in SYSTEM and SYS schemas Note 68080.1
Verify Stored Procedures GSX 1019928.6

EXPORT/IMPORT
Exporting All Tables Within a Specific Tablespace GSX 1039292.6
Script To Export Data To Fixed-Width Text GSX 1019522.6
Script To Export User Definitions GSX 1019554.6
Script To Generate Sql*Loader Control File GSX 1019523.6
Script To Report Object Owners In Database GSX 1019919.6

INDEX
Script To Describe All Indexes In A Schema GSX 1019464.6
Script To Report Information On Indexes GSX 1019722.6
Script To Show Data Selectivity For A Column GSX 1019622.6

JOB QUEUE
Script To List Running Jobs GSX 1020283.6
Script To List Submitted Jobs GSX 1020284.6

LOCKING
Display Locks And Give Sid And Serial # To Kill GSX 1020007.6
Display Sql Text From Locks GSX 1020010.6
Fully Decoded Locking Script GSX 1020008.6
Low Complexity Locking Info GSX 1020011.6
Report Sessions Waiting For Locks GSX 1020088.6
Script To Display User Lock Information GSX 1020047.6
Script To Return Medium Detail Locking Info GSX 1020012.6
Viewing Locks on Objects Held by a Specific User GSX 1039273.6

OBJECT_CREATION
Canned Scripts To Gather Ddl For All Triggers GSX 1053232.6
Generate Create Tablespace Script GSX 1020180.6
Script To Generate Db Link Script GSX 1020175.6
Generate Index Creation Script GSX 1020181.6
Generate Object Priv Grant Script GSX 1020176.6
Generate Role Creation Script GSX 1020178.6
Generate Synonym Creation Script GSX 1020179.6
Generate System Priv Grant Script GSX 1020177.6
Generate View Creation Script GSX 1016346.102
View To Show Parallel Instance Number GSX 1020344.6

PERFORMANCE
Create Stats For All Tables In Schema GSX 1020191.6
Formatted Select Of Explain Plan Table GSX 1020282.6
Mstat -- System Statistics /Tuning Script GSX 1019592.6
Report Session & System Waits GSX 1019936.6
Report Statistics For A Table GSX 1020003.6
Report Table's Rows Per Datafile GSX 1019625.6
Report Various Db Statistics GSX 1020187.6
Rstat -- Reactive Performance Utility GSX 1020046.6
Script To Check Tables For Statistics GSX 1020188.6
Script To Provide Latch Performance Information GSX 1019627.6
Script To Report Dictionary Cache Effectiveness GSX 1019632.6
Script To Report File I/O Statistics GSX 1019629.6
Script To Report Sga Buffer Summary GSX 1019635.6
Script To Report The Instance Statistics GSX 1019624.6
Script To Simplify The Use Of Explain Plan GSX 1019631.6
Script To Show SGA Parameters & Statistics GSX 1020076.6
Sort physical rows of a table GSX 1019924.6

PRIVILEGES/PROFILES
Create View To Show All User Privs GSX 1020286.6
Report Privs Granted To A User GSX 1020086.6
Report User Role And Profile Info GSX 1019929.6
Script To Report Roles Granted To Users GSX 1019486.6
Script To Show System & Object Privs For User GSX 1019508.6

ROLLBACK_SEGMENTS
Finding the Number of Transactions in RBS for a Specific Object GSX 1039126.6
Script For Displaying Active Transactions In Roll GSX 1019467.6
Script To Display Wrap Time For Rollback Segs GSX 1019993.6
Script To Obtain Rollback Segment Information GSX 1019485.6

SPACE_MANAGEMENT
Analyzing Tablespace Usage With An Os-Independent Sql Script GSX 1039289.6
Check Tables For Backup Status GSX 1020000.6
Computing Table Size Note 70183.1
Count How Many Of Each Object Users Have GSX 1020185.6
Get Row Count For All Tables In A Schema GSX 1020005.6
Report Db Space Usage By User GSX 1020186.6
Report On Space In Tablespaces GSX 1020090.6
Report Size Of Stored Objects GSX 1020290.6
Report Size Of Stored Objects II GSX 1020288.6
Report Tables With Many Extents GSX 1020085.6
Script To Calculate Blocks Needed By A Table GSX 1019585.6
Script To Calculate Table Row Size GSX 1019507.6
Script To Create Database Extents Summary GSX 1019463.6
Script To Create Tablespace Block Map GSX 1019474.6
Script For Data & Index Block Distribution GSX 1019718.6
Script To Detect Tablespace Fragmentation GSX 1020182.6
Script To Graphically View Occupied Space Of A Tablespace GSX 1039298.6
Script To List Tablespace, Files And Free Space GSX 1019999.6
Script To Move Sys.Aud$ Table Out Of System Table GSX 1019377.6
Script To Print Block Map Of Entire Database GSX 1019710.6
Script To Print Map Of All Database Files GSX 1019714.6
Script To Remove Chained Rows From A Table GSX 1019556.6
Script To Report Free Space In Database GSX 1020080.6
Script To Report # Of User Objects Per Tablespace GSX 1019998.6
Script To Report On Segment Extents GSX 1019915.6
Script To Report Segments in a Datafile GSX 1019720.6
Script To Report Segment Storage Parameters GSX 1019918.6
Script To Report Table Fragmentation GSX 1019716.6
Script To Report Tables Approaching Maxextents GSX 1019721.6
Script To Report Tables Which Should Be Dropped GSX 1020084.6
Script To Report Tablespace Free And Fragmentation GSX 1019709.6
Script To Report On Tablespace Storage Parameters GSX 1019506.6
Script To Report Tablespace Use By Segment Type GSX 1019711.6
Script To Report The Largest Segments In The Data GSX 1020083.6
Script To Report Users And # Of Objects In Tables GSX 1019994.6
Script To Show Tablespace Use By User GSX 1019712.6
Script To Show Table Extents & Storage Parameters GSX 1019505.6
Script To Show Tables Which Cannot Extend GSX 1019553.6
Script To Show Extents For A Given Segment GSX 1019996.6
User Segment/Extent Report GSX 1020184.6

STORAGE
How to Move Tables from One Tablespace to Another Note 147356.1
Script to Report Size of Oracle 8.x Stored Objects Note 111156.1
Script to Report on Space in Tablespaces Note 1020090.6

UTILITIES
Convert Mm/Dd/Yyyy Hh:Mi:Ss To Redo Dump Time GSX 1020342.6
Convert Redo Dump Time To Mm/Dd/Yyyy Hh:Mi:Ss GSX 1020343.6
Determine Segment Order Of Columns GSX 1020345.6
Package to Convert Numbers Between HEX/DECIMAL/OCTAL/BINARY Note 67533.1
Script For Hexadecimal To Decimal Conversion GSX 1019580.6
Script To Calculate Business Days Between Two Dat GSX 1019589.6