« Previous : 1 : 2 : 3 : 4 : Next »

Synonyms

2007/02/18 19:08 / Study/Oracle SQL

Synonyms

Synonyms are database objects that enable you to cll a table by another name.

  • create an easier reference to a table that is owned by another user.
  • shorten lengthy object name
  • This method can be especially useful with lengthy object names, such as views.
  • A user who wants to make a synonym can make any synonym as long as the user has CREATE SYNONYM privileges in the user's schema.
  • If there are two synonyms in public schema and private schema, a synonym from the private schema will have prior to the the synonym from the public schema.
syntax)
CREATE [PUBLIC] SYNONYMS synonym FOR object;

PUBLIC : creates a synonyms that is accessible to all users;

- The object cannot be contained in a package.
- A private synonym name must be distinct from all other object that are owned by the
  same user.

Creating and Removing Synonyms

ex)
CREATE SYNONYM d_sum FOR dept_sum_vu;

DROP SYNONYM d_sum;

CREATE PUBLIC SYNONYM dept FOR alice.departments;

DROP PUBLIC SYNONYM dept;

- Only the database administrator can drop a public synonym.


이올린에 북마크하기(0) 이올린에 추천하기(0)
Posted by Figo.
TAGS , ,
4 Trackbacks , No Comment

Leave your greetings here.

Restriction of using a sequence

2007/02/18 18:33 / Study/Oracle SQL

  • A subquery in a DELETE, SELECT, or UPDATE statement

  • A query of a view or of a materialized view

  • A SELECT statement with the DISTINCT operator

  • A SELECT statement with a GROUP BY clause or ORDER BY clause

  • A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator

  • The WHERE clause of a SELECT statement

  • The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

  • The condition of a CHECK constraint

이올린에 북마크하기(0) 이올린에 추천하기(0)
Posted by Figo.
TAGS , , , ,
5 Trackbacks , No Comment

Leave your greetings here.

Using Oracle in-line views

2007/02/18 17:54 / Study/Oracle SQL

The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.

A common use for in-line views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query.

The best example of the in-line view is the common Oracle DBA script that is used to show the amount of free space and used space within all Oracle tablespaces. Let’s take a close look at this SQL to see how it works. Carefully note that the FROM clause in this SQL query specifies two sub-queries that perform summations and grouping from two views, dba_data_files, and dba_free_space.

In ANSI standard SQL, it is quite difficult to compare two result sets that are summed together in a single query, and this is a common problem with Oracle SQL where specific values must be compared to a summary.  Without the use of an in-line view, several separate SQL queries would need to be written, one to compute the sums from each view and another to compare the intermediate result sets.

This is a great report for display the actual amount of free space within an Oracle tablespace.

column "Tablespace" format a13
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
colimn "Total MB"   format 99,999,999

select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;

This SQL quickly compares the sum of the total space within each tablespace to the sum of the free space within each tablespace. Here is a sample of the output:

Basically, this query needs to compare the sum of total space within each tablespace with the sum of the free space within each tablespace.

이올린에 북마크하기(0) 이올린에 추천하기(0)
Posted by Figo.
TAGS , , , ,
3 Trackbacks , No Comment

Leave your greetings here.

Savepoint and Rollback

2007/02/18 14:44 / Study/Oracle SQL

- Commit : End the current transaction by making all pending data changes permanant.

- SAVEPOINT name : make a save point of this current transaction. It's like living a mark.

- ROLLBACK : End the current transaction by discarding all pending data changes.

- ROLLBACK TO SAVEPOINT_name : Rollback to the specified savepoint. If you don't mention the name of the savepoint, the statement will roll back the entire transaction.

SAVEPOINT is not a standard.

- Automatic Commit : DDL, DCL, Normal exit from iSQL plus or SQLplus

- Automatic rollback : Adnormal exit from iSQL plus or SQL plus.

이올린에 북마크하기(0) 이올린에 추천하기(0)
Posted by Figo.
TAGS , ,
No Trackback , No Comment

Leave your greetings here.

MERGE Statement Syntax

2007/02/18 14:01 / Study/Oracle SQL
  1. Conditional update or insert.
  2. UPDATE if the same row exist and INSERT if it's a new row.
  3. Table name is not required because you already knew which table is the target table.

MERGE        INTO    table_name    table_alias

    USING ( table | view | sub_query ) alias

    ON ( join condition )

    WHEN MATCHED THEN

        UPDATE SET

        Col1 = col_val1,

        Col2 = col_val2

    WHEN NOT MATCHED THEN

        INSERT (column_list)

        VALUES (column_values);

이올린에 북마크하기(0) 이올린에 추천하기(0)
Posted by Figo.
TAGS , , , ,
1 Trackback , No Comment

Leave your greetings here.

Function-Based Indexes

2007/01/10 22:53 / Study/Oracle SQL
  1. The Oracle server uses the index only when that particular function is used in a query.
  2. If the function is not used in a query and there is no specific index, the Oracle server will full scan.
  3. QUERY_REWRITE_ENABLE initialization parameter must be set to TRUE for a function-based index to be used.

CREATE        INDEX    upper_dept_name_idx

ON        dept2(UPPER(department_name));

 

By Office 2007

이올린에 북마크하기(0) 이올린에 추천하기(0)
Posted by Figo.
No Trackback , No Comment

Leave your greetings here.

Adding a Constraint Syntax

2007/01/09 23:04 / Study/Oracle SQL
ALTER TABLE  <table_name>
ADD [CONSTRAINT  <constraint_name.]
type (<column_name>);
  • Enable or Disable a constraint
  • Can't modify
  • Add "NOT NULL" by using "MODIFY" clause

    Only if the table is empty or every row of the table is filled.
ALTER TABLE emp2
modify employee_id Primary Key;

ALTER TABLE emp2
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(manager_id) REFERENCES emp2(employee_id)


ALTER TABLE emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY (Department_id)
REFERENCES departments ON DELETE CASCADE;
** When data in the parent key is deleted, all rows in the child table that depend on the delete parent key values are also deleted.
이올린에 북마크하기(0) 이올린에 추천하기(0)
Posted by Figo.
TAGS , ,
No Trackback , No Comment

Leave your greetings here.

The guideline of Modifying a Column

2007/01/09 21:36 / Study/Oracle SQL

Guidelines :

  • You can increase the width or precision of a numeric column.
  • You can increase the width of numeric of character columns.
  • You can decrease the width of a column if :
    - The column contains only null values
    - The table has no rows
    - The decrease in column width is not less than the existing values in that column
  • You can change the data type if the column contains only null values.
    exception) CHAR to VARCHAR
  • CHAR to VARCHAR2 , or VARCHAR2 to CHAR : Only if the column contains null values or if you don't change the size.
이올린에 북마크하기(0) 이올린에 추천하기(0)
Posted by Figo.
TAGS , ,
1 Trackback , No Comment

Leave your greetings here.

Revoking Object Privileges

2007/01/09 20:53 / Study/Oracle SQL

  • REVOKE : To revoke privileges granted to other users
  • Privileges granted to others through the WITH GRANT OPTION clause are also revoked.
Syntax]
REVOKE {privilege [, privilege ...] | ALL }
ON   object
FROM {user [,user....]  role | PUBLIC |
[CASCADE CONSTRAINTS];


CASCADE is required to remove any referential integrity constraints made to the CONSTRAINTS object by means of the REFERENCES privileges.

ex)
REVOKE   select, insert
ON           departments
FROM      scott;


** If you drop the user account without revoking privileges from it, then the system privileges granted by this user to other users are not affected by this action. To drop a user, you have to check about privileges granted to the user.

이올린에 북마크하기(0) 이올린에 추천하기(0)
Posted by Figo.
TAGS
3389 Trackbacks , No Comment