\u2022@ vs @@
\n@\tfind file in current directory
\n@@\tfind file in same path as the command file from which it was called<\/p>\n
\u2022object priv granted to PUBLIC
\nselect * from dba_tab_privs
\nwhere grantee = ‘PUBLIC’;
\n\u2022system priv granted to PUBLIC
\nselect * from dba_sys_privs
\nwhere grantee = ‘PUBLIC’;
\n\u2022roles granted to public
\nselect * from dba_role_privs
\nwhere grantee = ‘PUBLIC’;<\/p>\n
Move db files<\/p>\n
\u2022backup db
\n\u2022alter tablespace accounting offline normal | drop;
\n\u2022mv \/disk1\/accounting01.dbf \/disk2\/accounting01.dbf
\n\u2022alter tablespace accounting rename datafile ‘\/disk1\/accounting01.dbf’ to ‘\/disk2\/accounting01.dbf’;
\n\u2022alter tablespace accounting online ;<\/p>\n
\u2022OR: shutdown; startup mount
\n\u2022mv \/disk1\/accounting01.dbf \/disk2\/accounting01.dbf
\n\u2022alter database account rename file ‘\/disk1\/accounting01.dbf’ to ‘\/disk2\/accounting01.dbf’;
\n\u2022alter database open ;<\/p>\n
\u00a7Add datafile
\nalter tablespace “LOB1”
\nadd datafile ‘\/disk1\/file2.dbf’
\nsize 500M
\nreuse;<\/p>\n
\u00a7Recover datafile
\nalter datafile
\nrecover datafile ‘\/disk1\/ fils2.dbf’;<\/p>\n
\u00a7Resize datafile
\nalter database
\ndatafile ‘\/disk1\/file2.dbf’
\nresize 5000K;<\/p>\n
Recover from Missing RBS Datafile<\/p>\n
\u2022add in pfile to indicate corrupted rbs segmente
\n_corrupted_rollback_segments=RBS0,RBS1,RBS2…<\/p>\n
\u2022shutdown abort; \u2022create a temp rbs tblspc \u2022drop all original rbs segments \u2022recreate original rbs tblspc \u2022take temp rbs segments offline \u2022remove from pfile Reduce RBS datafiles<\/p>\n \u2022Create temp rbs tblspc Retrieve store procedure text \u2022Outer Joins SQL*Plus<\/p>\n \u2022SET AUTOCOMMIT 100 Oracle Table Definition Constraint Definition \u2022@ vs @@ @ find file in current directory @@ find file in same path as the command file from which it was called \u2022object priv granted to PUBLIC select * from dba_tab_privs where grantee = ‘PUBLIC’; \u2022system priv granted … Continue reading
\n\u2022alter database
\ndatafile ‘
\n\u2022alter database open;<\/p>\n
\n\u2022create at least one online rbs seg<\/p>\n
\n\u2022drop original rbs tblspc<\/p>\n
\n\u2022recreate all original rbs segments<\/p>\n
\n\u2022drop temp rbs tblspc<\/p>\n
\n_corrupted_rollback_segments=RBS0,RBS1,RBS2…
\n\u2022restart database<\/p>\n
\n\u2022Create temp rbs segs in it
\n\u2022Drop tblspc
\n\u2022Delete datafile
\n\u2022Recreate rbs tblspc & rbs segs<\/p>\n
\nselect obj.object_name, src.text
\nfrom user_objects obj,
\nuser_source src
\nwhere obj.object_name=src.name
\nand object_type=’PROCEDURE’
\n\/<\/p>\n
\nmysql>select *
\nfrom clients c left join invoices i
\non c.id=i.id;
\nOr
\nmysql>select *
\nfrom clients c left join invoices i
\nusing (client_id);<\/p>\n
\n\u2022SET SCAN OFF
\n\u2022set pagesize 80
\n\u2022break on name skip page on value<\/p>\n
\nselect table_name, column_name,
\ndata_type, data_length, nullable
\nfrom user_tab_columns
\norder by table name
\n\/<\/p>\n
\nselect cons.table_name, cons_constraint_nams, cons.constraint_type, col.column_name
\nfrom user_constraints cons, user_cons_columns col
\nwhere cons.table_name = col.table_name
\nand cons.constraint_nams = col.constraint_name
\norder by cons.table_nams, cons.constraint_name<\/p>\n\r\n@?\/rdbms\/admin\/UTLXPLAN.SQL\r\n\r\nEXPLAIN PLAN FOR\r\n 2 SELECT *\r\n 3 FROM emp e, dept d\r\n 4 WHERE e.deptno = d.deptno\r\n 5 AND e.ename = 'SMITH';\r\n \r\n\r\nSQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR\r\n 2 SELECT *\r\n 3 FROM emp e, dept d\r\n 4 WHERE e.deptno = d.deptno\r\n 5 AND e.ename = 'SMITH';\r\n\r\nExplained.\r\n\r\nSQL> @explain.sql TIM\r\n\r\nPLAN OBJECT_NAME OBJECT_TYPE BYTES COST PARTITION_START PARTITION_STOP\r\n-------------------------------------- --------------- --------------- ----- ----- --------------- ---------------\r\nSelect Statement 57 4\r\n 1.1 Nested Loops 57 4\r\n 2.1 Table Access (Full) EMP TABLE 37 3\r\n 2.2 Table Access (By Index Rowid) DEPT TABLE 20 1\r\n 3.1 Index (Unique Scan) PK_DEPT INDEX (UNIQUE) 0\r\n\r\n5 rows selected.\r\n\r\nSQL>\r\n\r\n-- SQL*Plus\r\nSet autotrace on\r\n<\/pre>\nTablespaces<\/h3><\/span>\n
\r\nDROP TABLESPACE tbs_01 \r\n INCLUDING CONTENTS \r\n CASCADE CONSTRAINTS; \r\n\r\n# Drop tablesapce mytbs01 AND drop data files\r\ndrop tablespace mytbs01 including contents and datafiles;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"