{"id":7630,"date":"2018-06-23T09:52:16","date_gmt":"2018-06-23T01:52:16","guid":{"rendered":"http:\/\/rmohan.com\/?p=7630"},"modified":"2018-06-23T09:53:20","modified_gmt":"2018-06-23T01:53:20","slug":"oracle-databases-server","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=7630","title":{"rendered":"oracle databases server"},"content":{"rendered":"

oracle databases server <\/strong><\/p>\n

create database TEST
\nMAXLOGFILES 16
\nMAXLOGMEMBERS 3
\nMAXDATAFILES 100
\nMAXINSTANCES 8
\nMAXLOGHISTORY 292
\nLOGFILE
\nGROUP 1 ‘C:\\oraclexe\\app\\oracle\\oradata\\TEST\\REDO01.LOG’ SIZE 50M BLOCKSIZE 512,
\nGROUP 2 ‘C:\\oraclexe\\app\\oracle\\oradata\\TEST\\REDO02.LOG’ SIZE 50M BLOCKSIZE 512
\nDATAFILE’C:\\oraclexe\\app\\oracle\\oradata\\TEST\\SYSTEM.DBF’ size 100m autoextend on
\nsysaux datafile ‘C:\\oraclexe\\app\\oracle\\oradata\\TEST\\SYSAUX.DBF’ size 100m autoextend on
\nundo tablespace undotbs1 datafile ‘C:\\oraclexe\\app\\oracle\\oradata\\TEST\\UNDOTBS1.DBF’ size 100m autoextend on
\nCHARACTER SET AL32UTF8
\n;<\/p>\n

CREATE DATABASE axisdevdb
\nUSER SYS IDENTIFIED BY oracle
\nUSER SYSTEM IDENTIFIED BY oracle
\nGROUP 1 (‘\/u01\/app\/oracle\/oradata\/axisdevdb\/Disk1\/redo01_a.log’) SIZE 100M
\nGROUP 2 (‘\/u01\/app\/oracle\/oradata\/axisdevdb\/Disk1\/redo02_a.log’) SIZE 100M
\nGROUP 3 (‘\/u01\/app\/oracle\/oradata\/axisdevdb\/Disk1\/redo03_a.log’) SIZE 100M
\nMAXLOGFILES 5
\nMAXLOGMEMBERS 5
\nMAXLOGHISTORY 1
\nMAXDATAFILES 100
\nMAXINSTANCES 1
\nCHARACTER SET US7ASCII
\nNATIONAL CHARACTER SET AL16UTF16
\nDATAFILE ‘\/u01\/app\/oracle\/oradata\/axisdevdb\/Disk1\/system01.dbf’ SIZE 325M REUSE
\nEXTENT MANAGEMENT LOCAL
\nSYSAUX DATAFILE ‘\/u01\/app\/oracle\/oradata\/axisdevdb\/Disk1\/sysaux01.dbf’ SIZE 325M REUSE
\nSIZE 20M REUSE
\nSIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;<\/p>\n

Log into the database server as a user belonging to \u2018dba\u2019 [unix ] or \u2018ora_dba\u2019 [windows ] group , typically \u2018oracle\u2019, or an administrator on your windos machine. You are able to log into Oracle as SYS user, and change the SYSTEM password by doing the following:<\/p>\n

$ sqlplus “\/ as sysdba”
\nSQL*Plus: Release 9.2.0.1.0 – Production on Mon Apr 5 15:32:09 2004<\/p>\n

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.<\/p>\n

Connected to:
\nOracle9i Enterprise Edition Release 9.2.0.1.0 – Production
\nWith the OLAP and Oracle Data Mining options
\nJServer Release 9.2.0.1.0 – Production<\/p>\n

SQL> show user<\/p>\n

USER is “SYS”<\/p>\n

SQL> passw system
\nChanging password for system
\nNew password:
\nRetype new password:
\nPassword changed
\nSQL> quit<\/p>\n

Next, we need to change the password of SYS:<\/p>\n

$ sqlplus “\/ as system”
\nSQL*Plus: Release 9.2.0.1.0 – Production on Mon Apr 5 15:36:45 2004<\/p>\n

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.<\/p>\n

SP2-0306: Invalid option.
\nUsage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
\nwhere <logon> ::= <username>[\/<password>][@<connect_string>] | \/
\nEnter user-name: system
\nEnter password:<\/p>\n

Connected to:
\nOracle9i Enterprise Edition Release 9.2.0.1.0 – Production
\nWith the OLAP and Oracle Data Mining options
\nJServer Release 9.2.0.1.0 – Production<\/p>\n

SQL> passw sys
\nChanging password for sys
\nNew password:
\nRetype new password:
\nPassword changed
\nSQL> quit
\nYou should now be able to log on the SYS and SYSTEM users, with the passwords you just typed in.<\/p>\n

Method 2: Creating pwd file (Tested on Windows Oracle 8.1.7)<\/p>\n

Stop the Oracle service of the instance you want to change the passwords of.
\nFind the PWD###.ora file for this instance, this is usuallly located atC:\\oracle\\ora81\\database\\, where ### is the SID of your database.
\nrename the PWD###.ora file to PWD###.ora.bak for obvious safety reasons.
\nCreate a new pwd file by issuing the command:
\norapwd file=C:\\oracle\\ora81\\database\\PWD###.ora password=XXXXX
\nwhere ### is the SID and XXXXX is the password you would like to use for the SYS and INTERNAL accounts.
\nStart the Oracle service for the instance you just fixed. You should be able to get in with the SYS user and change other passwords from there.<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

Oracle default user and password<\/p>\n

sqlplus \/nolog
\nconn \/as sysdba
\nalter user system identified by manager<\/p>\n

OK, you can now use the Oracle database service normally. Specific instructions and points of attention are given in the next section.<\/p>\n

Extra operation
\n1, modify the instance name<\/p>\n

select instance from v$thread;<\/p>\n

echo $ORACLE_SID<\/p>\n

First, create a user
\ncreate user test identified by password
\nalter user test identified by password ;
\nSecond, the authorization role
\ngrant connect, resource to test;
\nrevoke connect, resource from test;
\nSecond, delete the user
\ndrop user test;
\ndrop user test cascade;
\nFourth, create \/ authorize \/ delete the role
\ncreate role testRole;
\ngrant select on class to testRole;
\ndrop role testRole;<\/p>\n

test:
\ngrant resource to nova; \/\/
\nRole related<\/p>\n

Fifth, create wm_concat function on 12C
\n11g2 and 12C have abandoned the wm_concat function<\/p>\n

Create wm_concat function on 12C Oracle default user name and password cheat sheet<\/p>\n

create user ums_dev identified by ums_dev?
\ngrant session, connect, resource to ums_dev;
\nALTER USER ums_dev ACCOUNT UNLOCK;
\nalter user ums_dev identified by ums_dev;
\ngrant unlimited tablespace to ums_dev;
\nalter table table_user add (overdate TIMESTAMP(6));<\/p>\n

View Oracle Role Users and Permissions<\/p>\n

select user_id, username, DEFAULT_TABLESPACE, ACCOUNT_STATUS,PROFILE from dba_users;<\/p>\n

select username,default_tablespace from user_users;<\/p>\n

select * from user_role_privs;<\/p>\n

select * from user_sys_privs<\/p>\n

select * from user_tab_privs<\/p>\n

Check the table related information<\/p>\n

select sum(bytes)\/(1024*1024) tablesize from user_segments where segment_name=’ZW_YINGYEZ’;<\/p>\n

select index_name,index_type,table_name from user_indexes order by table_name<\/p>\n

select * from user_ind_columns where table_name=’CB_CHAOBIAOSJ201004′<\/p>\n

select sum(bytes)\/(1024*1024) as indexsize from user_segments
\nwhere segment_name=upper(‘AS_MENUINFO’)<\/p>\n

select * from v$version<\/p>\n

Import and Export<\/p>\n

exp dbserver\/dbserver1234@ORCL file=\/opt\/dbserver.dmp owner=dbserver<\/p>\n

imp dbserver\/dbserver1234@XE file=c:\\orabackup\\full.dmp log=c:\\orabackup\\imp.log full=y<\/p>\n

Export<\/p>\n

exp dbserver\/dbserver1234@ORCL file=dbserver.dmp log=dbserver.log owner=ums rows=n<\/p>\n

exp dbserver\/dbserver1234 jdbc:oracle:thin:@10.10.73.206:1521:umpay file=dbserver.dmp log=dbserver.log owner=ums rows=n<\/p>\n","protected":false},"excerpt":{"rendered":"

oracle databases server <\/p>\n

create database TEST MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ‘C:\\oraclexe\\app\\oracle\\oradata\\TEST\\REDO01.LOG’ SIZE 50M BLOCKSIZE 512, GROUP 2 ‘C:\\oraclexe\\app\\oracle\\oradata\\TEST\\REDO02.LOG’ SIZE 50M BLOCKSIZE 512 DATAFILE’C:\\oraclexe\\app\\oracle\\oradata\\TEST\\SYSTEM.DBF’ size 100m autoextend on sysaux datafile ‘C:\\oraclexe\\app\\oracle\\oradata\\TEST\\SYSAUX.DBF’ size 100m autoextend on undo tablespace undotbs1 datafile ‘C:\\oraclexe\\app\\oracle\\oradata\\TEST\\UNDOTBS1.DBF’ size 100m autoextend on CHARACTER SET […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7630"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7630"}],"version-history":[{"count":3,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7630\/revisions"}],"predecessor-version":[{"id":7633,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7630\/revisions\/7633"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7630"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7630"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7630"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}