ORACLE study notes-ORACLE (basic commands)

2010-10-29  来源:本站原创  分类:Database  人气:73 

- View VGA Information:

show sga;

select * from v $ sgastat;

- Through the following dynamic performance view to see information:

V $ sysstat system statistics
V $ sesstat user session statistics
V $ pgastat display memory usage statistics
V $ sql_workarea SQL cursors used in the work zone information
V $ sql_workarea_active the current system work zone information
data_file: dba_data_files;
control_file: which can be seen from the init.ora, D: ora92adminora92pfileinit.ora.
redo_log: v $ logfile;
In fact D: ora92oradataora92 placed inside a lot.
select * from v $ database; archivelog which can query the information.
Section: dba_segments
District: dba_extents

- The dynamic performance view V $ process can query to each Oracle process PGA memory allocated and used memory situation,
- Which PGA_used_mem that has been used, pag_alloc_mem that has been assigned, pga_max_men that the maximum value of PGA.

SQL> select pid, pga_used_mem, pga_alloc_mem, pga_max_mem from v $ process;

- View the background processes:

SELECT * FROM v $ bgprocess WHERE paddr <> '00 ';

- View all the table space;

SQL> select tablespace_name from dba_data_files order by tablespace_name;

- View the name and size of table space:
SQL> select t.tablespace_name, round (sum (bytes / (1024 * 1024)), 0) ts_size
from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

- Create a table space syntax is:
CREATE TABLESPACE tablespacename
DATAFILE 'filename' [SIZE integer [K | M]]
[AUTOEXTEND [OFF | ON]];
- Create multiple data files of the tablespace:
SQL> create tablespace SALES
datafile 'd: salesSALES_DATA01.dbf' size 10m autoextend on next 10m maxsize 100m,
'D: salesSALES_DATA02.dbf' size 10m autoextend on next 10m maxsize
unlimited, 'd: salesSALES_DATA03.dbf' size 10m;

- View the size of table space;

SQL> SELECT TABLESPACE_NAME, SUM (BYTES) / 1024/1024 MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

- View the table space in the path of data files:

SQL> SELECT TABLESPACE_NAME, BYTES/1024/1024 FILE_SIZE_MB, FILE_NAME FROM DBA_DATA_FILES;

insert into date_test values ​​(interval '12 'month,' adjf '); test interval data type;

Delete the table space:
SQL> drop tablespace worktbs including contents;
Table space has been discarded.

Query table space will once again find no worktbs table space.
If we remove the table space again, what will happen?
Delete a table space does not exist:
SQL> drop tablespace worktbs including contents;
drop tablespace worktbs including contents
*
ERROR at line 1:
ORA-00959: tablespace 'WORKTBS' does not exist
o add a data file for the table space
SQL> alter tablespace sales add datafile 'd: oracleoradata est esttablespace
sales_data04.dbf 'size 10m autoextend on next 10m maxsize 100m,
'C: oracleoradata est esttablespacesales_data05.dbf' size 10m autoextend on next 10m maxsize unlimited, 'c: oracleoradata est esttablespacesales_data06.dbf' size 10m;

Table space has changed.
o modify the data file size
o in the data file is not used up the available space, allow for data file compression, when the compression space over unused space, it will generate an error.
SQL> alter database datafile 'c: oracleoradata est esttablespaceSALES_DATA04.dbf' resize 30m;

Database has changed.
o Close the table space data file automatically extended attributes
o alter database
o datafile 'c: SALES_DATA04.dbf',
o 'c: SALES_DATA05.dbf',
o 'c: SALES_DATA06.dbf'
o autoextend off;
o Open the table space data file automatically extended attributes
o alter database
o datafile 'c: SALES_DATA04.dbf',
o 'c: SALES_DATA05.dbf',
o 'c: SALES_DATA06.dbf'
o autoextend on;

o modify the table space attributes (offline)
o alter tablespace sales offline;
o modify the table space attributes (online)
o alter tablespace sales online;
o modify the table space attributes (read-only)
o alter tablespace sales read only;
o modify the table space attributes (read and write)
o alter tablespace sales read write;
o are not set to the system tablespace offline or read only
o system, temp, undo, undotbs

o Move the table space data file
o 1. the tablespace offline (system table space is not offline, it is not moving)
o 2. to modify the data file name
o 3. execute the command alter tablespace rename datafile
o 4. the table space online
o The first step: alter tablespace sales offline;
o The second step: the physical move data files to the destination (can be a table space
o part of the data file, you can modify the data file name)
o The third step: the logical move, modify the contents of control file

o alter tablespace sales rename
o datafile 'c: sales01.dbf' to 'd: sales02.dbf'
o - can have multiple data files, but the source file
o to to the left, aim to the right to file, the file name between commas.
o Step Four: The table space online
o alter tablespace sales online;
o Step: Confirm query dba_data_files

o Create a user to specify the default table space, disk quotas
o create user rose identified by rose default
o tablespace sales quota 10m on sales;
o to the user authorization
o grant connect, resource, dba to rose;
o User Login
o connect rose / rose
o Create Table
o create table emp (eid number)
o The default table space on the table in the sales
o check the user's storage quota dba_ts_quotas
o Delete the table space (if the table space is empty)
o drop tablespace sales;
o Delete the table space, the deleted data files (not empty)
o drop tablespace sales including contents and datafiles;
o Create a table specifying the storage location of the table
o create table mytab (tid int) tablespace sales;

o Delete the table space (if the table space is empty)
o drop tablespace sales;
o Delete the table space, the deleted data files (not empty)
o drop tablespace sales including contents and datafiles;
o Create a table specifying the storage location of the table
o create table mytab (tid int) tablespace sales;

o Show the current user space the size of each table:
o select segment_name, sum (bytes) / 1024/1024 from user_extents group by segment_name
o View space each table space size:
o select tablespace_name, sum (bytes) / 1024/1024 from dba_segments group by tablespace_name

Only valid user accounts can access the Oracle database
There are several default Oracle database user
Scott / tiger

Create a user name for the martin, the password is martinpwd
CREATE USER MARTIN IDENTIFIED BY martinpwd
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

GRANT command can be used to assign permissions to users or roles;
CONNECT role to allow users to connect to the database and create database objects.
GRANT CONNECT TO MARTIN;

RESOURCE role allows the user to use the database storage space.
GRANT RESOURCE TO MARTIN;

This system privilege allows the user to create a sequence in the current mode, this permission included in the CONNECT role.
GRANT CREATE SEQUENCE TO MARTIN;

GRANT CREATE SESSION TO MARTIN;

GRANT CREATE TABLE TO MARTIN;

GRANT CREATE VIEW TO MARTIN;

GRANT CREATE SEQUENCE TO MARTIN;

MARTIN operating grant users permissions to an object emp table:
TEST table allows the user to query the records
GRANT SELECT ON EMP TO MARTIN;

Allows the user to update records in the table TEST
GRANT UPDATE ON EMP TO MARTIN;

Allows the user to insert, delete, update, and query records in the table TEST
GRANT ALL ON EMP TO MARTIN;

ALTER USER command can be used to change the password:
MARTIN modify user's password:
ALTER USER MARTIN IDENTIFIED BY martinpass;
DROP USER command is used to delete a user:
MARTIN delete user mode:
DROP USER MARTIN CASCADE;

alter session set nls_date_format = 'yyyy-mm-dd';

create table customer (customer_id int not null, constraint pk_id primary key (customer_id));
create table salesinfo (salesid varchar2 (10), customer_id int not null, primary key (salesid, customer_id),
foreign key (customer_id) references customer (customer_id) on delete cascade);

insert into date_test values ​​(to_date ('09-09-09 ',' dd-mm-yy '),' akldjf ');
alter table date_test modify current_date timestamp;
create table timestamp_test (currenttime timestamp with time zone);
insert into timestamp_test values ​​(to_timestamp ('2006-09-09 12:12:12 ',' yyyy-mm-dd hh: mi: ss'));

相关文章
  • Oracle study notes 1 - Basic knowledge 2010-02-28

    Current mainstream database: Microsoft: sql server and access Sweden MySql: AB Company mysql IBM Corporation: DB2, Sybase, the United States: Sybase IBM Corporation: informix United States oracle Company: oracle For oracle jobs that demand is quite b

  • oracle Study Notes 8 - basic management database 2010-03-06

    Each oracle database should have at least one database administrator (DBA), for a small database, a DBA is enough, but for a large database may require multiple DBA, are responsible for various management responsibilities. Then a database administrat

  • oracle study notes two (basic date functions) 2010-12-22

    Date of function: sysdate: Returns the system time add_months (d, n); Find more than eight months of the entry of employees: select * from emp where sysdate> add_months (hiredate, 8); add_months (hiredate, 8): that from the beginning of time employed

  • Study Notes Oracle Database 02 2010-05-04

    Oracle study notes of the two - SQL Language Fundamentals 1.SQL language classification a. Data Query Language (Select statement): used to retrieve data, features, and more complex grammar, but also the most widely used b. Data Manipulation Language

  • Study Notes Oracle Database 03 2010-05-04

    Oracle study notes of the three - SQL Language Fundamentals 1. Using basic functions 2 Date function ADD_MONTH (date, i): returns the date d plus i months after the results, i may be fractional, but the database will convert it to integer, decimal pa

  • Oracle study notes (8) using PLSQL write trigger 2010-04-07

    How time flies, then quickly to the Oracle study notes of the last chapters, and through the previous seven chapters of the study we should have some of the Oracle program understand it, this stuff if not soon be forgotten for some time , so I had to

  • ORACLE Study Notes - Performance Optimization 2010-04-15

    ORACLE Study Notes - Performance Optimization http://www.51cto.com/art/200508/888.htm

  • Study Notes Oracle Database 01 2010-05-04

    One of Oracle Study Notes 1. Relational database Relational database which refers to the relevant tables and other database objects in the collection, this definition the meaning of the expression of the three parts. a. In the relational database, in

  • Oracle study notes explain the table cache 2011-04-09

    Oracle study notes explain the table cache http://database.51cto.com 2010-04-16 13:52 Anonymous I want to comment (0) Abstract: The current network appears on the "Oracle Cache Table" question, greatly accelerates the finishing line to find some

  • Extreme programming study notes - the basic practice of "sit down together." 2010-02-18

    Extreme programming study notes - the basic practice of "sit down together." Sit down together Write larger, sit down together This practice seems relatively easy to understand and implement, but it is a very important XP practices, communicatio

  • Oracle Study Notes (3) PLSQL program control structure 2010-03-20

    This is the third chapter of the study notes, study completed after the second chapter of the programming basics, from now to learn Oracle programming the ... ..., I hope that we can give some support ah! This is after all not to work overtime on Sat

  • Oracle study notes (7) subroutine and package development PLSQL 2010-04-07

    Haha, Ching Ming Festival holiday to go back a bit, it was so wonderful to go home feeling nice ah! Now again on business trip, how long time did not come, and today continues Oracle's learning! This is the seventh chapter of the study notes, study c

  • Oracle Study Notes (2) PLSQL Programming Fundamentals 2010-03-14

    This is the second chapter of the study notes, study after completion of chapter basis, from now to learn Oracle programming the ... ..., I hope that we can give some support ah! Programming tools is PLSQL Developer 7.1.4 select * from Employee; sele

  • Oracle study notes (5) in PLSQL cursor to obtain the data used in 2010-03-24

    This is the fifth chapter of the study notes, study complete database of Chapter IV of the operation and affairs, began to learn the use of a cursor ... ..., I hope that we can give some support ah! Programming tools is PLSQL Developer 7.1.4 Implicit

  • Oracle study notes (data dictionary control file rewrite the log file) 2010-04-17

    Recent study Oracle, did some study notes, mainly digital dictionary control file and redo log file contents 1. Start iSQL * Plus tool: to ensure the iSQL * Plus application server has started (usually installed by default are automatically activated

  • Oracle data dictionary of basic commands (2) 2010-08-04

    1, ORACLE startup and shutdown 1, the stand-alone environment To enable or disable the ORACLE system must first switch to the ORACLE user, as su - oracle a, start the ORACLE system oracle> svrmgrl SVRMGR> connect internal SVRMGR> startup SVRMGR&g

  • The study notes oracle 2011-08-03

    Fundamental part of the contents of introduction: oracle basic use oracle user management senior oracle table management section oracle query table oracle permissions, roles oracle function pl / sql programming oracle database management indexes, con

  • Oracle study notes 5 - JDBC operations Oracle 2010-03-06

    Using Java to manipulate the database, there are several ways: ○ 1JDBC-ODBC Bridge: ODBC data source to support the operating system level, in the end we can not understand the underlying data source which is encapsulated, it can still complete the o

  • Oracle study notes (4) change in PLSQL data and management services 2010-03-20

    This is the fourth chapter of the study notes, study after completion of Chapter III process control, began to study the data changes, the collection traversal and transaction management of the ... ..., I hope that we can give some support ah! Progra

  • Oracle Study Notes (6) Dynamic SQL 2010-03-31

    Haha, good big favor this time, but also work overtime on Saturdays, and for a long time not to send articles, and today continues to ... ... can go back to the Ching Ming Festival holiday, a good time, ah, can finally go home, really want to hurry u