FLASHBACK VERSIONS BETWEEN

select * from hr.prueba;

update hr.prueba set salary=25000 where employee_id=100;
commit;

select salary from hr.prueba
as of timestamp (systimestamp - interval '1' MINUTE)
where employee_id=100;

select versions_starttime, versions_endtime, employee_id,salary from hr.prueba
versions between scn minvalue and maxvalue
where employee_id=100;

JOIN V$SESSION TO V$SQL

SELECT OSUSER, SERIAL#, SID, executions, sql.SQL_ID ,sql.child_number, SQL_TEXT
FROM V$SESSION sess JOIN V$SQL sql
on  (sess.SQL_ADDRESS = sql.ADDRESS)
where sess.STATUS = 'ACTIVE'


PARA ANALIZAR BLOQUEOS

SELECT OSUSER, SERIAL#, SID, executions, sql.SQL_ID ,sql.child_number, SQL_TEXT
FROM V$SESSION sess JOIN V$SQL sql
on  (sess.SQL_ADDRESS = sql.ADDRESS)
where sid in (select blocking_session from v$session)
and sess.STATUS = 'ACTIVE'

quota tablespace oracle

Set tablespace quota to 10G for user

1. ALTER USER SCOTT QUOTA 10G ON USERS;

SELECT TABLESPACE_NAME,

BYTES / 1024 / 1024 "UTILIZIED_SPACE",

MAX_BYTES / 1024 / 1024 "QUOTA_ALLOCATED"

FROM dba_ts_quotas

WHERE username = 'SCOTT';

SEQUENCE

CREATE SEQUENCE id_seq

    INCREMENT BY 10

    START WITH 10

    MINVALUE 10

    MAXVALUE 100

    CYCLE

    CACHE 2;

SELECT

    id_seq.NEXTVAL

FROM

    dual;

SELECT

    id_seq.CURRVAL

FROM

    dual;

alter sequence id_seq nomaxvalue;

Configuring the NTP Service Oracle Linux

3.4.4. Configuring the NTP Service

When an Oracle VM Server is discovered from Oracle VM Manager, NTP (Network Time Protocol) is automatically configured and enabled to ensure time synchronization. Oracle VM automatically configures Oracle VM Manager as the NTP source for all Oracle VM Servers under its control.
However, to provide time services to the Oracle VM Servers, NTP must first be installed and configured on the Oracle VM Manager host server. Make sure that your Oracle VM Manager host is either registered with the Unbreakable Linux Network (ULN) or configured to use Oracle's public YUM service.

To configure NTP on the Oracle VM Manager host:

1. Install the NTP package.
   

   # yum install ntp

   Once NTP is installed, configure it to both synchronize with upstream servers and provide time services to the local network, where the Oracle VM Servers reside.
2. Enable upstream synchronization by defining the upstream time servers in the ntp.conf file.
   Oracle Linux uses three public NTP servers as upstream time sources by default. Check with your network administrator if time services are provided on the corporate network and, if necessary, replace the default entries with the names or IP addresses of your NTP servers.
   Use your favorite text editor to modify /etc/ntp.conf. Search for these entries:
   

   # vi /etc/ntp.conf
   
   server 0.rhel.pool.ntp.org
   server 1.rhel.pool.ntp.org
   server 2.rhel.pool.ntp.org

3. Configure downstream access for the Oracle VM Servers in your local network. To do so, disable the default "noquery" option for the Oracle VM Server management network.
   Again in the /etc/ntp.conf file, search for this entry:
   

   #restrict 192.168.1.0 mask 255.255.255.0 nomodify notrap

   Replace 192.168.1.0 with the network address of your Oracle VM Server management network. You may also need to adjust the netmask (255.255.255.0). Remove the # character at the start of the line to uncomment this entry. Once you have finished editing, save the /etc/ntp.conf file.
4. Start the NTP service and set it to launch automatically upon boot.
   

   # service ntpd start
   # chkconfig ntpd on

5. When NTP is running, check upstream synchronization. The ntpq command should output something similar to this example:
   

   # ntpq -p
   
        remote           refid      st t when poll reach   delay   offset  jitter
   ==============================================================================
    lists2.luv.asn. 203.161.12.165  16 u   25   64    3    3.495   -3043.1   0.678
    ns2.novatelbg.n 130.95.179.80   16 u   27   64    3   26.633   -3016.1   0.797
    sp1.mycdn.fr    130.234.255.83  16 u   24   64    3    4.314   -3036.3   1.039

   When upstream NTP is working properly, delay and offset values should be non-zero and the jitter value should be below 100.
6. Test downstream synchronization from another server in the management network to ensure that NTP services are working. Note that it may take several minutes before your NTP server is able to provide time services to downstream clients. Begin by checking the stratum level of your server:
   

   # ntpq -c rv
   
   assID=0 status=c011 sync_alarm, sync_unspec, 1 event, event_restart,
   version="ntpd 4.2.4p8@1.1612-o Tue Jul  6 21:50:26 UTC 2010 (1)",
   processor="x86_64", system="Linux/2.6.32-200.19.1.el6uek.x86_64",
   leap=11,  stratum=16 , precision=-20, rootdelay=0.000,
   rootdispersion=1.020, peer=0, refid=INIT,
   reftime=00000000.00000000  Thu, Feb  7 2036 17:28:16.000, poll=6,
   clock=d21d4a96.a26c5962  Fri, Sep 16 2011 14:09:58.634, state=0,
   offset=0.000, frequency=0.000, jitter=0.001, noise=0.001,
   stability=0.000, tai=0   

   If the server is showing stratum=16, wait a few minutes and try again. It may take up to 15 minutes for an NTP server to stabilize sufficiently to lower its stratum level.
   Downstream clients will not synchronize with a server at stratum level 16. Once the stratum level has dropped, log in to any available Linux host in your management network and issue the following command:
   

   # ntpdate -d manager.hostname
   [...]
   16 Sep 13:58:25 ntpdate[1603]: step time server 192.168.1.1 offset 3.009257 sec

   This command runs ntpdate in debug mode, in which case the availability of the remote time server is checked. If the debug run ends in a line similar to the one in this example, the test is successful. In case the stratum level is still too high, the error message "Server dropped: strata too high" is displayed.

Once you have confirmed with these tests that NTP is working properly for your environment, you should not need to check this again.

Caution

NTP communicates over UDP port 123. Ensure that no firewall is blocking this traffic.