[DATABASE] Tổng hợp 30 câu truy vấn hữu ích trong Oracle

SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"
FROM DUAL;

SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"    
FROM DUAL;

SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day"
FROM DUAL;

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL

SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days
FROM DUAL;

SELECT SYSDATE,
       LAST_DAY (SYSDATE) "Last",
       LAST_DAY (SYSDATE) - SYSDATE "Days left"
  FROM DUAL;

SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0)
          num_of_days
  FROM DUAL;
 
OR
 
SELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees;

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date,
       TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date
  FROM XMLTABLE (
          'for $i in 0 to xs:int(D) return $i'
          PASSING XMLELEMENT (
                     d,
                     FLOOR (
                        MONTHS_BETWEEN (
                           ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12),
                           SYSDATE)))
          COLUMNS i INTEGER PATH '.');

SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning
  FROM DUAL;

SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left
  FROM DUAL;

SELECT table_name
  FROM user_tables
 WHERE table_name = 'TABLE_NAME';

SELECT column_name AS FOUND
  FROM user_tab_cols
 WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';

SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;

SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;

ALTER SESSION SET CURRENT_SCHEMA = new_schema;

SELECT * FROM v$version;

SELECT username,
       profile,
       default_tablespace,
       temporary_tablespace
  FROM dba_users;

SELECT * FROM nls_database_parameters;

SELECT VALUE
  FROM v$system_parameter
 WHERE name = 'compatible';

ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;

SELECT SUBSTR (file_name, 1, 50), AUTOEXTENSIBLE FROM dba_data_files;
 
(OR)
 
SELECT tablespace_name, AUTOEXTENSIBLE FROM dba_data_files;

ALTER TABLESPACE data01 ADD DATAFILE '/work/oradata/STARTST/data01.dbf'
    SIZE 1000M AUTOEXTEND OFF;

ALTER DATABASE DATAFILE '/u01/app/Test_data_01.dbf' RESIZE 2G;<br>

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;<br>

SELECT SUM (bytes / 1024 / 1024) "size"
  FROM dba_segments
 WHERE owner = '&owner';

SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME,
         s.program || '-' || s.terminal || '(' || s.machine || ')' PROG,
         s.sid || '/' || s.serial# sid,
         s.status "Status",
         p.spid,
         sql_text sqltext
    FROM v$sqltext_with_newlines t, V$SESSION s, v$process p
   WHERE     t.address = s.sql_address
         AND p.addr = s.paddr(+)
         AND t.hash_value = s.sql_hash_value
ORDER BY s.sid, t.piece;

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    FROM v$session ss, v$sesstat se, v$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

SELECT b.sid,
       b.serial#,
       a.spid processid,
       b.process clientpid
  FROM v$process a, v$session b
 WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');

SELECT owner, COUNT (owner) number_of_objects
    FROM dba_objects
GROUP BY owner
ORDER BY number_of_objects DESC;