HOW we Check quota unlimited on particular user?

If the bytes=-1 in DBA_TS_QUOTAS view it means tablespace quota is unlimited on that user.

SQL> desc dba_Ts_quotas;
Name Null?
Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAr2(30)
USERNAME NOT NULL VARCHAR2(30)
BYTES NUMBER
MAX_BYTES NUMBER
BLOCKS NOT NULL NUMBER
MAX_BLOCKS NUMBER
SQL> select username,max_bytes from dba_Ts_quotas;
USERNAME MAX_BYTES
------------------------ ------------------------
TEMP -1

Tablespace Information

Tablespace Information
Here are some scripts related to Tablespace Information .
Information
TABLESPACE INFORMATION NOTES:
Tablespace Name - Name of the tablespace
Initial Extent - Default initial extent size
Next Extent - Default incremental extent size
Min Extents - Default minimum number of extents
Max Extents - Default maximum number of extents
PCT Increase - Default percent increase for extent size
Status - Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)
Contents - Type of tablespace. This column will have 'TEMPORARY' (v7.3+) for dedicated temporary tablespaces, and 'PERMANENT' for tablespaces that can store both temporary sort segments and permanent objects.
SQL>select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT,MIN_EXTENTS,
MAX_EXTENTS, PCT_INCREASE, STATUS, CONTENTS from dba_tablespaces
order by TABLESPACE_NAME
Coalesced Exts

WAIT STATISTIC NOTES:
Tablespace Name - Name of tablespace
Total Extents - Total number of free extents in tablespace
Extents Coalesced - Total number of coalesced free extents in tablespace
% Extents Coalesced - Percentage of coalesced free extents in tablespace
Total Bytes - Total number of free bytes in tablespace
Bytes Coalesced - Total number of coalesced free bytes in tablespace
Total Blocks - Total number of free oracle blocks in tablespace
Blocks Coalesced - Total number of coalesced free Oracle blocks in tablespace
% Blocks Coalesced - Percentage of coalesced free Oracle blocks in tablespace
SQL>select TABLESPACE_NAME, TOTAL_EXTENTS, EXTENTS_COALESCED, PERCENT_EXTENTS_COALESCED, TOTAL_BYTES, BYTES_COALESCED, TOTAL_BLOCKS, BLOCKS_COALESCED, PERCENT_BLOCKS_COALESCED
from dba_free_space_coalesced
order by TABLESPACE_NAME
Usage
TABLESPACE USAGE NOTES:
Tablespace Name - Name of the tablespace
Bytes Used - Size of the file in bytes
Bytes Free - Size of free space in bytes
Largest - Largest free space in bytes
Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%
SQL>select a.TABLESPACE_NAME, a.BYTES bytes_used, b.BYTES bytes_free, b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from ( select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files
group by TABLESPACE_NAME) a, ( select TABLESPACE_NAME, sum(BYTES) BYTES , max(BYTES) largest from dba_free_space group by TABLESPACE_NAME ) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc
Users Default (SYSTEM)
SYSTEM TABLESPACE USAGE NOTES:
Username - Name of the user
Created - User creation date
Profile - Name of resource profile assigned to the user
Default Tablespace - Default tablespace for data objects
Temporary Tablespace - Default tablespace for temporary objects
Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.
select USERNAME,
CREATED,
PROFILE,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE
from dba_users
order by USERNAME
Objects in SYSTEM TS
OBJECTS IN SYSTEM TABLESPACE NOTES:
Owner - Owner of the object
Object Name - Name of object
Object Type - Type of object
Tablespace - Tablespace name
Size - Size (bytes) of object
Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace
SQL>select OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES
from dba_segments where TABLESPACE_NAME = 'SYSTEM' and OWNER not in ('SYS','SYSTEM') order by OWNER, SEGMENT_NAME
Freespace/Largest Ext FREE, LARGEST, & INITIAL NOTES:
Tablespace - Name of the tablespace
Total Free Space - Total amount (bytes) of freespace in the tablespace
Largest Free Extent - Largest free extent (bytes) in the tablespace
select TABLESPACE_NAME,
sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent
from dba_free_space
group by TABLESPACE_NAME

Outer join query in Oracle using the (+) sign

This post will show you how to create outer join query in Oracle using the (+) sign rather than LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN clause, since Oracle 8 didn't know them.
It is simple to do this work.Assume we have two tables (T1 and T2) with the structure is shown below:
Table T1Column Name Data Type----------- ----------A intR int
SQL> create table T1 ( A int, R int );
SQL> insert into T1 (A, R) values (1, 1);
SQL> insert into T1 (A, R) values (3, 3);
SQL> select * from T1;
A R
----------
1 1
3 3
Table T2Column Name Data Type----------- ----------B intR int
SQL> create table T2 ( R int, B int );
SQL> insert into T2 (R, B) values (2, 2);
SQL> insert into T2 (R, B) values (3, 4);
SQL> insert into T2 (R, B) values (4, 4);
SQL> select * from T2;
R B---- ----
2 2
3 4
4 4
I. LEFT OUTER JOIN
Here is the script to create left outer join query.
select * from T1, T2whereT1.R = T2.R (+);
That script will produce the following output:
A R R B
---- ---- ---- ----
1 1
3 3 3 4
The same result will be produced when we use the following script (in Oracle 9i)
select * from T1left outer join T2on T1.R = T2.R;

II. RIGHT OUTER JOIN
Here is the script to create right outer join query.
select * from T1, T2whereT1.R (+) = T2.R;
That script will produce the following output:
A R R B
---- ---- ---- ----
2 2
3 3 3 4
4 4
The same result will be produced when we use the following script (in Oracle 9i)
select * from T1right outer join T2on T1.R = T2.R;
III. FULL OUTER JOIN
To create full outer join using (+) sign, we need combine two queries above using UNION, like this:
select * from T1, T2whereT1.R = T2.R (+);
UNION
select * from T1, T2whereT1.R (+) = T2.R;
The output is below:
A R R B
---- ---- ---- ----
1 1
3 3 3 4
2 2
4 4
In Oracle 9i, we can do it with the following script.
select * from T1full outer join T2on T1.R = T2.R;

without Control files and redo log files Recover database

If your database control file and redo log file files is lost,to recover the database without these:--

Startup the database with the initialization file. As we do not have the control files, start the database in no mount state.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1248140 bytes
Variable Size 75498612 bytes
Database Buffers 130023424 bytes
Redo Buffers 2945024 bytes
SQL>Check the path of control files.SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string K:\ORCL10G\CONTROL\CONTROL01.C
TL, K:\ORCL10G\CONTROL\CONTROL
02.CTL, K:\ORCL10G\CONTROL\CON
TROL03.CTL
Having the details of all the data files at hand recreate the control files.SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL10G" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'K:\ORCL10G\LOG\REDO01.LOG' SIZE 50M,
GROUP 2 'K:\ORCL10G\LOG\REDO02.LOG' SIZE 50M,
GROUP 3 'K:\ORCL10G\LOG\REDO03.LOG' SIZE 50M
DATAFILE
'K:\ORCL10G\DATA\SYSTEM01.DBF',
'K:\ORCL10G\DATA\UNDOTBS01.DBF',
'K:\ORCL10G\DATA\SYSAUX01.DBF',
'K:\ORCL10G\DATA\USERS01.DBF',
'K:\ORCL10G\DATA\EXAMPLE01.DBF',
'K:\ORCL10G\DATA\UNDOTBS02.DBF'
CHARACTER SET WE8MSWIN1252;
Control file created.
Note the RESETLOGS option in the create control file script. This will reset the logs and synchronizes the SCN between the database files, control files and redo log file. Oracle will re-create the redo log files when the database is opened with resetlogs options.
Once the control file is created, try mounting the database. If the database mounts well then everything seems to be fine.
SQL> alter database mount;
Database altered.
The database mounted successfully. Open the database with resetlogs option.SQL> alter database open resetlogs;
Database altered.
Here we go. The database opened successfully. The only point to consider is that of the data loss. The data in the redo log files, as it existed before the loss, will be lost. Hence the data loss here could be minimum.
Conclusion :
1. Always multiplex the Control files and Redo log files.
2. Have a consistent backup of the database.