Go Oracle Common Commands

Common commands in Oracle Oracle



Log Management
1.forcing Log Switches
sql> alter system switch logfile;

2.forcing checkpoints
Sql> alter system checkpoint;

3.adding Online Redo Log groups
sql> ALTER DATABASE Add logfile [Group 4]
Sql> ('/disk3/log4a.rdo ', '/disk4/log4b.rdo ') size 1m;

4.adding Online Redo Log Members
sql> ALTER DATABASE Add logfile Member
Sql> '/disk3/log1b.rdo ' to group 1,
Sql> '/disk4/log2b.rdo ' to group 2;

5.changes the name of the online redo logfile
sql> ALTER DATABASE rename file ' C:/oracle/oradata/oradb/redo01.log '
sql> to ' C:/oracle/oradata/redo01.log ';

6.drop Online Redo Log groups
sql> ALTER DATABASE drop logfile Group 3;

7.drop Online Redo Log Members
sql> ALTER DATABASE drop logfile member ' C:/oracle/oradata/redo01.log ';

8.clearing Online redo log files
Sql> ALTER DATABASE clear [unarchived] logfile ' C:/oracle/log2a.rdo ';

9.using Logminer Analyzing Redo LogFiles

A. In the Init.ora specify Utl_file_dir = '
B. sql> execute Dbms_logmnr_d.build (' Oradb.ora ', ' c:\oracle\oradb\log ');
C. sql> Execute Dbms_logmnr_add_logfile (' C:\oracle\oradata\oradb\redo01.log '),
Sql> dbms_logmnr.new);
D. sql> execute Dbms_logmnr.add_logfile (' C:\oracle\oradata\oradb\redo02.log '),
Sql> dbms_logmnr.addfile);
E. Sql> Execute DBMS_LOGMNR.START_LOGMNR (dictfilename=> ' C:\oracle\oradb\log\oradb.ora ');
F. sql> select * from V$logmnr_contents (v$logmnr_dictionary,v$logmnr_parameters
Sql> v$logmnr_logs);
G. sql> execute DBMS_LOGMNR.END_LOGMNR;


Table Space Management
1.create tablespaces
sql> Create tablespace tablespace_name datafile ' c:\oracle\oradata\file1.dbf ' size 100m,
Sql> ' c:\oracle\oradata\file2.dbf ' size 100m minimum extent 550k [logging/nologging]
sql> default storage (initial 500k next 500k maxextents pctinccease 0)
sql> [Online/offline] [permanent/temporary] [Extent_management_clause]

2.locally managed Tablespace
sql> Create tablespace user_data datafile ' c:\oracle\oradata\user_data01.dbf '
Sql> Size 500m extent management local uniform size 10m;

3.temporary tablespace
sql> Create temporary tablespace temp tempfile ' c:\oracle\oradata\temp01.dbf '
Sql> Size 500m extent management local uniform size 10m;

4.change the storage setting
sql> alter tablespace App_Data minimum extent 2m;
sql> alter tablespace app_data default storage (initial 2m next 2m maxextents 999);

5.taking tablespace offline or online
sql> alter tablespace App_Data offline;
sql> alter tablespace App_Data online;

6.read_only tablespace
Sql> alter tablespace App_Data read only|write;

7.droping tablespace
Sql> drop tablespace app_data including contents;

8.enableing automatic extension of data files
Sql> alter tablespace app_data add datafile ' c:\oracle\oradata\app_data01.dbf ' size 200m
Sql> Autoextend on next 10m maxsize 500m;

9.change the size fo data files manually
sql> ALTER DATABASE datafile ' C:\ORACLE\ORADATA\APP_DATA.DBF ' resize 200m;

10.Moving Data Files:alter tablespace
sql> alter tablespace app_data rename datafile ' c:\oracle\oradata\app_data.dbf '
sql> to ' c:\oracle\app_data.dbf ';

11.moving Data Files:alter Database
sql> ALTER DATABASE rename file ' c:\oracle\oradata\app_data.dbf '
sql> to ' c:\oracle\app_data.dbf ';



Table

1.create a table
Sql> CREATE TABLE table_name (column Datatype,column datatype] ...)
sql> tablespace tablespace_name [pctfree integer] [pctused integer]
sql> [Initrans integer] [maxtrans integer]
sql> Storage (initial 200k next 200k Pctincrease 0 maxextents 50)
sql> [logging|nologging] [Cache|nocache]

2.copy an existing table
sql> CREATE TABLE table_name [logging|nologging] as subquery

3.create temporary table
Sql> Create global temporary table Xay_temp as SELECT * from Xay;
On commit Preserve rows/on commit Delete rows

4.pctfree = (average row size-initial row size) *100/average Row Size
pctused = 100-pctfree-(average row size*100/available data space)

5.change Storage and Block utilization parameter
Sql> ALTER TABLE table_name PCTFREE=30 PCTUSED=50 storage (next 500k
Sql> minextents 2 maxextents 100);

6.manually Allocating extents
Sql> ALTER TABLE TABLE_NAME ALLOCATE extent (size 500k datafile ' c:/oracle/data.dbf ');

7.move tablespace
Sql> ALTER TABLE employee move tablespace users;

8.deallocate of unused space
Sql> ALTER TABLE table_name deallocate unused [keep integer]

9.truncate a table
Sql> TRUNCATE TABLE table_name;

10.drop a table
sql> DROP TABLE table_name [cascade constraints];

11.drop a column
Sql> ALTER TABLE table_name DROP COLUMN comments cascade constraints checkpoint 1000;
ALTER TABLE table_name DROP columns continue;

12.mark a column as unused
Sql> ALTER TABLE TABLE_NAME SET UNUSED column comments cascade constraints;
ALTER TABLE table_name drop unused columns checkpoint 1000;
ALTER TABLE orders drop columns continue checkpoint 1000
Data_dictionary:dba_unused_col_tabs