Some good Oracle PL/SQL statements :]

1.) get all tablespaces
select   TABLESPACE_NAME,
    INITIAL_EXTENT,
    NEXT_EXTENT,
    MIN_EXTENTS,
    MAX_EXTENTS,
    PCT_INCREASE,
    STATUS,
    CONTENTS
from     dba_tablespaces
order     by TABLESPACE_NAME
2.) count points of polygon,line
select id,SDO_UTIL.GETNUMVERTICES(gt.GEOM) as count_point 
from GEOM_TABLE gt 
order by count_point desc;
3.) select where text has newline, space, tab
SELECT * FROM TEXT_DATA 
WHERE REGEXP_LIKE(TEXT_NOTE, '[[:space:]]{2}');
4.) get the time difference between two timestamp columns
SELECT SUBSTR(DATE_TIME_FROM,1,30) "Time1",
       SUBSTR(DATE_TIME_TO,1,30) "Time2",
       SUBSTR((DATE_TIME_TO-DATE_TIME_FROM),1,30) "difference",
       extract(HOUR from(DATE_TIME_TO-DATE_TIME_FROM)) "difference in hours",
       extract(MINUTE from(DATE_TIME_TO-DATE_TIME_FROM)) "difference in minutes",
       extract(SECOND from(DATE_TIME_TO-DATE_TIME_FROM)) "difference in secunds"
 FROM ACTIVITES;
WHERE DATE_TIME_FROM IS NOT NULL;
5.) create  MDSYS spatial index
CREATE INDEX BORDER_T_GI ON BORDER_T(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX
6.) calculate bounding box of geometry
select SDO_AGGR_MBR(a.geom).SDO_ORDINATES 
from GEOM_TABLE a 
where id = 132080703;

Comments

Popular Posts