Oracle Database Objects and Naming rules

 

Oracle Database objects:

  1. Tables: Basic storage structures for data organized in rows and columns.
  2. Indexes: Improve the speed of data retrieval operations by providing quick access paths.
  3. Views: Virtual tables based on the result of a SELECT query, simplifying data access.
  4. Sequences: Objects used to generate unique numbers, often for creating primary key values.
  5. Synonyms: Aliases for database objects, providing alternative names for simplifying access.
  6. Stored Procedures: Sets of SQL statements stored for execution. They don’t return a value.
  7. Functions: Sets of SQL statements stored for execution, returning a value.
  8. Triggers: Sets of instructions executed automatically in response to specific events.
  9. Packages: Organize related procedures, functions, and PL/SQL constructs for modularization.
  10. Materialized Views: Views that store the result set physically, improving query performance.
  11. Constraints: Rules applied to tables to enforce data integrity, such as primary key and foreign key constraints.
  12. Partitions: Divide large tables and indexes into smaller, more manageable pieces.
  13. Database Links: Connections to remote databases, allowing communication between them.
  14. Roles: Named groups of privileges assigned to users, simplifying access control.
  15. Profiles: Set of resource limits and password policies for database users.
  16. Clusters: Physical storage structure that groups tables sharing common columns.
  17. Snapshots: Read-only copies of data from remote databases, updated periodically.
  18. LOBs (Large Objects): Data types for handling large amounts of data, like BLOBs (Binary LOBs) and CLOBs (Character LOBs).
  19. Directories: Logical aliases for physical file locations, used in database operations.

Oracle Object Naming Rules:

  1. Length: Keep names under 30 characters.
  2. Characters: Use letters, numbers, and underscores.
  3. Reserved Words: Avoid Oracle reserved words.
  4. Case: Stick to uppercase (default) unless using double quotes for case sensitivity.
  5. Special Characters: Use underscores instead of spaces or symbols.
  6. Global/Local Names: Ensure uniqueness within a schema.
  7. Quoted Identifiers: Use double quotes if needed, but be aware of case sensitivity.
  8. Prefixes/Suffixes: Consider using for clarity, e.g., “EMP_” for employee-related objects.

Example:

  • table_name
  • date_of_birth
  • firstname
  • "WELCOME & ALL!"

Comments

Post a Comment