Home
[Oracle Database] - Oracle Database A->Z
December 22, 2018 |
Each database has a different concept and we should know that and it helps easy to organize, manage also easy to choose an accordant RMS for your system.
 |
| Oracle Instance and database |
Database Storage Structure
* Physical Storage Structure:
Data files > Control File > Online redo log file
- Data files: Oracle DB has one or more physical data files.
- Control File: Contain metadata specifying the physical structure of the database included database name, the names, and location of the database file.
- Online redo log file: is made up of redo entries, which record all changes made to data.
* Logical Storage Structure:
Data blocks > Extents > Segments > Tablespaces.
- Data blocks: data stored in data blocked. 1 data block correspond number byte on disk.
- Extents: An extent is a specific number of logically contiguous data blocks, obtained in a single allocation, used to store a specific type of information.
- Segments: A segment is a set of extents allocated for a user object (for example, a table or index), undo data, or temporary data.
- Tablespaces: A database is divided into logical storage units called tablespaces. A tablespace is a logical container for a segment. Each tablespace contains at least one data file.
Oracle Database 11c
* Data Type
+ Character
char(s), nchar(s)
varchar2(s), nvarchar2(s)
+ Number (p is the precision and s is the scale)
number(p,s): p = 1 to 38, s = -84 to 124
+BOOLEAN : NULL, TRUE, FALSE
<var1> BOOLEAN; //declare with null
<var2> BOOLEAN NOT NULL := TRUE;
<var3> BOOLEAN NOT NULL := FALSE;
Ex:
department_id number(10), department_name varchar2(50) NOT NULL,
Ref: https://www.techonthenet.com/oracle/datatypes.php
========================================================================
INDEX
An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access.
Create index:
CREATE INDEX <INDEX_NAME> ON <TABLE> (COLUMN1, COLUMN2,...);Ex:
CREATE INDEX members_last_name_i
ON members(last_name);
Type of Indexes
B-tree indexes
B-trees, short for balanced trees. B-tree index is an ordered list of values divided into ranges.B-Tree has 2 types: Branch Blocks and Leaf Block.
- Branch Blocks: for searching
- Leaf Block: for store values.
========================================================================
Source:
https://docs.oracle.com/cd/E11882_01/server.112/e40540/intro.htm#CNCPT914
========================================================================
Create Synonym
CREATE SYNONYM <NAME> ON FOR schema.object;Ex:
User A has table Customer.
User B want to use Customer from User A:
No Synonym:
Select * from a.customer;
— create synonym for user B
CREATE SYNONYM customer FOR a.customer;
With Synonym:
Select * from customer;
DATE-TIME
TO_CHAR(datetime);
========================================================================
RMAN tool
The RMAN executable is automatically installed with the database and is
typically located in the same directory as the other database
executables. For example, the RMAN client on Linux is located in
$ORACLE_HOME/bin.Retention policy
RMAN > show all;
========================================================================
Other
1. Show table structure/columnsdescribe <table_name>; // in SQL Plus
DESC <table_name>;
2. Table space is full
sqlplus /as sysdba
alter tablespace USERS add datafile '+DATA' size 100M autoextend on;
3. Check and delete archivelog
// using rman target
rman target /
// check archivelog
crosscheck archivelog all;
// check backup log
crosscheck backupset;
//delete archivelog backup has expired.
delete expired backupset;
delete obsolete;
//delete archivelog has expired.
delete expired archivelog all;
delete archivelog all completed
before ‘sysdate-7’;
//Check disk group
set lin 250col name format a10col USABLE_FILE_GB format 999,999.00col TOTAL_GB format 999,999.00col FREE_GB format 999,999.00col USABLE_CALC_GB format 999,999.00select name, USABLE_FILE_MB/1024 USABLE_FILE_GB, total_mb/1024 TOTAL_GB, free_mb/1024 FREE_GB, 100-round(free_mb/total_mb*100) "usgae(%)", ((FREE_MB - REQUIRED_MIRROR_FREE_MB))/1024 USABLE_CALC_GB, state, type, group_numberfrom v$asm_diskgroup;
//Show Recovery Dest File
show parameter DB_RECOVERY_FILE_DEST_SIZE;//alter Recovery Dest size
alter system set DB_RECOVERY_FILE_DEST_SIZE = 300G SCOPE=BOTH SID='*';//Show RMAN backup job details
SELECT session_key, input_type, status, start_time, end_time, elapsed_seconds/3600 hrsFROM v$rman_backup_job_details;
4. Check archive log by SQLPlus*
Ref:
// cd to <oracle_path>/bin
sqlplus /nolog
connect SYS as SYSDBA
//check log mode
select log_mode from v$database;
//check information of archivelog , see size, path of log.
archive log list;
// show archivelog
SQL> show parameter log_archive_dest;
// show recover
SQL> show parameter recovery;
//show archive log list
SQL> archive log list;
SQL> archive log list;
//show use db recovery file dest
SQL > show parameter db_recovery_file_dest;
// Find SPACE_USED, SPACE_LIMIT
SQL> select * from V$RECOVERY_FILE_DEST;
SQL> select NAME,SPACE_LIMIT/1024/1024 TOTAL_MB,SPACE_USED/1024/1024 USED_MB from v$RECOVERY_FILE_DEST;
//change archive log dest
SQL > alter system set LOG_ARCHIVE_DEST = 'location=/<path>'
//Specifying alternate destinations
SQL >
//Check usage data or Archive log,
SQL > SELECT * FROM V$RECOVERY_AREA_USAGE;
* Select limit data
Oracle: select * from table where rownum <= 2;
SQL Server: select top 200 * from table;
MySql: select * from table where condition limit 20;
2. ora-00937: not a single-group group function
Ex:
//ora-00937: not a single-group group function
SELECT CUS_NAME, MAX(AGE)
FROM CUSTOMER;
// CORRECT
SELECT CUS_NAME, MAX(AGE)
FROM CUSTOMER
GROUP BY CUS_NAME;
in Mysql, we can use without GROUP;
3. Turn on cache PL/SQL and ouput to Console
SQL> SET SERVEROUTPUT ON SIZE 10000000;
on script:
dbms_output.enable(1000000);
dbms_output.disable;
dbms_output.put('message'); // output message
dbms_output.new_line; // create new line
dbms_output.put_line(''); //output message with entered line.
4. Create user on oracle database
When you created a user, remember that username must be UPPERCASE. if it's not, you cannot login.
========================================================================
SQL
1.Compare between two date
Select *From dual aWhere trunc(a.reg_dt) = trunc(current_date)
2. Check table space
#Check tablespace size
//MB
Select (sum(bytes)/1024/1024) Space_allocatedfrom dba_data_fileswhere tablespace_name=upper('&tname');
//GBSelect (sum(bytes)/1024/1024/1024) Space_allocatedfrom dba_data_fileswhere tablespace_name=upper('&tname');
3. RMAN tool
Back up log
RMAN> backup archivelog until time 'sysdate-2' delete input;
Crosscheck commands:
To crosscheck all backups use:
RMAN> CROSSCHECK BACKUP;
To list any expired backups detected by the CROSSCHECK command use:
RMAN> LIST EXPIRED BACKUP;
To delete any expired backups detected by the CROSSCHECK command use:
RMAN> DELETE EXPIRED BACKUP;
To crosscheck all archive logs use:
RMAN> CROSSCHECK ARCHIVELOG ALL;
To list all expired archive logs detected by the CROSSCHECK command use:
RMAN> LIST EXPIRED ARCHIVELOG ALL;
To delete all expired archive logs detected by the CROSSCHECK command use:
RMAN> DELETE EXPIRED ARCHIVELOG ALL;
To crosscheck all datafile image copies use:
RMAN> CROSSCHECK DATAFILECOPY ALL;
To list expired datafile copies use:
RMAN> LIST EXPIRED DATAFILECOPY ALL;
To delete expired datafile copies use:
RMAN> DELETE EXPIRED DATAFILECOPY ALL;
To crosscheck all backups of the USERS tablespace use:
RMAN> CROSSCHECK BACKUP OF TABLESPACE USERS;
To list expired backups of the USERS tablespace:
RMAN> LIST EXPIRED BACKUP OF TABLESPACE USERS;
To delete expired backups of the USERS tablespace:
RMAN> DELETE EXPIRED BACKUP OF TABLESPACE USERS;
4. ORA-01012: not logged on error
Ref: https://doyensys.com/blogs/ora-01012-not-logged-on-error/
$ sysresv // get shared memory ID
$ipcrm -m <ID>
$sqlplus / as sysdba
$ startup // start up instance
5. Check account status of users
select username, account_status
from dba_users
where username = 'your_user_name';
Is updating...
Previous Article
