Creating Control file
sql>shu immediate
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "BANK" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\REDO01.LOG' SIZE 10M,
GROUP 2 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\REDO02.LOG' SIZE 10M,
GROUP 3 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\REDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\SYSTEM01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\UNDOTBS01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\SYSAUX01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\USERS01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;
--------------------------------------------------------------------------------------------------------------
creating redolog file;
at open stage
ALTER DATABASE DB_name ADD LOGFILE GROUP 4
('$ORACLE_HOME/ORADATA/u01/log3a.rdo',
'$ORACLE_HOME/ORADATA/u02/log3b.rdo')
SIZE 1M;
ADDING LOG FILE TO GROUP:
ALTER DATABASE db_name ADD LOGFILE MEMBER
'$HOME/ORADATA/u04/log1c.rdo' TO GROUP 1,
'$HOME/ORADATA/u04/log2c.rdo' TO GROUP 2,
'$HOME/ORADATA/u04/log3c.rdo' TO GROUP 3;
Dropping redolog file:
ALTER DATABASE DB_name DROP LOGFILE GROUP 4;
THEN DELETE THE PHYSICAL FILES MANUALLY
clearing online redolog files;
ALTER DATABASE DB_name CLEAR LOGFILE
'$HOME/ORADATA/u01/log2a.rdo';
---------------------------------------------------------------------------------------------------------------
Creating Tablespace:
CREATE TABLESPACE tbs01
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;
CREATE TABLESPACE data02
DATAFILE ‘/u01/oradata/data02.dbf’ SIZE 5M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
altering Tablespace:
ALTER DATABASE Db_name DATAFILE
'/u01/oradata/userdata02.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
Adding Data Files to a Tablespace
ALTER TABLESPACE app_data
ADD DATAFILE '/u01/oradata/userdata03.dbf'
SIZE 200M;
Resize of Data Files:
ALTER DATABASE db_name
DATAFILE '/u03/oradata/userdata02.dbf'
RESIZE 200M;
1.........Moving (or) Renaming Data Files without shutdown;
1. Take the tablespace offline.
2. Use an operating system command to move or copy the files.
3. Execute the ALTER TABLESPACE RENAME DATAFILE command.
4. Bring the tablespace online.
5. Use an operating system command to delete the file if necessary.
ALTER TABLESPACE userdata
RENAME
DATAFILE '/u01/oradata/userdata01.dbf'
TO '/u01/oradata/userdata01.dbf';
2..........Moving (or) Renaming Data Files with shutdown;
1. Shut down the database.
2. Use an operating system command to move the files.
3. Mount the database.
4. Execute the ALTER DATABASE RENAME FILE command.
5. Open the database.
ALTER DATABASE db_name RENAME
FILE '/u01/oradata/system01.dbf'
TO '/u03/oradata/system01.dbf';
creating undo tablespace:
---------------------------
CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/oradata/undo101.dbf' SIZE 40M;
ALTER TABLESPACE undotbs
ADD DATAFILE 'undotbs2.dbf' SIZE 30M
AUTOEXTEND ON;
switching undo tablespace;
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;
dropping undo tablespace;
DROP TABLESPACE UNDOTBS2;
To drop an active UNDO tablespace:
– Switch to a new UNDO tablespace
– Drop the tablespace after all current transactions
are complete
Undo Data Statistics
--------------------
SQL> SELECT end_time,begin_time,undoblks FROM v$undostat;
Obtaining Undo Segment Information
-----------------------------------
V$ROLLSTAT and V$ROLLNAME Join the V$ROLLSTAT and V$ROLLNAME views to obtain the statistics of the undo segments currently used by the instance.
SQL> SELECT n.name, s.extents, s.rssize,s.hwmsize,
s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;
V$TRANSACTION and V$SESSION
------------------------------
To check the use of a undo segment by currently active transactions, join the
V$TRANSACTION and V$SESSION views:
SQL> SELECT s.username, t.xidusn, t.ubafil,
t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;
Creating Temporary Tablespace:
-------------------------------
CREATE TEMPORARY TABLESPACE temp01
TEMPFILE 'C:\oracle\product\10.1.0\oradata\bank\temp0101.dbf' size 100M AUTOEXTEND ON NEXT 10M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER TABLESPACE temp add
TEMPFILE 'C:\oracle\product\10.1.0\oradata\bank\temp01.dbf' size 100M AUTOEXTEND ON NEXT 10M MAXSIZE unlimited;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
ALTER TABLESPACE tablespace_name READ [ONLY | WRITE]
Dropping Tablespace:
DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES;
Tables
--------------
ALTER TABLE hr.employees
ALLOCATE EXTENT(SIZE 500K
DATAFILE ‘/DISK3/DATA01.DBF’);
ALTER TABLE hr.employees
MOVE TABLESPACE data1;
ALTER TABLE hr.employees
DROP COLUMN comments
CASCADE CONSTRAINTS CHECKPOINT 1000;
Mark a column as unused
------------------------
ALTER TABLE hr.employees
SET UNUSED COLUMN comments CASCADE CONSTRAINTS;
Drop unused columns
-------------------
ALTER TABLE hr.employees
DROP UNUSED COLUMNS CHECKPOINT 1000;
Continue to drop column operation
----------------------------------
ALTER TABLE hr.employees
DROP COLUMNS CONTINUE CHECKPOINT 1000;
Creating a Profile:
--------------
Resource Limit
------------------
CREATE PROFILE developer_prof LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION 10000
IDLE_TIME 60
CONNECT_TIME 480;
Creating a New User:
-----------------------
CREATE USER aaron
IDENTIFIED BY soccer
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 15m ON users
PASSWORD EXPIRE;
Granting Object Privileges
----------------------------
GRANT EXECUTE ON dbms_output TO jeff;
GRANT UPDATE ON emi.customers TO jeff WITH GRANT OPTION;
Revoking Object Privileges
----------------------------
REVOKE SELECT ON emi.orders FROM jeff;
Auditing
----------
• Statement auditing
AUDIT TABLE;
• Privilege auditing
AUDIT create any trigger;
• Schema object auditing
AUDIT SELECT ON emi.orders;
disable:
--------
Add no infront of audit.
Ex: NOAUDIT TABLE;
Creating Roles
---------------
CREATE ROLE oe_clerk;
CREATE ROLE hr_clerk IDENTIFIED BY bonus;
CREATE ROLE hr_manager IDENTIFIED EXTERNALLY;
Modifying Roles
----------------
ALTER ROLE oe_clerk IDENTIFIED BY order;
ALTER ROLE hr_clerk IDENTIFIED EXTERNALLY;
ALTER ROLE hr_manager NOT IDENTIFIED;
Assigning Roles
------------------
GRANT oe_clerk TO scott;
GRANT hr_clerk TO hr_manager;
GRANT hr_manager TO scott WITH ADMIN OPTION;
No comments:
Post a Comment