admin 수업 마지막
Flashback 실습
[oracle@localhost ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-JUL-2018 09:14:31
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 03-JUL-2018 09:14:32
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@localhost ~]$ startup
bash: startup: command not found
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 3 09:14:47 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SYS> startup
ORACLE instance started.
Total System Global Area 623546368 bytes
Fixed Size 1338308 bytes
Variable Size 478151740 bytes
Database Buffers 138412032 bytes
Redo Buffers 5644288 bytes
Database mounted.
Database opened.
SYS> conn hr/hr
Connected.
HR> select avg(salary) from bigemp;
AVG(SALARY)
-----------
10641.8825
HR> update bigemp
2 set salary=salary*1.1;
655360 rows updated.
HR> !date
Tue Jul 3 09:32:12 KST 2018
HR> commit
2 ;
Commit complete.
HR> update bigemp
2 set salary = salary * 1.1;
655360 rows updated.
HR> commit;
Commit complete.
HR> !date
Tue Jul 3 09:35:07 KST 2018
HR> alter table bigemp enable row movement;
Table altered.
HR> flashback table bigemp to timestamp
2 to timestamp('2018/07/03 09:32:30','yyyy/mm/dd hh24:mi:ss');
to timestamp('2018/07/03 09:32:30','yyyy/mm/dd hh24:mi:ss')
*
ERROR at line 2:
ORA-00936: missing expression
HR> ed
Wrote file afiedt.buf
1 flashback table bigemp to timestamp
2* to_timestamp('2018/07/03 09:32:30','yyyy/mm/dd hh24:mi:ss')
HR> /
Flashback complete.
HR> alter table bigemp disable row movement;
Table altered.
HR> select avg(salary) from bigemp;
AVG(SALARY)
-----------
10641.8825
HR> select * from v$option;
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Partitioning
TRUE
Objects
TRUE
Real Application Clusters
FALSE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Advanced replication
TRUE
Bit-mapped indexes
TRUE
Connection multiplexing
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Connection pooling
TRUE
Database queuing
TRUE
Incremental backup and recovery
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Instead-of triggers
TRUE
Parallel backup and recovery
TRUE
Parallel execution
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Parallel load
TRUE
Point-in-time tablespace recovery
TRUE
Fine-grained access control
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Proxy authentication/authorization
TRUE
Change Data Capture
TRUE
Plan Stability
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Online Index Build
TRUE
Coalesce Index
TRUE
Managed Standby
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Materialized view rewrite
TRUE
Materialized view warehouse refresh
TRUE
Database resource manager
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Spatial
TRUE
Automatic Storage Management
FALSE
Export transportable tablespaces
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Transparent Application Failover
TRUE
Fast-Start Fault Recovery
TRUE
Sample Scan
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Duplexed backups
TRUE
Java
TRUE
OLAP Window Functions
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Block Media Recovery
TRUE
Fine-grained Auditing
TRUE
Application Role
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Enterprise User Security
TRUE
Oracle Data Guard
TRUE
Oracle Label Security
FALSE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
OLAP
TRUE
Basic Compression
TRUE
Join index
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Trial Recovery
TRUE
Data Mining
TRUE
Online Redefinition
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Streams Capture
TRUE
File Mapping
TRUE
Block Change Tracking
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Flashback Table
TRUE
Flashback Database
TRUE
Transparent Data Encryption
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Backup Encryption
TRUE
Unused Block Compression
TRUE
Oracle Database Vault
FALSE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Result Cache
TRUE
SQL Plan Management
TRUE
SecureFiles Encryption
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Real Application Testing
TRUE
Flashback Data Archive
TRUE
DICOM
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Active Data Guard
TRUE
Server Flash Cache
TRUE
Advanced Compression
TRUE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
XStream
TRUE
Deferred Segment Creation
TRUE
65 rows selected.
HR> conn / as sysdba
Connected.
SYS> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS> create tablespace f_tbs
2 datafile '/u01/app/oracle/oradata/orcl/f_tbs01.dbf' size 20m
3 autoextend on;
Tablespace created.
SYS> create user fa identified by fa
2 default tablespace f_tbs
3 temporary tablespace temp;
User created.
SYS> grant connect, resource to fa;
Grant succeeded.
SYS> grant flashback archive administer to fa;
Grant succeeded.
SYS> conn fa/fa
Connected.
FA> create flashback archive fla1
2 tablespace f_tbs
3 retention 1 year;
Flashback archive created.
FA> ed
Wrote file afiedt.buf
1 create flashback archive fla3
2 tablespace f_tbs
3* retention 3 year
FA> grant flashback archive on fla1 to hr;
Grant succeeded.
FA> conn hr/hr
Connected.
HR> create table f_emp
2 as
3 select employee_id, last_name, salary
4 from employees
5 ;
Table created.
HR> alter table f_emp flashback archive fla1;
Table altered.
HR> create table nof_emp
2 as
3 select employee_id, last_name, salary
4 from employees;
Table created.
HR> select * from user_flashback_archive_tables;
TABLE_NAME OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- --------
F_EMP HR
FLA1
SYS_FBA_HIST_17855 ENABLED
HR> spool /home/oracle/test_fa
HR> select * from f_emp
2 where employee_id = 101;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
101 Kochhar 17000
HR> !date
Tue Jul 3 10:31:14 KST 2018
HR> update f_emp
2 set salary = salary*1.1
3 where employee_id = 101;
1 row updated.
HR> commit;
Commit complete.
HR> update nof_emp
2 set salary = salary*1.1
3 where employee_id = 101;
1 row updated.
HR> commit;
Commit complete.
HR> !date
Tue Jul 3 10:32:42 KST 2018
HR> spool off;
HR> !ls /home/oracle
afiedt.buf expdat.dmp q_bc.sql q_tbs.sql ws2_labs
backup labs q_dbf.sql test_fa.lst
Desktop oradiag_oracle q_log.sql users01.dbf
HR> col versions_starttime for a32
HR> col versions_endtime for a32
HR> select versions_starttime, versions_endtime, salary
2 from f_emp
3 versions between timestamp minvalue and maxvalue
4 where employee_id = 101;
VERSIONS_STARTTIME VERSIONS_ENDTIME SALARY
-------------------------------- -------------------------------- ----------
03-JUL-18 10.31.46.000000000 AM 17000
03-JUL-18 10.31.46.000000000 AM 18700
HR> select versions_starttime, versions_endtime, salary
2 from nof_emp
3 versions between timestamp minvalue and maxvalue
4 where employee_id = 101;
VERSIONS_STARTTIME VERSIONS_ENDTIME SALARY
-------------------------------- -------------------------------- ----------
18700
HR> drop table nof_emp purge;
Table dropped.
HR> conn / as sysdba
Connected.
SYS> drop table f_emp purge;
drop table f_emp purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS> alter table hr.f_emp no flashback archive;
Table altered.
SYS> conn hr/hr
Connected.
HR> drop table f_emp purge;
Table dropped.
HR> conn / as sysdba
Connected.
SYS> drop user fa cascade;
User dropped.
SYS> drop tablespace f_tbs including contents and datafiles;
Tablespace dropped.
---------------------------------------------------------------
Flashback Database 실습
[oracle@localhost ~]$ cd /u01/app/oracle/flash_recovery_area/ORCL/
[oracle@localhost ORCL]$ ls
archivelog autobackup backupset
(flash_recovery_area/ORCL 디렉토리의 내용 확인)
SYS> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 190
Next log sequence to archive 192
Current log sequence 192
SYS> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SYS> alter database flashback on;
Database altered.
SYS> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
(다시 폴더 확인)
[oracle@localhost ORCL]$ ls
archivelog autobackup backupset flashback
(flashback이라는 디렉토리가 만들어졌다.)
[oracle@localhost ORCL]$ cd flashback/
[oracle@localhost flashback]$ ls
o1_mf_fmor2181_.flb
(백그라운드 프로세스 확인)
[oracle@localhost flashback]$ ps -ef | grep orcl
oracle 5292 1 0 09:14 ? 00:00:00 ora_pmon_orcl
oracle 5294 1 0 09:14 ? 00:00:00 ora_vktm_orcl
oracle 5298 1 0 09:14 ? 00:00:00 ora_gen0_orcl
oracle 5300 1 0 09:14 ? 00:00:00 ora_diag_orcl
oracle 5302 1 0 09:14 ? 00:00:00 ora_dbrm_orcl
oracle 5304 1 0 09:14 ? 00:00:00 ora_psp0_orcl
oracle 5306 1 0 09:14 ? 00:00:00 ora_dia0_orcl
oracle 5308 1 0 09:14 ? 00:00:00 ora_mman_orcl
oracle 5310 1 0 09:14 ? 00:00:01 ora_dbw0_orcl
oracle 5312 1 0 09:14 ? 00:00:03 ora_lgwr_orcl
oracle 5314 1 0 09:14 ? 00:00:00 ora_ckpt_orcl
oracle 5316 1 0 09:14 ? 00:00:00 ora_smon_orcl
oracle 5318 1 0 09:14 ? 00:00:00 ora_reco_orcl
oracle 5320 1 0 09:14 ? 00:00:00 ora_mmon_orcl
oracle 5322 1 0 09:15 ? 00:00:00 ora_mmnl_orcl
oracle 5363 1 0 09:15 ? 00:00:00 ora_arc0_orcl
oracle 5365 1 0 09:15 ? 00:00:00 ora_arc1_orcl
oracle 5367 1 0 09:15 ? 00:00:00 ora_arc2_orcl
oracle 5369 1 0 09:15 ? 00:00:00 ora_arc3_orcl
oracle 5371 1 0 09:15 ? 00:00:00 ora_ctwr_orcl
oracle 5374 1 0 09:15 ? 00:00:00 ora_qmnc_orcl
oracle 5389 1 0 09:15 ? 00:00:00 ora_cjq0_orcl
oracle 5404 1 0 09:15 ? 00:00:00 ora_q000_orcl
oracle 5406 1 0 09:15 ? 00:00:00 ora_q001_orcl
oracle 5490 1 0 09:20 ? 00:00:00 ora_smco_orcl
oracle 6215 1 0 09:32 pts/2 00:00:00 /u01/app/oracle/product/11.2.0/db_1/perl/bin/perl /u01/app/oracle/product/11.2.0/db_1/bin/emwd.pl dbconsole /u01/app/oracle/product/11.2.0/db_1/localhost.localdomain_orcl/sysman/log/emdb.nohup
oracle 6229 6215 0 09:32 pts/2 00:00:31 /u01/app/oracle/product/11.2.0/db_1/jdk/bin/java -server -Xmx192M -XX:MaxPermSize=200M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 -Doracle.home=/u01/app/oracle/product/11.2.0/db_1/oc4j -Doracle.oc4j.localhome=/u01/app/oracle/product/11.2.0/db_1/localhost.localdomain_orcl/sysman -DEMSTATE=/u01/app/oracle/product/11.2.0/db_1/localhost.localdomain_orcl -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_localhost.localdomain_orcl/config/jazn.xml -Djava.security.policy=/u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_localhost.localdomain_orcl/config/java2.policy -Djavax.net.ssl.KeyStore=/u01/app/oracle/product/11.2.0/db_1/sysman/config/OCMTrustedCerts.txt-Djava.security.properties=/u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/u01/app/oracle/product/11.2.0/db_1/localhost.localdomain_orcl -Dsysman.md5password=true -Drepapi.oracle.home=/u01/app/oracle/product/11.2.0/db_1 -Ddisable.checkForUpdate=true -Doracle.sysman.ccr.ocmSDK.websvc.keystore=/u01/app/oracle/product/11.2.0/db_1/jlib/emocmclnt.ks -Dice.pilots.html4.ignoreNonGenericFonts=true -Djava.awt.headless=true -jar /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/home/oc4j.jar -config /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_localhost.localdomain_orcl/config/server.xml
oracle 6265 1 0 09:33 ? 00:00:07 oracleorcl (LOCAL=NO)
oracle 6305 1 0 09:33 ? 00:00:00 oracleorcl (LOCAL=NO)
oracle 6538 1 0 09:33 ? 00:00:01 oracleorcl (LOCAL=NO)
oracle 6780 1 0 09:33 ? 00:00:00 oracleorcl (LOCAL=NO)
oracle 6786 1 0 09:33 ? 00:00:01 oracleorcl (LOCAL=NO)
oracle 6848 1 0 09:33 ? 00:00:04 oracleorcl (LOCAL=NO)
oracle 6898 1 0 09:33 ? 00:00:00 oracleorcl (LOCAL=NO)
oracle 6900 1 0 09:33 ? 00:00:00 oracleorcl (LOCAL=NO)
oracle 8366 1 0 09:52 ? 00:00:00 oracleorcl (LOCAL=NO)
oracle 11218 1 0 10:18 ? 00:00:00 ora_fbda_orcl
oracle 15277 1 0 11:10 ? 00:00:00 ora_w000_orcl
oracle 16644 5221 0 11:28 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 16894 1 0 11:32 ? 00:00:00 ora_rvwr_orcl
oracle 16978 1 0 11:33 ? 00:00:00 oracleorcl (LOCAL=NO)
oracle 17141 1 0 11:34 ? 00:00:00 oracleorcl (LOCAL=NO)
oracle 17354 5758 0 11:37 pts/2 00:00:00 grep orcl
(이제 flashback database 실습용 유저 만들고 접속하기)
SYS> create user fd identified by fd;
User created.
SYS> grant connect, resource to fd;
Grant succeeded.
SYS> conn fd/fd
Connected.
FD> create table fd_test
2 (id number(2));
Table created.
FD> insert into fd_test
2 values(12);
1 row created.
FD> insert into fd_test
2 values(22);
1 row created.
FD> commit;
Commit complete.
FD> conn / as sysdba
Connected.
SYS> select current_scn from v$database;
CURRENT_SCN
-----------
1493883
SYS> select scn_to_timestamp(1493883) from dual;
SCN_TO_TIMESTAMP(1493883)
---------------------------------------------------------------------------
03-JUL-18 11.42.51.000000000 AM
SYS> drop user fd cascade;
User dropped.
SYS> create user aa identified by aa;
User created.
SYS> grant connect, resource to aa;
Grant succeeded.
SYS> conn aa/aa
Connected.
AA> create table aatest
2 (id number);
Table created.
AA> conn hr/hr
Connected.
HR> update employees
2 set salary = salary*1.1;
20 rows updated.
HR> commit;
Commit complete.
HR> select avg(salary) from employees;
AVG(SALARY)
-----------
9691
HR> conn fd/fd
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
>
(유저가 삭제되었음을 발견)
> conn / as sysdba
Connected.
(DB를 내린다.)
SYS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
(마운트 상태까지만 올린다.)
SYS> startup mount exclusive
ORACLE instance started.
Total System Global Area 623546368 bytes
Fixed Size 1338308 bytes
Variable Size 469763132 bytes
Database Buffers 146800640 bytes
Redo Buffers 5644288 bytes
Database mounted.
(시점을 정하여 flashback 한다.)
SYS> flashback database to scn 1493883;
Flashback complete.
(DB를 다시 연다.)
SYS> alter database open resetlogs;
Database altered.
SYS> conn fd/fd
Connected.
FD> select * from fd_test;
ID
----------
12
22
FD> conn aa/aa
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
(fd 유저는 살아났지만, 그 후에 수정(추가)한 aa 유저는 없다.)
> conn / as sysdba
Connected.
SYS> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
(불완전 복구와 매우 닮았음을 알 수 있다.)
-----------------------------------------------------------------------
Managing Memory
실습)
SYS> col component for a35
SYS> select component, current_size/1024/2014
2 from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE/1024/2014
----------------------------------- ----------------------
shared pool 113.890765
large pool 4.06752731
java pool 2.03376365
streams pool 2.03376365
DEFAULT buffer cache 71.1817279
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
COMPONENT CURRENT_SIZE/1024/2014
----------------------------------- ----------------------
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache 0
14 rows selected.
SYS> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SYS> alter system set db_keep_cache_size=10m;
System altered.
SYS> alter system set db_recycle_cache_size=10m;
System altered.
SYS> select component, current_size/1024/2014
2 from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE/1024/2014
----------------------------------- ----------------------
shared pool 113.890765
large pool 4.06752731
java pool 2.03376365
streams pool 2.03376365
DEFAULT buffer cache 58.979146
KEEP buffer cache 6.10129096
RECYCLE buffer cache 6.10129096
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
COMPONENT CURRENT_SIZE/1024/2014
----------------------------------- ----------------------
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache 0
14 rows selected.
SYS> select tablespace_name, block_size from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
SYSTEM 8192
SYSAUX 8192
UNDOTBS1 8192
TEMP 8192
USERS 8192
SYS> create tablespace sales
2 datafile '/u01/app/oracle/oradata/orcl/sales01.dbf'
3 size 10m blocksize 4k;
create tablespace sales
*
ERROR at line 1:
ORA-29339: tablespace block size 4096 does not match configured block sizes
SYS> alter system set db_4k_cache_size=10m;
System altered.
SYS> create tablespace sales
2 datafile '/u01/app/oracle/oradata/orcl/sales01.dbf'
3 size 10m blocksize 4k;
Tablespace created.
SYS> select tablespace_name, block_size from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
SYSTEM 8192
SYSAUX 8192
UNDOTBS1 8192
TEMP 8192
USERS 8192
SALES 4096
6 rows selected.
SYS> conn hr/hr
Connected.
HR> analyze table bigemp compute statistics;
Table analyzed.
HR> select num_rows, blocks, empty_blocks from user_tables
2 where table_name = 'BIGEMP';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
655360 7051 117
HR> ed
Wrote file afiedt.buf
1 select tablespace_name, num_rows, blocks, empty_blocks from user_tables
2* where table_name = 'BIGEMP'
HR> /
TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
USERS 655360 7051 117
HR> conn / as sysdba
Connected.
SYS> alter database datafile
2 '/u01/app/oracle/oradata/orcl/sales01.dbf' resize 100m;
Database altered.
SYS> conn hr/hr
Connected.
HR> create table bigemp2
2 tablespace sales
3 as
4 select * from bigemp;
Table created.
HR> analyze table bigemp2 compute statistics;
Table analyzed.
HR> select tablespace_name, num_rows, blocks, empty_blocks from user_tables
2 where table_name = 'BIGEMP2';
TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
SALES 655360 13975 105
HR>