Oracle Database - Table

Card Puncher Data Processing

About

A table name must follow the naming convention of database object.

Alter

Column

  • Add
alter table MyTable add (MyColumnName VARCHAR(60) DEFAULT 'My Default Value' NOT NULL)
  • DataType
alter table MyTable modify (MyColumnName VARCHAR(60) DEFAULT 'My Default Value' NOT NULL)

Create a table

Create Table

tablespace

ALTER TABLE TABLE_NAME MOVE TABLESPACE TABLESPACE_NAME

Grants

Oracle Database - Grant with PL/SQL - DBMS_METADATA

GET_DEPENDENT_DDL

SELECT 
    DBMS_METADATA.GET_DEPENDENT_DDL(
          'OBJECT_GRANT',
          'TABLE_NAME',
          'OWNER_NAME')
    FROM DUAL;

Example:

GRANT SELECT ON "OWNER_NAME"."TABLE_NAME" TO "REPORT_ADMINS"
GRANT SELECT ON "OWNER_NAME"."TABLE_NAME" TO "SHAREPOINT"

OPEN, TRANSFORM, FETCH

set serveroutput on;
DECLARE
v_handle NUMBER;
v_transform_handle NUMBER;
-- CREATE TYPE sys.ku$_ddls IS TABLE OF sys.ku$_ddl
-- CREATE TYPE sys.ku$_ddl AS OBJECT (
--     ddlText        CLOB,
--     parsedItem     sys.ku$_parsed_items )
v_ddls  sys.ku$_ddls;
v_ddl   sys.ku$_ddl;
BEGIN
  v_handle := DBMS_METADATA.OPEN ('OBJECT_GRANT');
  v_transform_handle := DBMS_METADATA.ADD_TRANSFORM(v_handle, 'DDL');
  DBMS_METADATA.SET_TRANSFORM_PARAM(v_transform_handle,'PRETTY', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(v_transform_handle,'SQLTERMINATOR',TRUE);
  DBMS_METADATA.SET_FILTER(v_handle, 'BASE_OBJECT_NAME', 'TABLE_NAME' );
  DBMS_METADATA.SET_FILTER(v_handle, 'BASE_OBJECT_SCHEMA', 'OWNER_NAME' );
  DBMS_METADATA.SET_COUNT (v_handle, 10); -- 10 object by call to fetch
  v_ddls := DBMS_METADATA.FETCH_DDL(v_handle);
  while (v_ddls is not null) 
  LOOP
    FOR indx IN 1 .. v_ddls.COUNT
    LOOP
       v_ddl := v_ddls(indx);
      DBMS_OUTPUT.put_line ('Output: ' || v_ddl.ddlText);
    END LOOP;
    v_ddls := DBMS_METADATA.FETCH_DDL(v_handle);
    -- The FETCH_DDL function returns the DDL (to create the object) 
    -- in a sys.ku$_ddls nested table. 
    -- Each row of the sys.ku$_ddls nested table contains a single DDL statement in the ddlText column; 
    -- if requested, parsed items for the DDL statement will be returned in the parsedItems column. 
  END LOOP;
  DBMS_METADATA.CLOSE (v_handle);
END;
/
PL/SQL procedure successfully completed.
Output: GRANT SELECT ON "OWNER_NAME"."TABLE_NAME" TO "REPORT_ADMINS";
Output: GRANT SELECT ON "OWNER_NAME"."TABLE_NAME" TO "SHAREPOINT";





Discover More
Card Puncher Data Processing
Oracle - Partitions (Table and Indexes)

partition in Oracle. Partitioning enables you to store one logical object – a table or index – transparently in several independent segments. Partitioning can provide great performance improvements...
Card Puncher Data Processing
Oracle Database - (Integrity) Constraints

Constraints on tables. Violation of constraint is not a syntax error but a run-time error. See A UNIQUE key integrity constraint requires that every value in a column or set of columns (key)...
Oracle Segment Extent Data Block
Oracle Database - (Logical) Segment (Database Object Storage)

A segment is a logical storage structure that contains data structure (segment type) of database objects. For example: each table's data is stored in its own data segment, while each index's data...
Card Puncher Data Processing
Oracle Database - (Online table) Redefinition (DBMS_REDEFINITION)

Online table redefinition is a mechanism to make table structure modifications without significantly affecting the availability of the table. You can perform online table redefinition: with the Oracle...
Card Puncher Data Processing
Oracle Database - Data Load

Data loading in a Oracle Database environment. In the Oracle Database, you can load data in the following elements: Table, View , Materialized View, External Table Oracle Version Utility...
Card Puncher Data Processing
Oracle Database - External Tables

External tables feature lets you access data in external sources as if it were in a table in the database. The external tables feature is a complement to existing SQLLoader functionality. External tables...
Card Puncher Data Processing
Oracle Database - NOLOGGING

A table of a statement marked as NOLOGGING will bypass the generation. direct path insert The operation will not be logged and then you prevent the redo log to be generated. It's not in the...
Card Puncher Data Processing
Oracle Database - Objects

Database object. Each database object is considered to either be: a schema object (ie owned by a user) or a “non-schema object”. See for plsql object type: Object in different namespace are...
Card Puncher Data Processing
Oracle Database - Primary Key

In Oracle Database, a Primary Key is a constraint implemented as: a “not null” constraint combined with an “Unique” constraint . A table may have only one primary key. A composite...
Card Puncher Data Processing
Oracle Database - Schema Object

The schema Objects are logical structures of data stored physically in data file and categorized in a schema. private synonym Database objects that are owned by a user are schema...



Share this page:
Follow us:
Task Runner