Oracle Objects

Index
Synonym
Sequence
Materialized View
Access Control List

Sequence

Sequence

A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key .

SQL> select object_name,object_type,owner from dba_objects where object_name='SOA_B2B_SEQUENCE';

OBJECT_NAME                    OBJECT_TYPE         OWNER

------------------------------ ------------------- ------------------------------

SOA_B2B_SEQUENCE               SEQUENCE            SOACONFIG

SQL> conn SOACONFIG/****;

Connected.

SQL> select dbms_metadata.get_ddl('SEQUENCE', 'SOA_B2B_SEQUENCE') from dual;

DBMS_METADATA.GET_DDL('SEQUENCE','SOA_B2B_SEQUENCE')

--------------------------------------------------------------------------------

   CREATE SEQUENCE  "SOACONFIG"."SOA_B2B_SEQUENCE"  MINVALUE 1 MAXVALUE 99999999

9999999999999999999 INCREMENT BY 1 START WITH 1951246 CACHE 20 NOORDER  NOCYCLE

=============================================

How to increment/reset sequence.

 Either we can drop and recreate . But it can be incremented with negative number .

 Increment two time ,update with increment 1

SQL> alter sequence SOA_B2B_SEQUENCE increment by -1902490;

Sequence altered.

SQL> select SOA_B2B_SEQUENCE.nextval from dual;

   NEXTVAL

----------

    999998

SQL> alter sequence SOA_B2B_SEQUENCE increment by 1;

Sequence altered.

SQL> select SOA_B2B_SEQUENCE.nextval from dual;

   NEXTVAL

----------

    999999

SQL> select SOA_B2B_SEQUENCE.nextval from dual;

   NEXTVAL

----------

   1000000

SQL> select SOA_B2B_SEQUENCE.nextval from dual;

   NEXTVAL

----------

   1000001

Synonym

Synonym

Synonym is a pointer.

Recompilation or recreate or replace object types does not invalidate the Synonym. (in 10g and before, it will in 11g and later, it will not )

The synonym will automagically 'fix' itself when referenced, you need to do nothing

select dbms_metadata.get_ddl('SYNONYM','AR_CREDIT_MEMO_LINES','NTX_AR') from dual;

  CREATE OR REPLACE EDITIONABLE SYNONYM "NTX_AR"."AR_CREDIT_MEMO_LINES" FOR "NOE

TIX_SYS"."AR_CREDIT_MEMO_LINES"

    AR_CREDIT_MEMO_LINES - View 

    NOETIX_SYS   - View owner

    NTX_AR       - Private Synonym owner

Materialized View

Refreshing a materialised view -FAST and COMPLETE

ORA-12012: error on auto execute of job **ORA-12048:

error encountered while refreshing materialized view "GENESIS"."RA_CUST_RELATE"

ORA-12057: materialized view or zonemap "GENESIS"."RA_CUST_RELAT" is invalid and must use complete refresh

Need to perform Complete refresh for one time, later it can be FAST refreshed large snapshot We may want to run it in parallel mode.

execute dbms_snapshot.refresh (LIST=>'RA_CUST_RELATE ' ,PARALLELISM=>4, METHOD=>'C');

Index

Index alteration

  To get the unusable indexes and set them fix.

select owner,index_name,status,degree from dba_indexes where index_name='I_SNAP$_RA_CUST_RELATE_SNA';

                                                 -- Degree = parallelism,Number of parallel operations on table/index.

                                                 -- Status   =  Valid,Unusable ( you did  some sort of direct path operation) 

    

alter index GENESIS.I_SNAP$_RA_CUST_RELATE_SNA rebuild online compute statistics
                                                -- Online = NO blocking
                                                --  nologging;

alter index GENESIS.I_SNAP$_RA_CUST_RELATE_SNA rebuild online parallel 10;

      +++++++++++++++++++++++++++

  select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;'

  2  from dba_indexes

  3  where owner=upper('hr');

set lin 1000

set pagesize 1000

spool index_170715.sql

select 'alter index '||owner||'.'||index_name ||' rebuild ;' from dba_indexes where owner='ODB';

spool off

Access Control List

ACL in oracle


Creating ACL

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          =>'https_permissions.xml',
        description  => 'ACL for users to send mail.',
        principal    => 'USER',
        is_grant     => TRUE,
        privilege    => 'connect',
        start_date   => null,
        end_date     => null
    );
END;
/

Add privilege to user
Use below to add a privilege to grant or deny the network access to the user. The access control entry (ACE) will be created if it does not exist.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl         => 'https_permissions.xml',
        principal   => 'USER',
        is_grant    =>  TRUE,
        privilege   => 'connect');
END;
/

Assing host to ACL
Use Below to assign an access control list (ACL) to a host computer, domain, or IP subnet, and if specified, the TCP port range.
BEGIN
dbms_network_acl_admin.assign_acl (
acl => 'https_permissions.xml',
host => 'hostname.com',
lower_port => 1,
upper_port => 10000
);
END;
/
commit;

dont forget to give commit at end of the session to make these changes permanent.

Revoke priviledge from a user
The below procedure deletes a privilege in an access control list.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege (
    acl         => 'SCOTT.xml',
    principal   => 'MN',
    is_grant    => TRUE,
    privilege   => 'connect');
  COMMIT;
END;
/





SELECT HOST, LOWER_PORT, UPPER_PORT, ACL,
   DECODE(
     DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid,  'USER', 'connect'),
     1, 'GRANTED', 0, 'DENIED', null) PRIVILEGE
FROM DBA_NETWORK_ACLS
WHERE host IN
  (SELECT * FROM      TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('hostname.com')))
 ORDER BY
  DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) DESC, LOWER_PORT, UPPER_PORT;

HOST                 LOWER_PORT  UPPER_PORT  ACL           PRIVILE
-------------------- ---------- ------------------------------
hostname.com     1         10000        /sys/acls/https_permissions.xml GRANTED



 Adding additional users to the ACL use below

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl         => 'SCOTT.xml',
        principal   => 'MN',
        is_grant    =>  TRUE,
        privilege   => 'connect');
END;
/


BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
     acl         => 'SCOTT.xml',
     host        => 'Mail Server name',
     lower_port => 25);
END;
/
commit;