Shulou(Shulou.com)06/01 Report--

View oracle version

Select banner from sys.v_$version

-

Check the number of connections for different users

Select username,count (username) from v$session where username is not null group by username

-

Query the number of concurrent connections to oracle

Select count (*) from v$session where status='ACTIVE'

-

Import and export of tables in the Oracle database:

Take the database under Windows as an example (in cmd):

Export table:

1. Export the entire database

User name / password of exp import table @ instance name file='E:\ xxx.dmp' full=y

two。 Export single or multiple tables

The user name / password of the exp guide table @ instance name file='E:\ xxx.dmp' tables=t1 [(t _ 1 ~ T _ 2 ~ T3)]

3. Export tables under one or more users in the database

User name / password of exp import table @ instance name file='E:\ xxx.dmp' owner= (system,sys)

4. Export the field filed1 in the table table1 in the database with data starting with "00"

User name / password of exp import table @ instance name file='E:\ xxx.dmp' tables= (table1) query=\ "where filed1 like'00%'\"

-

Import Table:

Import the data from E:\ xxx.dmp into a database.

User name / password of imp import table @ instance name file=e:\ xxx.dmp

User name / password of imp import table @ instance name full=y file=e:\ xxx.dmp ignore=y

After that, add ignore=y to ignore the error of import and import directly.

2 Import the table table1 in e:\ xxx.dmp

User name / password of imp import table @ instance name file=e:\ xxx.dmp tables= (table1)

-

Linux's words directly exp,imp according to the prompt operation, the effect is the same. If you pass the statement, you can now write on the emedit to copy and paste directly.

-

View the version in the database:

Select * from v$version

View ORACLE version information under Linux:

File $ORACLE_HOME/bin/oracle

-

The database server views the character set:

Select * from nls_database_parameters

Below NLS_CHARACTERSET is the character set of the database server.

-

View the xxx usage of the tablespace: (sysdba permission required):

Select / * + ordered use_merge (aPerm b) * /

A.tablespace_name tablespace name

Total/ (1024 to 1024) tablespace size

(total-free) / (1024-1024) Tablespace usage size

Free/ (1024 to 1024) Tablespace remaining size

Round ((total-free) / total,4) * 100 "usage%"

From (select tablespace_name,sum (bytes) free from dba_free_space

Group by tablespace_name) a

(select tablespace_name,sum (bytes) total from dba_data_files

Group by tablespace_name) b

Where a.tablespace_name = b.tablespace_name

And a.tablespace_name = 'xxx'

-

View the tablespaces under the current user: (non-sysdba)

Select

A.bytes/1024/1024 "used (MB)"

B.bytes/1024/1024 "free (MB)"

A.bytes/1024/1024+b.bytes/1024/1024 "total (MB)"

From user_ts_quotas a,user_free_space b

Where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a. TABLESPACENAMESTRON XXX'

-

View the permissions of the current role XXX:

Select * from dba_sys_privs where grantee='XXX'

-

View the roles of the current user:

Select * from user_role_privs

Select * from session_privs

-

View the system permissions and table-level permissions of the current user

Select * from user_sys_privs

Select * from user_tab_privs

-

See which users have sysdba or sysooper privileges:

Select * from V$PWFILE_USERS

-

View tablespace quotas for which the user is XXX. (- 1 is unrestricted)

Select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='XXX'

-

Set the tablespace quota limit for user mc to 100m:

Alter user mc quota 100M on tablespacname

-

Set the tablespace quota for user mc to unlimited:

Alter user mc quota unlimited on tablespacname

-

Give the user mc unlimited permission to configure tablespaces:

Grant unlimited tablespace to mc

-

View the instance name of the current database:

Select instance_name from v$instance

-

Modify user and temporary tablespaces:

ALTER USER SCOTT DEFAULT TABLESPACE USERS

ALTER USER SCOTT TEMPORARY TABLESPACE TEMP

-

View the default tablespace for the current user:

Select username,default_tablespace from user_users

-

Check whether archiving is enabled; enable / close archiving:

-

View the archive:

Sqlplus > archive log list

Open archiving

Sqlplus > shutdown immediate; (stop the database before starting the archive)

Sqlplus > startup mount; (the database starts in mount mode)

Sqlplus > alter database archivelog; (start database archiving)

Sqlplus > alter system set log_archive_dest_1= "/ arch" (change path, use drive letter)

Sqlplus > alter database open; (Open Database)

Sqlplus > archive log list; (check to see if the archive is open)

Close archiving

Alter database noarchivelog

-

View archive log usage:

Select * from v$flash_recovery_area_usage

View user account status:

Select username,account_status from dba_users where username in ('DBSNMP','SYSMAN')

-

View the location and status of REDO logs, and how to add or delete REDO log groups:

Location:

Select * form v$logfile

Status:

Select group#,thread#,bytes/1024/1024,status from v$log

: add

Alter database add logfile group 1 | 2 | 3 | 4 ('/ u01/oracle/oradata/mcocp/redo01 | 02 | 03 | 04') size 100m

Delete:

Alter database drop logfile group 1 | 2 | 3 | 4

Note: the default redo logsize of oracle11g is 50m

-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.