how to create tablespace:
===========================
>CREATE SMALLFILE TABLESPACE "KUMAR" DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\kumar01.dbf' SIZE 2m AUTOEXTEND ON NEXT
2M MAXSIZE 20M LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE
MANAGEMENT AUTO;
now tablepace created:
but when we create one object corresponding tablespace then only segment will created;
>select * from dba_segments where
tablespace_name='KUMAR';
no row select
SQL> create table test as select * from scott.emp;
create table test as select * from scott.emp
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table test1 as select * from scott.emp;
Table created.
SQL> alter table test1 move tablespace kumar;
Table altered.
>select * from dba_segments where tablespace_name='KUMAR';
default tablespace:
===================
SQL> select default_tablesapce from dba_tablespaces;
select default_tablesapce from dba_tablespaces
*
ERROR at line 1:
ORA-00904: "DEFAULT_TABLESAPCE": invalid
identifier
SQL> alter database default tablespace kumar;
Database altered.
SQL> alter database default tablespace users;
Database altered.
SQL> select default_tablespace from dba_users;
DEFAULT_TABLESPACE
------------------------------
SYSTEM
SYSTEM
SYSTEM
SYSAUX
SYSAUX
USERS
USERS
USERS
USERS
SYSTEM
SYSAUX
DEFAULT_TABLESPACE
------------------------------
SYSAUX
SYSAUX
SYSAUX
SYSAUX
SYSAUX
SYSAUX
SYSAUX
SYSAUX
SYSAUX
SYSAUX
USERS
DEFAULT_TABLESPACE
------------------------------
USERS
USERS
USERS
USERS
USERS
USERS
USERS
29 rows selected.
Alter tablespace offline and read only comment:
>
SQL> select status from dba_tablespaces;
STATUS
---------
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
7 rows selected.
SQL> alter tablespace kumar offline;
Tablespace altered.
SQL> select status from dba_tablespaces;
STATUS
---------
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
OFFLINE
7 rows selected.
SQL> select * from test1;
select * from test1
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\KUMAR01'
SQL> alter tablespace kumar read;
alter tablespace kumar read
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option
SQL> alter tablespace kumar read only;
alter tablespace kumar read only
*
ERROR at line 1:
ORA-01539: tablespace 'KUMAR' is not online
SQL> alter tablespace kumar readonly;
alter tablespace kumar readonly
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option
SQL> alter tablespace kumar read,write;
alter tablespace kumar read,write
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option
SQL> alter tablespace kumar read write;
alter tablespace kumar read write
*
ERROR at line 1:
ORA-01646: tablespace 'KUMAR' is not read only - cannot make
read write
SQL> alter tablespace online;
alter tablespace online
*
ERROR at line 1:
ORA-02140: invalid tablespace name
SQL> alter tablespace kumar online;
Tablespace altered.
SQL> select * From test1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------
---------- ----------
DEPTNO
----------
7369 SMITH CLERK
7902 17-DEC-80
800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------
---------- ----------
DEPTNO
----------
7566 JONES MANAGER
7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------
---------- ----------
DEPTNO
----------
7782 CLARK MANAGER
7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------
---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN
7698 08-SEP-81 1500
0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------
---------- ----------
DEPTNO
----------
7902 FORD ANALYST
7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
SQL> alter tablespace kumar read only;
Tablespace altered.
SQL> select status from dba_tablespaces;
STATUS
---------
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
READ ONLY
7 rows selected.
SQL> insert into test1 as select * From scott.emp;
insert into test1 as select * From scott.emp
*
ERROR at line 1:
ORA-00926: missing VALUES keyword
SQL> insert into test1 select * From scott.emp;
insert into test1 select * From scott.emp
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\KUMAR01'
SQL> alter tablespace kumar read write;
Tablespace altered.
SQL> alter tablespace kumar write only;
alter tablespace kumar write only
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option
SQL> select status from dba_tablespaces;
STATUS
---------
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
7 rows selected.
Enlarging tablespace: how to add dafile in tablespace:
======================================================
SQL> create tablespace k
2 datafile
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\k01.dbf' size 1k;
create tablespace k
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum
required
SQL> ed
Wrote file afiedt.buf
1 create tablespace k
2* datafile
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\k01.dbf' size 512k
SQL> /
Tablespace created.
SQL> alter tablespace k
2 add datafile
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\K01.dbf' size 512k;
alter tablespace k
*
ERROR at line 1:
ORA-01537: cannot add file
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\K01.dbf' -
file already part of database
SQL> ed
Wrote file afiedt.buf
1 alter tablespace k
2* add datafile
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\K02.dbf' size 512k
SQL> /
Tablespace altered.
&resize datafile:
=================
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\K01.DBF' res
ize 2m;
Database altered.
SQL> select tablespace_name,sum(bytes)/1024 from dba_data_files group by tablesp
ace_name;
TABLESPACE_NAME SUM(BYTES)/1024
------------------------------ ---------------
SYSAUX
286720
UNDOTBS1
66560
KUMAR
10240
USERS
5120
K
2560
SYSTEM
501760
EXAMPLE
102400
7 rows selected.
3)autoextend:
============
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\K01.DBF'
2 autoextend on next 512k maxsize 5m;
Database altered.
SQL> select tablespace_name,sum(bytes)/1024 from dba_data_files group by tablesp
ace_name;
TABLESPACE_NAME SUM(BYTES)/1024
------------------------------ ---------------
SYSAUX
286720
UNDOTBS1
66560
KUMAR
10240
USERS
5120
K
2560
SYSTEM
501760
EXAMPLE
102400
7 rows selected.
4) drop tablespace including datafiles;
========================================
>alter tablespace <t_name> offlie;
>drop tablespace <t_name> including contents and datafiles;
====================================================================
No comments:
Post a Comment