Oracle DBAs for IT ninja turtles are commonly used to query enumeration of vomiting blood, oracledba

Oracle DBAs for IT ninja turtles are commonly used to query enumeration of vomiting blood, oracledba

-1. query all objects in the system
Select owner, object_name, object_type, created, last_ddl_time, timestamp, status
From dba_objects
Where owner = upper ('Scott ')

-2. view all tables in the system
Select owner, table_name, tablespace_name from dba_tables

-3. View tables of all users
Select owner, table_name, tablespace_name from all_tables

-4. view the current user table
Select table_name, tablespace_name from user_tables

-5. view User table Indexes
Select t. *, I. index_type from user_ind_columns t, user_indexes I where
T. index_name = I. index_name and t. table_name = I. table_name
And t. table_name = table to be queried

-6. view the primary key
Select cu. * from user_cons_columns cu, user_constraints au
Where cu. constraint_name = au. constraint_name
And au. constraint_type = upper ('P') and au. table_name = table to be queried

-7. View uniqueness constraints
Select column_name from user_cons_columns cu, user_constraints au
Where cu. constraint_name = au. constraint_name and au. constraint_type = upper ('U ')
And au. table_name = table to be queried

-8. View Foreign keys
Select * from user_constraints c where c. constraint_type = 'r' and c. table_name = table to be queried
Select * from user_cons_columns cl where cl. constraint_name = foreign key name
Select * from user_cons_columns cl where cl. constraint_name = foreign key reference table key name

-9. view the column attributes of the table
Select t. *, c. comments
From user_tab_columns t, user_col_comments c
Where t. table_name = c. table_name and t. column_name = c. column_name and t. table_name = table to be queried

-10. View All tablespaces
Select tablespace_name from dba_data_files group by tablespace_name

######################################## ####
-1. view the maximum number of oracle connections
SQL> show parameter processes # maximum number of connections

-2. Modify the maximum number of connections
SQL> alter system set processes = value scope = spfile
-Restart the database.
SQL> shutdown force
SQL> start force

-3. view the current number of connections
SQL> select * from v $ session where username is not null

-4. view the number of connections of different users
SQL> select username, count (username) from v $ session where username is not null group by username # view the number of connections of a specified user

-5. View active connections
SQL> select count (*) from v $ session where status = 'active' # view concurrent connections

-6. view the number of connections of a specified program
SQL> select count (*) from v $ session where program = 'jdbc thin client' # view the number of jdbc connections to oracle

-7. view the database installation instance (dba permission)
SQL> select * from v $ instance

-8. view the running Instance name
SQL> show parameter instance_name

-9. view the Database Name
SQL> show parameter db_name

-10. view the database Domain Name
SQL> show parameter db_domain

-11. view the Database Service name
SQL> show parameter service_names

-12. view the global database name
SQL> show parameter global

-13. view the tablespace usage

12345678910111213141516171819202122232425262728293031

-- (1)select dbf.tablespace_name,       dbf.totalspace"Total (m )",       dbf.totalblocksas "Total number of blocks",       dfs.freespace"Total remaining amount (m )",       dfs.freeblocks"Number of remaining parts",       (dfs.freespace / dbf.totalspace) * 100 as "Idle percentage"  from (select t.tablespace_name,               sum(t.bytes) / 1024 / 1024 totalspace,               sum(t.blocks) totalblocks          from dba_data_files t         group by t.tablespace_name) dbf,       (select tt.tablespace_name,               sum(tt.bytes) / 1024 / 1024 freespace,               sum(tt.blocks) freeblocks          from dba_free_space tt         group by tt.tablespace_name) dfs where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)-- (2)select t.name "tablespace name",       free_space,       (total_space - free_space) used_space,       total_space  from (select tablespace_name,sum(bytes / 1024 / 1024) free_space          from sys.dba_free_space         group by tablespace_name)free,       (select b.name,sum(bytes / 1024 / 1024) total_space          from sys.v_$datafile a, sys.v_$tablespace b         where a.ts# = b.ts#         group by b.name) t where free.tablespace_name = t.name

Oracle DBA Training

Some Opinions on oracle DBA training are as follows:
1. First of all, I am not in favor of oracle beginners to explore on their own, because this will often be like a blind person, there will be some wrong oracle concepts. Some people in oracle DBA training said that I would use SQL to query oracle data. DBA said that if you say this, you are afraid that oracle data may also have the opportunity to check errors.
2. It is important to select a good DBA training school to learn ORACLE well. It is more important to select a good teacher. Remember that the wise man is clear. It is not a famous name, so what you need is a Mingshi who can make you understand. There is only one way to discover the oracle knowledge, that is, to communicate with the teacher, to see if the training teacher is familiar with the DBA general concept of the course.

I attended the oracle DBA employment training class at CUUG in. I have never been familiar with databases before. I just wanted to learn High-Tech C ++ or oracle. Now it seems that the choice is correct. I have had the same questions as you, and I was worried that I could not keep up with you. In fact, oracle is not so unattainable. As long as you want to learn it, you will be able to keep up with it. CUUG's DBA employment training courses start from scratch, and the progress is also slow and won't take you away. At the beginning, I started to teach oracle basics, SQL, and so on without any foundation. Who told me to learn SQL? If you have finished the preliminary basics, you will not worry about it later. However, oracle is indeed a science major, and it cannot be computer blind if you are interested in computers. In fact, I am willing to keep up with the foundations of hard work and poor performance. I am also a person of the past.
Can CUUG's oracle DBA employment training courses be employed after completion? This is really a deep feeling. During my training, I had 27 employees in the class. After the training, I successfully found a job for 26 students. Another one was very slow, so I stayed for another two months and found a job. Post-training is a project. This is very useful and I feel like I am looking for a job for an interview. I am a person who has worked for several years.

Oracle DBA interview questions

List several questions that are easy to ask. (From cuug)

1. Explain the differences between cold backup and hot backup and their respective advantages

Answer: Hot Backup is applicable to databases in the archive mode. When the database is still in the working state, it is backed up. Cold backup refers to the backup after the database is closed, applicable to all databases. The advantage of hot backup is that the database can still be used during Backup and can be restored to any point in time. The advantage of cold backup is that its backup and recovery operations are quite simple, and because cold backup databases can work in non-archive mode, the database performance will be slightly better than the archive mode. (Because archive log is not necessarily written to the hard disk)

2. You must use backup to restore the database, but you do not have control files. How can this problem be solved?

A: recreate the control file and use the recover command with the backup control file clause to restore the database.

3. How to convert init. ora to spfile?

Q: run the create spfile from pfile command.
.
4. Explain the differences between data block, extent, and segment (the term here is recommended)

A: data block is the smallest logical storage unit in the database. When database objects require more physical storage space, continuous data blocks constitute extent. All extents owned by a database object are called the segment of this object.

5. Two Methods of checking table structure are provided.

Answer: 1. DESCRIBE command
2. DBMS_METADATA.GET_DDL package
6. How to view database engine errors

Answer: alert log.

7. Compare the truncate and delete commands

A: both of them can be used to delete all records in the table. The difference is that truncate is a DDL operation, which moves HWK and does not require rollback segment. Delete is a DML operation, which requires rollback segment and takes a long time.

8. Reasons for using Indexes

A: quick access to the data block in a table

9. Two types of tables in star schema and their data are given respectively.

Answer: Fact tables and dimension tables. fact table contain a large amount of major information, while dimension tables stores descriptions of certain attributes of fact table.
Information

10. What indexes should be created on the FACT Table?

A: bitmap index)

11. are two related constraints provided?

Answer: primary key and foreign key

12. How to recreate a parent table without affecting the sub-table?

A: The foreign key of the sub-table is forcibly invalid. The parent table is rebuilt and the foreign key is activated.

13. Explain the differences between archive and non-archive modes and their respective advantages and disadvantages

A: In archive mode, you can back up all database transactions and restore them to any point in time. Non-archive mode, on the contrary, cannot be recovered to any point in time.
However, the non-archive mode can slightly improve the database performance.

14. How to Create a backup control file?

Answer: Alter database backup control file to trace.

15. How many statuses does the database normally start?

Answer:
Startup nomount-start a database instance
Startup mount-Database loading
Startup open-OPEN Database

16. Which column can be used to distinguish between the V $ view and the GV $ view?

Answer: INST_ID indicates a specific instan in the cluster environment... the remaining full text>