Tablespace Data mining

    Tablespace Data mining
  -----------------------------
    
      Let me call some of my findings as Oracle Tablespace Data minig some I dont know really what to name this some may call it as Tablespace cracking or hacking but I dont think so ;because what i done is just some analysis with  my current Database and its . DBF and .LOG file so we can call Tablespace Data-mining .


Note : When you are experimenting try to copy .DBF and .LOG file and experiment on the copied file .Most probably you may more details other than mentioned here .
Do experiment .This is for non-encrypted tablespace


        In Redo Tablespace it records the changes , If you open the RED.LOG file in a text editor you will get dbms_job's 'WHAT' details if a procedure or function is given if query is given we will not get the query detail .Then NLS_LANGUAGE parameter can be searched then search for MAXVALUE keyword

If you open the tablespace you created for a particular schema or Database you will able to see what all data are stored in that DB in that tablespace

Oracle Exdata

Exdata is a hardware machine which runs oracle database; supports OLTP, Database

Exologic is a hardware machine for application and primarily for cloud computing

Exadata X2-2 Hardware Architecture (FULL Rack)

DBA's has to think about storage server, DB server, storage connecting devices, fiber channel connectivity, Network components, OS, storage drives this all includes in Exdata

In Storage Grid of Exdata it contains 2 Xenon CPU with computing power which take care of processing inside the storages 

Flash is the key performance of Exdata machine. Its a kind of h/w in a pci slot where all your data is stored intermediately between db server and storage server. 

InfiniBand Networks (fiber channel has only 10gb/s) which is also a key performance of Exdata ;which used to transfer data between db server to storage which has high bandwidth

In Exdata is a preconfigured plug and play software .

Key Performance of Exdata

1)      Intelligent Storage Grid

2)      Hybrid Columnar Compression will compress 10 times the data

3)      Smart Flash Cache (patented product of Oracle) different from SSD; it’s a second SGA at storage level which also contains algorithm which drives the flash cache.

In Traditional San if we increase the storage it will not increase the db performance because the spindles of hardware will perform as usual to read from storage

In Exdata there storage is intelligent.  (www.tpc.org to get which is the fastest machine)

ROW level locking

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

it means any moment of time any person can do DML at a moment of time so entire table is locked ,so oracle introduced rowlevel locking mechanism which makes oracle faster

Rowlevel is managed at block level ultimate storage element of ur row.

RMAN is the backup in exdata

TIMESTAMP Datatypes

Once the database time zone is set (with ALTER DATABASE SET TIME_ZONE command) and you have defined single table with a TSWLTZ column in the database, you're locked to that time zone - Oracle won't let you change the database time zone because this will implicitly alter all TSWLTZ columns data - remember that they only hold the offset from the database time zone? If you will change the time zone, TSWLTZ will yeld different timestamps because it will add or substract the offset from new database time zone, not the one that was in effect when you created the row, and this will result in wrong data being returned. So if you attempt to alter the database time zone with at least one column of TSWLTZ type defined in the database, Oracle will throw this rather misguiding error.

Worse yet, TSWLTZ data type is not suited well for distributed systems where several databases around the world serve the application: each database will probably have its own time zone and TSWLTZ data in these databases will not be interchangeable between them. TSWTZ is free from this effect, because original time zone is always known. But then again, you certainly would want to present that data to users residing in different time zones and you would want to show what time it was in THEIR time zone, won't you? Oracle knew about that, and they extended the Datetime Expression syntax with AT clause to allow you to convert TSWTZ to different time zones easily:

For detail read please refer this link

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

 select * from V$TIMEZONE_NAMES;

SELECT TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 US/Eastern','YYYY-MM-DD HH24:MI TZR'),'YYYY-MM-DD HH24:MI TZH:TZM') "US/Eastern",

TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 US/Eastern','YYYY-MM-DD HH24:MI TZR') AT TIME ZONE 'US/Pacific','YYYY-MM-DD HH24:MI TZH:TZM') "US/Pacific" FROM DUAL;

SELECT TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 Europe/Moscow','YYYY-MM-DD HH24:MI TZR'),'YYYY-MM-DD HH24:MI TZH:TZM')"Daylight" FROM SYS.DUAL;

select SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00') kl,SYS_EXTRACT_UTC(SYSTIMESTAMP) sy , CURRENT_TIMESTAMP,  DBTIMEZONE , localtimestamp ,extract(hour from systimestamp),systimestamp,to_timestamp_tz('10/07/2004', 'DD/MM/YYYY') now,systimestamp(2) ,SESSIONTIMEZONE from dual;

Some examples

.

Spatial Error Related with SDO_NN

SELECT sdo_nn_distance (1) dist, a.cid,

sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 1) x,

sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 2) y

FROM code_boundaries a, golf_boundaries b

WHERE sdo_nn (a.sdo_geometry, b.sdo_geometry, 'sdo_num_res=1 UNIT=foot ', 1) = 'TRUE'

AND b.gid = 1341

When executing this sql  following error occurs .

ORA-13249: SDO_NN cannot be evaluated without using index

ORA-06512: at "MDSYS.MD", line 1723

ORA-06512: at "MDSYS.MDERR", line 17

ORA-06512: at "MDSYS.PRVT_IDX", line 9

We can resolve this error by removing  SDO_NN_DISTANCE ancillary operator from the query but what to do when we require distance what I have noted   is that it is due to non-spatial column which is in where clause is not indexed .When you create a index for that non-spatial column the query will execute

SQL > create index idx on golf_boundaries(gid) ;

SELECT sdo_nn_distance (1) dist, a.cid,

sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 1) x,

sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 2) y
FROM code_boundaries a, golf_boundaries b

WHERE sdo_nn (a.sdo_geometry, b.sdo_geometry, 'sdo_num_res=1 UNIT=foot ', 1) = 'TRUE'

AND b.gid = 1341 ;

cool

-------

DBMS_JOB VS DBMS_SCHEDULER

- logging of job runs (job history) (maintain history and a copy of the log file in the database)
- simple but powerful scheduling syntax (similar to but more powerful than cron syntax)
- running of jobs outside of the database on the operating system (can schedule O/S scripts)
- resource management between different classes of jobs
- use of job arguments including passing of objects into stored procedures
- privilege-based security model for jobs
- naming of jobs and comments in jobs
- stored, reusable schedules
- dependencies between job units (10gR2 and up)
- scheduling based on financial calendars and fiscal quarters (10gR2 and up)
- event based jobs which run when an event is received (10gR2 and up)
- running of jobs on remote machines (11gR1 and up)
- e-mail notifications on job events of interest (10gR2 and up)
- starting a job based on arrival of a file (10gR2 and up)

dbms_scheduler has 200 percent more functionality than dbms_job and it makes the windows scheduler and cron look very silly.