The Master Key to Oracle's Data Dictionary. You've just taken on responsibility for a new database. New to you, that is -- the database has actually been running for some years. What's the first thing you want to do? If you're like me, you probably want to get a handle on just what it is that you have. What's in the database? How big is it? Who are the users and how many of them are there? The answers to all of these questions lie in the Oracle data dictionary.
Viewing Database Metadata The data dictionary is the repository for database metadata, which is a fancy term for data describing the database. Codd's fourth rule for relational database systems states that database metadata must be stored in relational tables just like any other type of data. SELECT table_name FROM user_tables; Note the prefix user_ in this example. USER_ USER views return information about objects owned by the currently-logged-on database user.
Many views have analogs in all three groups. Oracle Built-in Functions. Oracle/PLSQL Date Functions. Oracle date functions. Using oracle date format. Oracle9i CASE Expressions And Statements. The CASE expression was first added to SQL in Oracle8i. Oracle9i extends its support to PL/SQL to allow CASE to be used as an expression or statement. Value Match CASE Expression The CASE expression is a more flexible version of the DECODE function. In its simplest form it is used to return a value when a match is found. SELECT ename, empno, (CASE deptno WHEN 10 THEN 'Accounting' WHEN 20 THEN 'Research' WHEN 30 THEN 'Sales' WHEN 40 THEN 'Operations' ELSE 'Unknown' END) department FROM emp ORDER BY ename; The value match CASE expression is also supported in PL/SQL. SET SERVEROUTPUT ON DECLARE deptno NUMBER := 20; dept_desc VARCHAR2(20); BEGIN dept_desc := CASE deptno WHEN 10 THEN 'Accounting' WHEN 20 THEN 'Research' WHEN 30 THEN 'Sales' WHEN 40 THEN 'Operations' ELSE 'Unknown' END; DBMS_OUTPUT.PUT_LINE(dept_desc); END; / Searched CASE Expression A more complex version is the searched CASE expression where a comparison expression is used to find a match.
Value Match CASE Statement. Little Things... SELECT tablespace_name FROM all_tables WHERE table_name = 'YOURTABLENAME'; If the unique/primary keys can be identified from the table, it is easier to remove the records from the table using the following query: DELETE FROM tablename WHERE rowid not in (SELECT MIN(rowid) FROM tablename GROUP BY column1, column2, column3...); Here column1, column2, column3 constitute the identifying key for each record. If the keys cannot be identified for the table, you may create a temporary table using the query CREATE TABLE temptablename AS SELECT DISTINCT * FROM tablename; Then drop the original table and rename the temp table to original tablename.
The records can be identified and removed using the query DELETE FROM childtablename ct WHERE NOT EXISTS (SELECT 'x' FROM parenttablename pt WHERE ct.keycolumn1 = pt.keycolumn1 AND ct.keycolumn2 = pt.keycolumn2...) Or if you need to provide the user with bad records you may change the DELETE to SELECT with column list. SELECT COUNT(*) FROM tablename; The Oracle Database Administrator. This chapter describes the responsibilities of the person who administers the Oracle server, the database administrator. The following topics are included: Types of Oracle Users At your site, the types of users and their responsibilities may vary. For example, at a large site the duties of a database administrator might be divided among several people.
This section includes the following topics: Database Administrators Because an Oracle database system can be quite large and have many users, someone or some group of people must manage this system. A database administrator's responsibilities can include the following tasks: Security Officers In some cases, a database might also have one or more security officers.
Application Developers An application developer designs and implements database applications An application developer's responsibilities include the following tasks: Application Administrators An Oracle site might also have one or more application administrators. Database Users. Magazine Online.
Case Query « Select Query « Oracle PL / SQL. Triggers. Metadata. Auditing. Sample Code. Scripts. Below are some free Oracle Scripts and code examples you can download: [edit] General SQL Scripts [edit] General PL/SQL Scripts [edit] General DBA Scripts [edit] Object Management Scripts [edit] Space Management Scripts [edit] SQL Performance Tuning Scripts [edit] Database Security and Security Auditing Scripts [edit] Database Performance Tuning Scripts [edit] Backup and Recovery Scripts [edit] Oracle XMLDB and general XML Scripts A method for logging the changes to an xml table [edit] Oracle Advanced Replication Scripts [edit] Oracle (interMedia) Text Scripts [edit] Designer Repository Scripts [edit] Oracle Developer Forms and Reports [edit] Oracle Applications Scripts [edit] Perl Scripts Perl (Practical Extraction and Report Language) scripts: [edit] AWK Scripts [edit] Pro*C Programs [edit] SQLJ Programs [edit] C and C++ Programs [edit] MS-Windows CMD and DOS Batch Scripts [edit] Visual Basic [edit] Unix Shell Scripts [edit] Other sites with script collections [edit] Warning.