Shulou(Shulou.com)05/31 Report--

Oracle which commonly used SQL sentences, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Find all fields in the database to correspond to the table

Select C.column_name,C.TABLE_NAME from dba_tab_columns C where owner=''

Check the top three class scores of each subject scro

Select id, name, class, scro

From (select row_number () over (partition by class order by scro desc) cnt

Id

Name

Class

Scro

From student) a

Where a.cnt = 1

Order by rw.class desc) n

Where rownum X

, what = > 'delete_data;'

, next_date = > to_date ('25qq08xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxuanxuanxxxxxxxxxxx

, interval = > 'sysdate+1/24/12'

, no_parse = > FALSE

);

SYS.DBMS_OUTPUT.PUT_LINE ('Job Number is:' | | to_char (x))

COMMIT

END

-- View current oracle plan

Select job,next_date,next_sec,failures,broken from user_jobs

-- delete a job

Begin

Dbms_job.remove (46);-46 is the job number

End

-- insert all data from the corresponding fields of the old table into the new table

Insert into aaa (id,name) select b.idjime b.name from bbb

Article search: [click to package the article]

[go to the forum on this site and communicate with peers]

Select * from all_users; # # View all users

Select name from vault database; # # View the current database

Database test; # # enter test database

Select * from vault instance; # # View all database instances

Shutdown immediate # # shut down the database

Alter user sys identified by new_password; # # change user password

Select username,password from dba_users; # # View the user and password in the instance when

Show parameter control_files; # # View control files

Select member from vault log file; # # View log files

Show parameter; # # View database parameters

Select * from user_role_privs; # # View the role of the current user

Select username,default_tablespace from user_users; # # View the default tablespace of the current user

Alter user system identified by [password] # # modify a user's password

ALTER USER "SCOTT" ACCOUNT UNLOCK # # unlock SCOTT users

Show parameter processes; # # View maximum number of sessions

View all the data tables for the current library:

SQL > select TABLE_NAME from all_tables

Select * from all_tables

SQL > select table_name from all_tables where table_name like'u'

TABLE_NAME-default_auditing_options

View table structure: desc all_tables

Create users and grant permissions

#-create a user and grant permissions-- #

Create user mpss

Identified by "mpss12"

Default tablespace TS_MPSS_DATA

Temporary tablespace TEMP

Give permissions to the user

Grant connect to mpss

Grant resource,create session to mpss; developer role

Grant create procedure to dbuser; # these permissions are sufficient for development and production environments

Authorize the user

Grant dba to spms;-- grants DBA permission

Grant unlimited tablespace to lxg;-- grants unlimited tablespaces

Grant select any table to lxg;-- grants query to any table

Grant select any dictionary to lxg;-- grants query to any dictionary

Delete user

Drop user mpss cascade

Build tablespace

#-- create a tablespace-- #

= create tablespace = =

CREATE TABLESPACE "TS_MPSS_DATA"

LOGGING

DATAFILE'/ mpss/data/ts_mpss_data.dbf' SIZE 1024m

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

=

= establish temporary tablespace = =

CREATE

TEMPORARY TABLESPACE "SWVIP" TEMPFILE'/ app/oracle/oradata/

Sworacle/SWVIP.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM

SIZE 1M

=

Create tablespace TS_MPSS_DATA datafile'/ mpss/data/ts_mpss_data.bdf 'size 1024m autoextend on; # autoextend on automatic extension

#

View tablespace

#-View tablespace size-#-

SELECT D.TABLESPACE_NAME "Name"

TO_CHAR (A.BYTES-DECODE (F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024)) / (A.BYTES / 1024 / 1024)) * 100Finals 99990.9') "used (%)"

TO_CHAR ((DECODE (F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024), '999990.9') "Free (M)"

FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F

WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME

AND F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME

#-#

SELECT D. TABLESPACENAME Magna Space "SUM_SPACE (M)", BLOCKS SUM_BLOCKS,SPACE-NVL (FREE_SPACE,0) "USED_SPACE (M)"

ROUND ((1-NVL (FREE_SPACE,0) / SPACE) * 100Pol 2) "USED_RATE (%)", FREE_SPACE "FREE_SPACE (M)"

FROM

(SELECT TABLESPACE_NAME,ROUND (SUM (BYTES) / (1024 / 1024), 2) SPACE,SUM (BLOCKS) BLOCKS

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) D

(SELECT TABLESPACE_NAME,ROUND (SUM (BYTES) / (1024 / 1024), 2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME (+)

UNION ALL-if have tempfile

SELECT D. TABLESPACENAMEMagneSpace "SUM_SPACE (M)", BLOCKS SUM_BLOCKS

USED_SPACE "USED_SPACE (M)", ROUND (NVL (USED_SPACE,0) / SPACE*100,2) "USED_RATE (%)"

NVL (FREE_SPACE,0) "FREE_SPACE (M)"

FROM

(SELECT TABLESPACE_NAME,ROUND (SUM (BYTES) / (1024 / 1024), 2) SPACE,SUM (BLOCKS) BLOCKS

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) D

(SELECT TABLESPACE_NAME,ROUND (SUM (BYTES_USED) / (1024 / 1024), 2) USED_SPACE

ROUND (SUM (BYTES_FREE) / (1024 / 1024), 2) FREE_SPACE

FROM V$TEMP_SPACE_HEADER

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME (+)

View the name and size of the tablespace physical file

#-name and size of the tablespace physical file-#-

Select tablespace_name, file_id, file_name

Round (bytes/ (1024,1024), 0) total_space

From dba_data_files

Order by tablespace_name

#

View the path where the data file is placed

#

SQL > col file_name format A50

SQL > select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id

#

View database library objects

Select owner, object_type, status, count (*) count# from all_objects group by owner, object_type, status

Use your system administrator to see how many user connections there are in the current database:

SQL > select username,sid,serial# from v$session

Expand table space

#

Alter tablespace G000 add datafile'/ dev/vgbilling/rg000_lv03' SIZE 7500m

Add a 7500m logical volume'/ dev/vgbilling/rg000_lv03' to table G000

#

Check for objects that have been locked for a long time

#

SQL > select a.sessiontrainidreagea.processmcea.lockedcallimodeauthorb.objectpapernamerewritingb.objectdistributypereb.status from v$locked_object aredbathing objects b where a.object_id=b.object_id

#

Source: http://www.diybl.com/course/7_databases/oracle/Oracleshl/200899/141376.html

Sys user login

Create a tablespace:

SQL > create tablespace lmsstemp datafile'F:\ ORADATA\ LMSS\ LMSSTEMP01.DBF' SIZE 1

024M extent management local

Add data files to the tablespace

Alter tablespace lmsstemp add datafile'F:\ ORADATA\ LMSS\ LMSSTEMP02.DBF' SIZE 1

024M

Change the tablespace to automatically expand

SQL > alter database datafile'F:\ ORADATA\ LMSS\ LMSSTEMP01.DBF' autoextend on

View tablespace information

SQL > select file_name,tablespace_name,autoextensible from dba_data_files

Authorization:

Create any table to leon

-- Create the user (executed with sys)

Create user xx identified by xx123 default tablespace lmss temporary tablespace TEMP profile DEFAULT

-- Grant/Revoke role privileges (executed with sys)

Grant connect to xx

-- create a view for hy users (with leon users)

Create or replace view view_tableName as

Select column . From table

-- Grant/Revoke object privileges

Grant select, update on RES_XIM_CARD to hy

-- create synonyms

Create synonym RES_XIM_CARD for YY.RES_XIM_CARD

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.