Oracle Database Common Commands

Create Tablespace GRP
DataFile ' e:/grp_tbspace.dbf '
Size 3000M
Autoextend on next 5M maxsize 5000M;

/* 3rd step: Create user and specify Tablespace */
Create user tctest0707 identified by a default tablespace GRP temporary tablespace temp profile default;

/* Step 4th: Grant permissions to users */
Grant Connect,resource,dba,select any table to tctest0707;
GRANT Select any TABLE to nncz2017 with ADMIN OPTION; (Normal library execution)
--Start, run, input cmd->imp tctest0707/[email PROTECTED]:1521/ORCL full=y file=

-----11g Import to 10g
Pm_db01/szxmk16
--IMPDP Nsxmk16/[email protected] dumpfile=nsxmk16062001.dmp logfile=nsxmk16062001.log remap_schema=nsxmk16:nsxmk16 Transform=oid:n version=10.2.0.1.0
Remap_tablespace=qjhxtbs:szxmktbs
--Import files placed in F:\oracle\product\10.2.0\admin\orcl\dpdump

--(Shenzhen 11G Library CREATE table space)
Create Tablespace Szxmktbs
DataFile ' D:\app\yanchao\oradata\orcl\SZXMKTBS.dbf ' size 1000m autoextend on next 10m;
Create Tablespace GRP
DataFile ' D:\app\yanchao\oradata\orcl\GRP.dbf ' size 1000m autoextend on next 10m;
Create Tablespace shenzhendb
DataFile ' D:\app\yanchao\oradata\orcl\SHENZHENDB.dbf ' size 1000m autoextend on next 10m;

--Export Database
Exp Nncz2017/[email Protected]/nncz owner=nncz2017 file=f:\nncz2017.dmp;

--Modify Database size
ALTER DATABASE DataFile ' E:\GRP_TBSPACE. DBF ' Resize 10240m

----Flash Back Data

Select Sysdate time, TIMESTAMP_TO_SCN (to_date (' 2017-09-01 17:20:31 ', ' yyyy-mm-dd hh24:mi:ss ')) SCN from dual

ALTER TABLE Ydz_exi_budget_trans enable row movement

Flashback table Ydz_exi_budget_trans to SCN 29127907

----Query Flashback data:
SELECT * from Sal_person as of timestamp to_date (' 2012-04-27 15:00:00 ', ' yyyy-mm-dd HH24:MI:SS ')

--Querying duplicate data
SELECT * from Gfm_ps_uv_view a where rowid! = (select Max (ROWID)

From Gfm_ps_uv_view b where a.template_id=b.template_id and a.menu_id=b.menu_id and a.class_name = B.class_name and A.asse Mbly_name = b.assembly_name) Order by view_id;

--Add column fields, column comments
ALTER TABLE Gfm_ps_pfs_interface add C1 number;
Comment on column gfm_ps_pfs_interface.c1 is ' test ';
ALTER TABLE gfm_ps_pfs_interface drop column c1;--does not delete columns comments

--Query which column of a field is in which table
Select Table_name,column_name from User_tab_columns where column_name like '%action_code% ';

--Create a job to execute the stored procedure automatically (Command mode)
Declare
JOB_INITJK Pls_integer;
Begin
Sys.dbms_job.submit (Job = JOB_INITJK,
what = ' pro_initinterfacetable; ',
Next_date = Sysdate,
Interval = ' TRUNC (sysdate+1) +6/24 ');
Commit
End

--

/**
* Object array converted to list (recommended)
*
* @param arr Array
* @return List
*/
public static List Array2list2 (object[] arr) {
List List = new ArrayList ();
if (arr = = null) return list;
List = Arrays.aslist (arr);
return list;
}

--Unable to delete the currently connected user
Drop User WZCZ2016 Cascade

DROP tablespace GRP including CONTENTS and datafiles;
Drop tablespace GRP including contents and datafiles cascade constraints;

Select Owner,table_name,tablespace_name from Dba_tables
where Tablespace_name= ' GRP '
and table_name in (select Mview_name from Dba_mviews);

--oracle user cannot delete, ORA-01940 prompt "Cannot delete current connected user"
Select username,sid,serial# from v$session where username= ' WZCZ2016 '

Alter system kill session ' 137,41 ';

Alter system kill session ' 201,14034 ';

--You can then delete the user

Drop user WZCZ2016 cascade;

--View table space situation

Select A.tablespace_name "Table space name",
Total "Table space size",
Free "Table space remaining size",
(Total-free) "Tablespace usage Size",
Total/(1024 * 1024 * 1024) "Table space Size (G)",
Free/(1024 * 1024 * 1024) "Table space remaining size (G)",
(total-free)/(1024 * 1024 * 1024) "Table space use Size (G)",
Round ((total-free)/Total, 4) * 100 "Utilization%"
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

--de-empty character
Update Lyz_ls Set Tt=rtrim (TT,CHR (0))
--a string that intercepts the position after the last occurrence of a character
substr (A.parent_sr,instr (A.parent_sr, '. ',-1) +1)

--Query a table referenced by that view
SELECT * from Dba_dependencies A where referenced_name= ' Gfm_ps_pfs ' and type= ' VIEW '

Oracle Database Common Commands