Now
SYSDATE
Tomorow/ next day
SYSDATE + 1
Seven days from now
SYSDATE + 7
One hour from now
SYSDATE + 1/24
Three hours from now
SYSDATE + 3/24
An half hour from now
SYSDATE + 1/48
10 minutes from now
SYSDATE + 10/1440
1 minute from now
SYSDATE + 1/1440
Tomorrow at 12 midnight
TRUNC(SYSDATE + 1)
Tomorrow at 10 AM
TRUNC(SYSDATE + 1) + 10/24
A collegue of mine had to debug all his code, twice, to try to find why one of his tables wasn’t populated.
Here is why: He asked to execute a script (script didn’t contained a commit statement) in sql*plus at someone of the production team. That guy opened sql*plus, executed the script and closed window (File>Exit) [...]
PROCEDURE exec_clob_sql_statement(vv_clob_sql_statement IN clob) IS
v_cur INTEGER := DBMS_SQL.open_cursor;
v_offset PLS_INTEGER :=1;
v_nb PLS_INTEGER :=0;
v_sql_table DBMS_SQL.varchar2a;
v_len PLS_INTEGER;
v_retval INTEGER;
BEGIN
v_len := DBMS_LOB.getlength(vv_clob_sql_statement);
LOOP
v_nb:=v_nb+1;
v_sql_table(v_nb):=DBMS_LOB.SUBSTR(vv_clob_sql_statement
,LEAST(4000,v_len - v_offset + 1)
,v_offset
);
v_offset:=v_offset + 4000;
EXIT WHEN v_offset > v_len;
END LOOP;
DBMS_SQL.parse(v_cur, v_sql_table, 1, v_nb, FALSE, DBMS_SQL.native);
v_retval:=DBMS_SQL.EXECUTE(v_cur);
DBMS_SQL.close_cursor(v_cur);
EXCEPTION WHEN OTHERS THEN
IF DBMS_SQL.is_open(v_cur) THEN DBMS_SQL.close_cursor(v_cur); END IF;
RAISE;
END exec_clob_sql_statement;
TABLE not partitioned:
select segment_name, bytes / 1024 / 1024 MB
from user_segments
Where segment_type = ‘TABLE’
and segment_Name=<TABLE_NAME>;
PARTITION:
select segment_name, partition_name, bytes / 1024 / 1024 MB
from user_segments
Where segment_type = ‘TABLE PARTITION’
and Segment_Name=<TABLE_NAME>
and partition_name = <PARTITION_NAME>;
TABLE partitioned:
select segment_name, sum(bytes / 1024 / 1024) MB
from user_segments
Where segment_type = ‘TABLE PARTITION’
and Segment_Name=<TABLE_NAME>
Group by segment_name;
