Nashim>--to check no of tablespace in database
Nashim>
Nashim>select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
6 rows selected.
Nashim>set lines 100
Nashim>select * from v$dbfile;
FILE# NAME
--------------------------------------------
4 D:\APP\NASHIM\ORADATA\ORCL\USERS01.DBF
3 D:\APP\NASHIM\ORADATA\ORCL\UNDOTBS01.DBF
2 D:\APP\NASHIM\ORADATA\ORCL\SYSAUX01.DBF
1 D:\APP\NASHIM\ORADATA\ORCL\SYSTEM01.DBF
5 D:\APP\NASHIM\ORADATA\ORCL\EXAMPLE01.DBF
Nashim>select ts#,name from v$datafiles;
select ts#,name from v$datafiles
*
ERROR at line 1:
ORA-00942: table or view does not exist
Nashim>select ts#,name from v$datafile;
TS# NAME
--------------------------------------------
0 D:\APP\NASHIM\ORADATA\ORCL\SYSTEM01.DBF
1 D:\APP\NASHIM\ORADATA\ORCL\SYSAUX01.DBF
2 D:\APP\NASHIM\ORADATA\ORCL\UNDOTBS01.DBF
4 D:\APP\NASHIM\ORADATA\ORCL\USERS01.DBF
6 D:\APP\NASHIM\ORADATA\ORCL\EXAMPLE01.DBF
Nashim>select t.name, d.name from v$tablespace t, v$datafile d where t.ts#=d.ts#
;
NAME NAME
----------------------------------------------------------------
SYSTEM D:\APP\NASHIM\ORADATA\ORCL\SYSTEM01.DBF
SYSAUX D:\APP\NASHIM\ORADATA\ORCL\SYSAUX01.DBF
UNDOTBS1 D:\APP\NASHIM\ORADATA\ORCL\UNDOTBS01.DBF
USERS D:\APP\NASHIM\ORADATA\ORCL\USERS01.DBF
EXAMPLE D:\APP\NASHIM\ORADATA\ORCL\EXAMPLE01.DBF
Nashim>show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
Nashim>create tablespace nashim;
create tablespace nashim
*
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause
Nashim>create tablespace nashim datafile 'D:\APP\NASHIM\ORADATA\ORCL\nashim01.dbf' size 20m;
Tablespace created.
Nashim>select * from v$dbfile;
FILE# NAME
--------------------------------------------
4 D:\APP\NASHIM\ORADATA\ORCL\USERS01.DBF
3 D:\APP\NASHIM\ORADATA\ORCL\UNDOTBS01.DBF
2 D:\APP\NASHIM\ORADATA\ORCL\SYSAUX01.DBF
1 D:\APP\NASHIM\ORADATA\ORCL\SYSTEM01.DBF
5 D:\APP\NASHIM\ORADATA\ORCL\EXAMPLE01.DBF
6 D:\APP\NASHIM\ORADATA\ORCL\NASHIM01.DBF
6 rows selected.
Nashim>alter system set db_create_file_dest='d:\app';
System altered.
Nashim>show parameter db_create_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string d:\app
Nashim>create tablespace test_tbs;
Tablespace created.
Nashim>select bytes/1024/1024 , name from v$datafiles;
select bytes/1024/1024 , name from v$datafiles
*
ERROR at line 1:
ORA-00942: table or view does not exist
NOTE:- TO CHECK ALL DATAFILE SIZE.....
Nashim>select bytes/1024/1024 , name from v$datafile;
BYTES/1024/1024 NAME
--------------------------------------------------------------------------------
-------------------------------------------------
680 D:\APP\NASHIM\ORADATA\ORCL\SYSTEM01.DBF
550 D:\APP\NASHIM\ORADATA\ORCL\SYSAUX01.DBF
40 D:\APP\NASHIM\ORADATA\ORCL\UNDOTBS01.DBF
5 D:\APP\NASHIM\ORADATA\ORCL\USERS01.DBF
100 D:\APP\NASHIM\ORADATA\ORCL\EXAMPLE01.DBF
20 D:\APP\NASHIM\ORADATA\ORCL\NASHIM01.DBF
100 D:\APP\ORCL\DATAFILE\O1_MF_TEST_TBS_89J2NLCC_.DBF
7 rows selected.
NOTE:- TO CREATE TABLE IN PARTICULAR TABLESPACE BASED ON ANOTHER TABLE...
Nashim>create table nashim tablespace nashim as select * from hr.employees;
Table created.
Nashim>select count(*) from nashim;
COUNT(*)
----------
107
Nashim>create table abc(id number) tablespace nashim;
Table created.
Nashim>show parameter db_create_online_log_dest_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
Nashim>select * from v$controlfile;
STATUS NAME
IS_ BLOCK_SIZE FILE_SIZE_BLKS
D:\APP\NASHIM\ORADATA\ORCL\CONTROL01.CTL
NO 16384 594
D:\APP\NASHIM\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL
NO 16384 594
Nashim>select * from v$logfile;
GROUP# STATUS TYPE MEMBER
IS_
3 ONLINE D:\APP\NASHIM\ORADATA\ORCL\REDO03.LOG
NO
2 ONLINE D:\APP\NASHIM\ORADATA\ORCL\REDO02.LOG
NO
1 ONLINE D:\APP\NASHIM\ORADATA\ORCL\REDO01.LOG
NO
Nashim>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ----------
------ ------------- --------- ------------ ---------
1 1 193 52428800 512 1 NO CURRENT
5561541 06-NOV-12 2.8147E+14
2 1 191 52428800 512 1 NO INACTIVE
5507930 04-NOV-12 5547374 05-NOV-12
3 1 192 52428800 512 1 NO INACTIVE
5547374 05-NOV-12 5561541 06-NOV-12
Nashim>show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\app\Nashim\flash_recovery_a
rea
db_recovery_file_dest_size big integer 3852M
Nashim>select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 NASHIM YES NO YES
8 TEST_TBS YES NO YES
8 rows selected.
Nashim>select count(*) from nashim;
COUNT(*)
----------
107
Nashim>select tablespace_name , status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
NASHIM ONLINE
TEST_TBS ONLINE
8 rows selected.
Nashim>alter tablespace nashim offline;
Tablespace altered.
Nashim>select count(*) from nashim;
select count(*) from nashim
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: 'D:\APP\NASHIM\ORADATA\ORCL\NASHIM01.DBF'
Nashim>select tablespace_name , status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
NASHIM OFFLINE
TEST_TBS ONLINE
8 rows selected.
Nashim>alter tablespace nashim online;
Tablespace altered.
Nashim>select count(*) from nashim;
COUNT(*)
----------
107
Nashim>insert into nashim select * from nashim;
107 rows created.
Nashim>alter tablespace nashim read only;
Tablespace altered.
Nashim>insert into nashim select * from nashim;
insert into nashim select * from nashim
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: 'D:\APP\NASHIM\ORADATA\ORCL\NASHIM01.DBF'
Nashim>select count(*) from nashim;
COUNT(*)
----------
214
Nashim>alter tablespace nashim read write;
Tablespace altered.
Nashim>insert into nashim select * from nashim;
214 rows created.
Nashim>commit;
Commit complete.
Nashim>--adding datafile in tablespace;
Nashim>alter tablespace nashim add datafile 'd:\app\nashim\oradata\orcl\nashim00
1.dbf' size 10m;
Tablespace altered.
Nashim>--resize datafile
Nashim>
Nashim>alter database datafile 'd:\app\nashim\oradata\orcl\nashim001.dbf'
2 resize 15m;
Database altered.
but if we have more than 1 datafile in a tablespace now i want to store data in particular datafile then what would be the command.
ReplyDelete