TimeZones in SQL

Current time
SQL> select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"
2 from dual;

Current Time
------------------------
Tue 10-Feb-2009 00:40:27

[OR]

SQL> alter session set nls_date_format='dd-mon-yyyy HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
10-feb-2009 00:47:48

[OR]


SQL> select current_date from dual;

CURRENT_DATE
--------------------
10-feb-2009 00:48:52

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
----------------------------------------------------
-05:00

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
------------------------------------------------------
10-FEB-09 12.53.13.998000 AM -05:00

Above returns TimeStamp with Time ZOne

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
------------------------------------------
10-FEB-09 12.54.29.396000 AM

Above returns only timestamp



SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
-----------------------------------------------
-05:00

SQL> select dbtimezone from dual;

DBTIME
------
+00:00


SQL> select hiredate from emp;

HIREDATE
--------------------
17-dec-1980 00:00:00
20-feb-1981 00:00:00
22-feb-1981 00:00:00
02-apr-1981 00:00:00
28-sep-1981 00:00:00
01-may-1981 00:00:00
09-jun-1981 00:00:00
19-apr-1987 00:00:00
17-nov-1981 00:00:00
08-sep-1981 00:00:00
23-may-1987 00:00:00

HIREDATE
--------------------
03-dec-1981 00:00:00
03-dec-1981 00:00:00
23-jan-1982 00:00:00

14 rows selected.


SQL> desc emp
Name Null? Type
----------------------------------------- -------- -----------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)


You can convert from DATE to TIMESTAMP when the column has data, but you cannot convert from DATE or TIMESTAMP to TIMESTAMP WITH TIME ZONE unless the column is empty.

You can specify the fractional seconds precision for timestamp. If none is specified, as in the above example, then it defaults to 6.

SQL> alter table emp modify hiredate timestamp(7);

Table altered.

SQL> select hiredate from emp;

HIREDATE
------------------------------------
17-DEC-80 12.00.00.0000000 AM
20-FEB-81 12.00.00.0000000 AM
22-FEB-81 12.00.00.0000000 AM
02-APR-81 12.00.00.0000000 AM
28-SEP-81 12.00.00.0000000 AM
01-MAY-81 12.00.00.0000000 AM
09-JUN-81 12.00.00.0000000 AM
19-APR-87 12.00.00.0000000 AM
17-NOV-81 12.00.00.0000000 AM
08-SEP-81 12.00.00.0000000 AM
23-MAY-87 12.00.00.0000000 AM

HIREDATE
------------------------------------
03-DEC-81 12.00.00.0000000 AM
03-DEC-81 12.00.00.0000000 AM
23-JAN-82 12.00.00.0000000 AM

14 rows selected.

EXTRACT function
------------
SELECT EXTRACT ([YEAR] [MONTH][DAY] [HOUR] [MINUTE][SECOND]
[TIMEZONE_HOUR] [TIMEZONE_MINUTE]
[TIMEZONE_REGION] [TIMEZONE_ABBR]
FROM [datetime_value_expression] [interval_value_expression]);


SQL> select extract(year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
2009

SQL> select extract(timezone_region from current_timestamp) from dual;

EXTRACT(TIMEZONE_REGIONFROMCURRENT_TIMESTAMP)
----------------------------------------------------------------
UNKNOWN

SQL> select extract(timezone_abbr from current_timestamp) from dual;

EXTRACT(TI
----------
UNK

TZ_OFFSET function:
--------------------------
returns the time zone offset. For example, if the function returns -05:00, it indicates that the time zone where the command was executed is five hours behind UTC (Coordinated Universal Time).


SQL> select tz_offset(sessiontimezone) from dual;

TZ_OFFS
-------
-05:00

SQL> select tz_offset(dbtimezone) from dual;

TZ_OFFS
-------
+00:00

Query v$timezone_names to get valid time zone name values:
SQL> select * from v$timezone_names;


FROM_TZ function: Converts a TIMESTAMP value to TIMESTAMP WITH TIME ZONE value
----------------------------

SQL> select from_tz(timestamp '2008-03-20 10:00:00', 'US/Pacific') from dual;

FROM_TZ(TIMESTAMP'2008-03-2010:00:00','US/PACIFIC')
---------------------------------------------------------------------------
20-MAR-08 10.00.00.000000000 AM US/PACIFIC


SQL> select from_tz(timestamp '2008-03-20 10:00:00', '-05:00') from dual;

FROM_TZ(TIMESTAMP'2008-03-2010:00:00','-05:00')
---------------------------------------------------------------------------
20-MAR-08 10.00.00.000000000 AM -05:00

TO_TIMESTAMP
---------------
SQL> select to_timestamp('05-12-09 13:20:00', 'MM-DD-YY HH24:MI:SS') from dual;

TO_TIMESTAMP('05-12-0913:20:00','MM-DD-YYHH24:MI:SS')
---------------------------------------------------------------------------
12-MAY-09 01.20.00.000000000 PM


TO_TIMESTAMP_TZ
-------------------
SQL> select to_timestamp_tz('05-12-09 13:20:00 -5:00', 'MM-DD-YY HH24:MI:SS TZH:TZM') from dual;

TO_TIMESTAMP_TZ('05-12-0913:20:00-5:00','MM-DD-YYHH24:MI:SSTZH:TZM')
---------------------------------------------------------------------------
12-MAY-09 01.20.00.000000000 PM -05:00


TO_YMINTERVAL: Convert character string to an INTERVAL YEAR TO MONTH datatype.
-------------------
SQL> select hiredate from emp;

HIREDATE
-----------------------------------------
17-DEC-80 12.00.00.0000000 AM
20-FEB-81 12.00.00.0000000 AM
22-FEB-81 12.00.00.0000000 AM

SQL> select hiredate + to_yminterval('02-01') as new_hire_date from emp;

NEW_HIRE_DATE
-------------------------------------------------------------------------
17-JAN-83 12.00.00.000000000 AM
20-MAR-83 12.00.00.000000000 AM
22-MAR-83 12.00.00.000000000 AM

The character string can also have negative value. Belos, it returns a date that is one year and two months before the hire date.
SQL> select hiredate + to_yminterval('-01-02') as new_hire_date from emp;

NEW_HIRE_DATE
---------------------------------------------------------------------------
17-OCT-79 12.00.00.000000000 AM
20-DEC-79 12.00.00.000000000 AM
22-DEC-79 12.00.00.000000000 AM

Displaying table metadata (data about data)

To find the names of the tables that you have created, use the view called cat or tab in the SELECT statement.

SQL> select * from cat;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
SAMPLE TABLE
TEST TABLE
CHILD TABLE
SEQ_TEST SEQUENCE
SEQSAMPLE TABLE
BIN$U6KfB5ApT1O9xKAFd9PnsA==$0 TABLE
BIN$0Mf6cwpmTnOXVUmXDBLnPA==$0 TABLE
TESTA TABLE
BIN$9iac65ZYQvOMzxudpHYijw==$0 TABLE
MYTEST TABLE

21 rows selected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
XYZ TABLE
TESTB TABLE
BIN$7E43RxQORwuc7tISq/SZVA==$0 TABLE
MYTAB TABLE
DIGITS TABLE
BIN$jwDk7ZM8TdO3TOBSmGrTeQ==$0 TABLE
MASTER TABLE

20 rows selected.



SQL> select interval '312' month,
interval '20-6' year to month,
interval '12:30:10.1234567' hour to second from dual;

INTERVAL'312'MONTH
-------------------------------------------------------------
INTERVAL'20-6'YEARTOMONTH
-------------------------------------------------------------
INTERVAL'12:30:10.1234567'HOURTOSECOND
-------------------------------------------------------------
+26-00
+20-06
+00 12:30:10.123457

Retrieve number of records in a table having column data as NULL

SQL statement that would retrieve the number of records having 'NAME' as NULL

Sample table that has NULL value for NAME column.
SQL> select * from mytab;

TNO NAME
---------- ---------------
1 vinay
2 suman
3 ravi
4 sai
5 sample
6
7

7 rows selected.

Incorrect Solutions:
SQL> select count(name) from mytab where name=NULL;

COUNT(NAME)
-----------
0

SQL> select count(name) from mytab where name IS NULL;

COUNT(NAME)
-----------
0

Correct Solution:
SQL> select count(nvl(name, 0)) from mytab where name is null;

COUNT(NVL(NAME,0))
------------------
2

Query to retrieve Nth highest salary records

Get the records with Nth highest salary of employee:
SQL> select empno, ename, sal from emp
where sal = (select max(sal) from emp e1 where n<(select count(*) from emp e2 where e1.sal<=e2.sal));

Ex: To get the records that has 2nd highest salary in the emp table:

SQL> select empno, ename, sal from emp
where sal = (select max(sal) from emp e1 where 2<(select count(*) from emp e2 where e1.sal<=e2.sal));

EMPNO ENAME SAL
---------- ---------- ----------
7788 SCOTT 3000
7902 FORD 3000

Identifying and Releasing Locks on objects

First identify the object id that is holding the lock. Query dba_locks,v$lock, v$lock_object to get the SID and object_id

SQL> select * from dba_locks
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED

LOCK_ID1
---------- -------------------------- ---------------------------------------- ---------------------
145 DML Row-X (SX) None

51966

SQL> select * from v$lock;


SQL> select sid, username from v$session;
SID USERNAME
------ ------------------
143
145 SCOTT
146 SCOTT


SQL> select session_id, process, locked_mode from v$locked_object where object_id=51966;

SESSION_ID PROCESS LOCKED_MODE
---------- ------------ -----------
145 1712:5368 3


SQL> select object_id from user_objects where object_name='SAMPLE';

OBJECT_ID
----------
51966
OR

SQL> select oracle_username os_user_name, locked_mode, object_name, object_type from v$locked_object a,dba_objects b where a.object_id = b.object_id;

OS_USER_NAME LOCKED_MODE OBJECT_NAME
------------------------------ ----------- ---------------------------------------------------------
SCOTT 3 SAMPLE

Also identify the serial# of the sid that is holding the lock.
SQL> select sid, serial#, username from v$session;

SID SERIAL# USERNAME
---------- ---------- ------------------------------
143 38
145 327 SCOTT
146 237 SCOTT
147 1886
154 1
158 4
160 1
161 1
162 1
163 1
164 1

SID SERIAL# USERNAME
---------- ---------- ------------------------------
165 1
166 1
167 1
168 1
169 1
170 1

17 rows selected.


Then, using the sid and serial#, you can kill the session to release the locks. Kill the session only when user is unavailable or in case of emergency. Or you can request the user to commit or rollback to release the locks.

SQL> alter system kill session '146, 237';

System altered.

Note that you cannot kill your own session.
SQL> alter system kill session '145, 327';
alter system kill session '145, 327'
*
ERROR at line 1:
ORA-00027: cannot kill current session

Get object_id of Database Object

To get the object id of an object you can query user_objects. Note that object_name is case-sensitive.

SQL> select object_id from user_objects where object_name='sample';

no rows selected

SQL> select object_id from user_objects where object_name='SAMPLE';

OBJECT_ID
----------
53673

[OR]

You can query dba_objects. Note that owner name is case-sensitive
SQL> select object_id, object_name from dba_objects where owner='SCOTT';

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------
53930 MASTER
53931 MASTER_MNO_PK
53932 CHILD1
53933 BIN$HfQPV+0lQOWmj9HXRXQ01w==$0
53934 CHILD2
51250 PK_DEPT
51249 DEPT
51251 EMP
51252 PK_EMP
51253 BONUS
51254 SALGRADE

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------
53673 SAMPLE
53674 BIN$aSuwOl8AQFCCphpKWoCRkg==$0
53675 BIN$Acsdzvs0RMewwv6ntnvFxQ==$0
54148 TEST

15 rows selected.

Querying V$DATABASE

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> select platform_id, platform_name from v$database;

PLATFORM_ID
-----------
PLATFORM_NAME
---------------------------------------------------------------------
7
Microsoft Windows IA (32-bit)


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
901163

Flushing out Buffer Cache and Shared Pool

Buffer cache which is part of System Global Area (SGA) holds the copy of data blocks in order to minimize the physical I/O.It caches the data that has been most recently accessed by the database users. Once you issue the SQL query, it fetches the data blocks from the disk and places it in the buffer. If you issue the same query again, it will retrieve blocks from the buffer cache instead of reading from disk.

With Oracle 10g. you can flush the buffer cache using the following statement:
alter system flush buffer_cache;

Shared Pool which is a required component in SGA caches most recently used SQL statements. All recently used SQL statements are removed from memory by flushing the shraed pool. Issue the following statement to flush the Shared Pool.
alter system flush shared_pool;

Query Tablespaces

SQL> select * from dba_tablespaces;

Using above you can retrieve the following information

• Tablespace block size
• Tablespace status : online, offline, or read-only
• Contents of the tablespace: undo, temporary, or permanent
• Whether it uses dictionary or locally managed extents
• Whether the segment space management is automatic or manual
• Whether it is a bigfile or smallfile tablespace

SQL> select * from dba_tablespace_usage_metrics;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX 37888 4194302 .903320743
SYSTEM 61560 4194302 1.46770547
TEMP 0 4194302 0
UNDOTBS1 288 4194302 .006866458
USERS 112 4194302 .002670289

SQL> select tablespace_name,status,contents,extent_management extents,segment_space_management free_
space from dba_tablespaces;

TABLESPACE_NAME STATUS CONTENTS EXTENTS FREE_S
------------------------------ --------- --------- ---------- ------
SYSTEM ONLINE PERMANENT LOCAL MANUAL
UNDOTBS1 ONLINE UNDO LOCAL MANUAL
SYSAUX ONLINE PERMANENT LOCAL AUTO
TEMP ONLINE TEMPORARY LOCAL MANUAL
USERS READ ONLY PERMANENT LOCAL AUTO

SQL> select tablespace_name, ((bytes/1024)/1024) MB from dba_data_files;

TABLESPACE_NAME MB
------------------------------ ----------
USERS 5
SYSAUX 300
UNDOTBS1 55
SYSTEM 490

To get the default tablespace for a user
SQL> select username, default_tablespace from dba_users;

SQL> SELECT TABLESPACE_NAME "TABLESPACE",
INITIAL_EXTENT "INITIAL_EXT",
NEXT_EXTENT "NEXT_EXT",
MIN_EXTENTS "MIN_EXT",
MAX_EXTENTS "MAX_EXT",
PCT_INCREASE
FROM DBA_TABLESPACES;

Table Statistics

How to check for tables which have stale statistics:
select owner,table_name,last_analyzed,sample_size from dba_tables;

Ex: SQL> select owner,table_name,last_analyzed,sample_size from
dba_tables where table_name in ('EMP','DEPT');

How to gather statistics for a schema:
Ex: exec dbms_stats.GATHER_TABLE_STATS(ownname=>'SCOTT',
estimate_percent=>20, cascade=>TRUE);

How to gather statistics for a particular table:
Ex: exec dbms_stats.GATHER_TABLE_STATS(ownname=>'SCOTT',
tabname=>'EMP', estimate_percent=>20, cascade=>TRUE);
( or )
Ex: exec dbms_stats.gather_table_stats(ownname=>'SCOTT',
tabname=>'EMP', estimate_percent=>NULL,
method_opt=>'FOR ALL INDEXED COLUMNS', degree=>5,
GRANULARITY => 'ALL', CASCADE=>TRUE);

Note: The above gather statistics command gives a more detailed
statistics

To check when a particular table was last analyzed:
Ex: SQL> select table_name, column_name, num_distinct nd,
num_nulls nn, density, last_analyzed from dba_tab_columns
where table_name in ( 'EMP' ) order by table_name, column_name;

GATHER_TABLE_STATS Procedure Parameters

ownname Schema of table to analyze

tabname Name of table

partname Name of partition

estimate_percent Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

block_sample Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

method_opt Accepts either of the following options, or both in combination:

* FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
* FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

column is defined as column := column_name | (extension)

- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms.
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.
- column_name : name of a column
- extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression

The default is FOR ALL COLUMNS SIZE AUTO. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

degree Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.

granularity Granularity of statistics to collect (only pertinent if the table is partitioned).

'ALL' - gathers all (subpartition, partition, and global) statistics

'APPROX_GLOBAL AND PARTITION' - similar to 'GLOBAL AND PARTITION' but in this case the global statistics are aggregated from partition level statistics. This option will aggregate all statistics except the number of distinct values for columns and number of distinct keys of indexes. The existing histograms of the columns at the table level are also aggregated.Global statistics are gathered if partname is NULL or if the aggregation cannot be performed (for example, if statistics for one of the partitions is missing).

'AUTO'- determines the granularity based on the partitioning type. This is the default value.

'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - gathers global statistics

'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION '- gathers partition-level statistics

'SUBPARTITION' - gathers subpartition-level statistics.

cascade Gathers statistics on the indexes for this table. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics are to be collected or not. This is the default. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

stattab User statistics table identifier describing where to save the current statistics

statid Identifier (optional) to associate with these statistics within stattab

statown Schema containing stattab (if different than ownname)

no_invalidate Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

force Gather statistics of table even if it is locked

Ex:
exec dbms_stats.gather_table_stats ( -
ownname => OWNER', -
tabname => 'TABLE_NAME, -
estimate_percent => NULL, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
degree => NULL, -
granularity => 'DEFAULT', -
cascade => TRUE, -
no_invalidate => TRUE);

Create User Statistics Table:
exec DBMS_STATS.CREATE_STAT_TABLE( -
ownname => 'SCOTT', -
stattab => 'STATS_EMP', -
tblspace => 'STATTASPC')

desc STATS_EMP

Check datafiles and tempfiles in database

SQL> select file_name, tablespace_name from dba_data_files;

SQL> select tablespace_name,file_name from dba_temp_files;

To get the datafile name and max size of datafile in the database:
SQL> select file_name, bytes from dba_data_files where bytes=(select max(bytes) from dba_data_files);

V$DATABASE Queries

To get database identifier and database name:
SQL> select DBID, NAME from v$database;

DBID NAME
---------- ---------
3235629315 WORK

To retrieve current sequence change number:
SQL> select current_scn from v$database;

To check whether database is in archive log mode or not:
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

To verify that forced logging (instructing Oracle Database to force all logging of changes to the redo, even if nologging or unrecoverable data loads are performed) has been enabled on your primary database:
SQL> select force_logging from v$database;

To get Oracle Version and Release from SQL

SQL> show release
release 1002000300

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 – Production

Finding Linux and Windows bit version from sql.

finding the linux bit version, i mean 32-bit or 64-bit ?

SQL> select metadata from sys.kopm$ ;
METADATA
------------------------------

--------------------------------------------------
0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A05050505050405
0607080823472323081123081141B023008303670367130000000000000000000000000000000000
0000000000000000000000000000000000000000

B023----------32BIT
BO47----------64BIT

Which session generating so many archives

This is the query for which session is generating so many arch files

select c.username,c.sid,c.serial#,c.

process,to_char( b.value/1024,
'999,999,999' ) || ' kbytes' kbytes from v$statname a, v$sesstat b,
v$session c where a.statistic# = b.statistic# and b.sid = c.sid and
a.name = 'redo size'
and c.username is not null
and b.value > 0
order by 5